What is a Transaction Management in TCL with Code?

Transaction Management in TCL

Database transactions are managed using a class of SQL instructions called Transaction Control Language (TCL). With the help of these instructions, database interactions are managed, guaranteeing consistent and dependable data particularly when dealing with concurrent access or system outages. A sequential set of database operations (such SELECT, INSERT, UPDATE, or DELETE) carried out as a single logical unit of work is known as a transaction, and it is the central idea. This indicates that either every operation in the transaction is successful or none of them are.

MySQL and other contemporary database management systems frequently incorporate built-in transaction support and the “ACID” attributes. The acronym ACID stands for the four common characteristics that ensure transaction reliability:

• Atomicity: A transaction is regarded as an indivisible entity thanks to the quality known as atomicity. It indicates that either every operation in the transaction is finished successfully and reflected in the database, or if any operation fails, the transaction is terminated and all earlier operations are reversed. Transferring money across bank accounts is one example; if the withdrawal from one account and the deposit into another are unsuccessful, neither should occur. In the event that the transaction is not completed, the database system makes sure that the database state is restored.
• Consistency: A transaction’s ability to move the database between valid states is guaranteed by its consistency. After a successful transaction is committed, it ensures that the database changes states appropriately. Database consistency requirements must not be broken by transactions. When a transaction successfully ends, the database must remain consistent if it was consistent when the transaction began.
• Isolation: This feature guarantees that transactions running concurrently won’t interfere with one another. Other transactions cannot see changes made by one transaction before the first transaction has been committed. As a result, transactions are able to function autonomously and openly. A transaction’s exposure to other concurrent transactions’ activity is controlled by various isolation levels. For instance, InnoDB isolates using row-level locking, MVCC, and consistent snapshots.
• Durability: This characteristic guarantees that adjustments made during a transaction are irreversible and will withstand any further system malfunctions, including crashes or power outages. The changes that have been committed are immediately stored in permanent storage. To guarantee longevity, MySQL’s InnoDB storage engine makes use of a transaction log, also known as a redo log.

Transactional databases need certain ACID features to maintain data integrity. MySQL’s InnoDB storage engine supports these properties, unlike MyISAM.

MySQL starts transactions with START TRANSACTION or BEGIN WORK. Each SQL query is handled as a distinct transaction and is automatically committed when it is finished when MySQL is in AUTOCOMMIT mode by default. In order to combine several statements into a single transaction, you must either initiate an explicit transaction block with initiate TRANSACTION or BEGIN WORK, or disable AUTOCOMMIT (SET AUTOCOMMIT=0). The AUTOCOMMIT feature is momentarily deactivated for the length of an explicit transaction.

You can construct a named point inside a transaction by using the SAVEPOINT command. By using this, you can partially reverse a transaction to a particular savepoint instead of reversing all of the modifications made since the transaction started. In essence, a savepoint is a point in time where the transaction is still ongoing.

To permanently preserve all changes made within the current transaction, use the COMMIT command. Depending on the degree of isolation, once a transaction is committed, the modifications are assured to be permanent and visible to other transactions. At commit, all of the transaction’s temporary values and changes are written to stable storage.

After the last COMMIT or START TRANSACTION/BEGIN WORK, all changes made to the current transaction can be undone using the ROLLBACK statement. You can use ROLLBACK TO savepoint_name to roll back to a specific named savepoint if you have saved them. Changes made after that savepoint are undone, while changes made before it are retained in the current transaction. The database reverts to its initial state and the entire transaction is rolled back if no savepoint is specified.

Here is an example demonstrating transaction control commands:

-- Example of a bank transfer transaction
-- Ensure you are using a transactional storage engine like InnoDB
-- Optional: Disable autocommit for the current session
SET AUTOCOMMIT = 0;
-- Start a new transaction
START TRANSACTION; -- or BEGIN WORK;
-- Step 1: Check availability and deduct from Account A
-- Assuming accTable has columns customerId and ledgerAmt
SELECT @availableAmt := ledgerAmt FROM accTable WHERE customerId = 1 FOR UPDATE; -- Use FOR UPDATE to lock the row 
-- Check if sufficient funds are available (example logic)
IF @availableAmt >= 100 THEN
    -- Deduct 100 from Account A
    UPDATE accTable SET ledgerAmt = ledgerAmt - 100 WHERE customerId = 1;
    -- Create a savepoint before depositing
    SAVEPOINT before_deposit;
    -- Step 2: Deposit into Account B
    UPDATE accTable SET ledgerAmt = ledgerAmt + 100 WHERE customerId = 2;
    -- Assume some error occurs during the deposit (e.g., constraint violation)
    -- In a real scenario, an error would raise an exception and trigger a rollback
    -- If deposit failed for some reason, rollback to the savepoint
    -- ROLLBACK TO before_deposit; -- If you need to undo only the deposit
    -- If all operations successful, commit the transaction
    COMMIT; -- Saves all changes permanently
ELSE
    -- Insufficient funds, rollback the entire transaction
    ROLLBACK; -- Undoes all changes since START TRANSACTION 
END IF;
-- Check the result (depending on whether COMMIT or ROLLBACK was executed)
-- SELECT ledgerAmt FROM accTable WHERE customerId IN (1, 2);
-- Optional: Re-enable autocommit
SET AUTOCOMMIT = 1;

This example shows how to manage a sequence of database activities as an atomic unit using START TRANSACTION, SAVEPOINT, COMMIT, and ROLLBACK, guaranteeing that the database stays in a consistent state. To aid preserve isolation when handling concurrent changes, the FOR UPDATE clause is also displayed. This clause obtains an exclusive lock on the selected row for the duration of the transaction.