Many a times I have been asked by people not familiar with databases, what is a transaction? What does ACID mean? So I thought of writing this post to help bring out a very simplistic explanation of transaction and ACID properties. There is a lot of technical jargon out there and it at times gets very confusing.
TRANSACTION
Literally means a give and take and in database parlance it is the give and take of information. I am sure you would have heard this term as often as you hear "water". But then what is a transaction in terms of a database? What constitutes a transaction? What are its properties? When does it begin and how does it end?
The most common definition of a database transaction is - "A unit of work" performed within a database system. So what is this "unit of work"? Simply put, it is the a series of one or more "actions" performed on the data stored within the database. And these actions could be storing/updating/deleting/reading data. In database language (SQL) a store operation is called an INSERT while read operation is called a SELECT. So a transaction can be a single such operation or a combination of INSERT/UPDATE/DELETE/SELECT operations.
ACID
What determines if a transaction will be a single operation or a combination of operations, depends on the initial information that the database has and the final information that it needs to have. So, obviously a successful transaction would be one that when completed, would leave the database to have this final information permanently. In other words, the transaction is successful if it is "DURABLE (D)".
Also, the final information that is stored in the database should confirm to the rules and relationships governing that data. The transaction should not lead to a data that is inconsistent or not in confirmation with the rules and relationships governing that data. In other words, the transaction is successful if it leaves the database in a 'CONSISTENT(C)' state.
During the execution of transaction, if there is a system crash or a power failure or the transaction itself raised an exception, what should happen to the data that was manipulated by the transaction thus far? Obviously, since the transaction didn't complete, the data it would have manipulated or created would be inconsistent with the rest of the data in the database. Would it not be good if the data that was manipulated by the transaction reverted to the initial state of the transaction? In other words, the transaction approached the data as "all or nothing"; all the operations successfully done or none of them. Which means that the transaction would be 'ATOMIC(A)'.
Finally, while the transaction is underway other transactions/sessions should not be able to see the intermediate data simply because the data is still inconsistent. In other words, the transaction should be 'ISOLATED(I)'.
This is all about ACIDity of transactions. ATOMIC, CONSISTENT, ISOLATED, DURABLE.
The last bit - When does a transaction start and when does it end? Well the implementation differs from vendors to vendors, but the underlying principle remains the same. A transaction starts with the first operation and ends with a "COMMIT" or a "ROLLBACK". While a commit completes the manipulations of the transaction; a rollback on the other hand reverts all the manipulations done by the transaction. In either scenario, the database is left in the consistent state.
