SQL : Transactions and ACID properties.
There are always two point of views of transactions. First, the user point of view, for example, customer went to an ATM to retrieve some amount. For him, the transaction might look like,
For a developer or admin, the transaction will be,
But did we estimate any failure condition in both of the cases shown above?
So these cases do not follow the basic expectation of transaction that is to save from any kind of failure. Now, with our experience, we can define that Transaction is an ‘Expectation’ of a user and a developer. ‘Expectation of success’.
We also have a theoretical definition, which is very good for interview purpose and available everywhere on different sites.
“ Database transaction is collection of SQL queries which are fired sequentially to perform a single unit of work. To succeed, all queries should successfully run. If the sequences breaks with a failed query, then the unit of work will not happen and all the previous successfully run queries will also revert back.”
Here the aim is to successfully run the task or not run it at all.
To achieve this operation, all the best practices/concepts available were merged and laid the foundation of ACID rules, which should be followed to achieve any kind of successful transaction.
What are ACID properties or concepts?
First of all, they are concepts and not implementations. There are three part of implementation: Transaction, Commit and RollBack.
A=Atomicity : A single unit of work has to be atomic, either all steps of transaction completes so its a success or none of them like it never began. Failure is not an option.
To achieve a successful transaction, Commit statement should fire. or if any step fails, then a RollBack statement should fire, like the transaction never began.
C=Consistency : Consistency is more a related term and requires for a valid state of the database. A unit of work forms a transaction but there might be dependency which follows after a successful transaction or are beyond that unit of work. For example, a customer having multiple accounts, transfers money from his one account to other. In one case, the account was deducted the requested amount but other account was not updated with the new amount. It will leave the database in half finished state, which is not a desirable state. So database should remain consistent after a transaction.
I=Isolation : This concept is more related to exclusivity of transactions. When a transaction is happening, this transaction should have exclusive rights to perform the transaction. No other transaction should interfere with this transaction. To implement the concept, we can use Isolation levels in database.
D=Durability : This concept guarantees the availability of data after the transaction. It can be use of database backups and transaction logs that facilitate the restoration of committed transactions or hardware fail safe methods. The aim is clear which is ‘Never lose any data’.
Transaction Implementation Examples:
There are following commands used to control transactions.
COMMIT: Successful Transaction.
BEGIN TRANSACTION UPDATE Savings SET min_Amount=500 COMMIT TRANSACTION
ROLLBACK: No transaction if any step fails.
BEGIN CATCH <..........> ROLLBACK TRANSACTION END CATCH
SAVEPOINT: Points to save the transaction before any ROLLBACK. No rollback of whole transaction.
SAVEPOINT SAVEPOINT_NAME; //and ROLLBACK TO SAVEPOINT_NAME; // to rollback till savepoint, created earlier.
SET TRANSACTION: Sets characteristics of a transaction.
SET TRANSACTION [ READ WRITE | READ ONLY ];
A sample format:
BEGIN TRY BEGIN TRANSACTION <..........> COMMIT TRANSACTION END TRY BEGIN CATCH <..........> ROLLBACK TRANSACTION END CATCH