What is a database transaction? How do database transactions work? Provide an example of a database transaction.
In databases, a transaction is a set of separate actions that must all be completely processed, or none processed at all. When you think of a transaction, you should think of the phrase “all or nothing”, because that is a defining feature of database transactions – either every part of the transaction is completed, or nothing at all.
One thing that’s important to understand is that a transaction can consist of multiple SQL statements – not just one. An example would be transferring some funds from one bank customer to another. This scenario would have to both credit one customer and debit another – requiring updates to different rows in table, but would be considered a single transaction.
A commonly used synonym for a transaction is a unit of work.
The acronym ACID can be used to remember the properties of database transactions. Here is what each letter in the acronym ACID stands for:
- Atomicity. This means that a transaction must remain whole – it’s all or nothing. So, the transaction as a whole must either fully succeed or fully fail. If and when the transaction is a success, all of the changes must be saved by the system. If the transaction fails, then all of the changes made by the transaction must be completely undone and the system must revert back to it’s original state before the changes were applied. The term rollback is used for the process that undoes any changes made by a transaction that has failed – think of it as the database rolling back the changes of a failed transaction. The term commit is used to refer to the process which makes the transaction changes fixed – think of it as the database fully committing the transaction changes once and for all.
- Consistency. This means that a transaction should change the database from one consistent state to another.
- Isolation. This means that each transaction should do it’s work independently of other transactions that might be running at the same time.
- Durability. This means that any changes made by a transactions that have run to completion should stay permanent, even if the database fails or shuts down dues to something like power loss. You might be confused, because clearly data in a database is always changing, so how could anything be permanent? Well, permanent in this context simply means that the change made by the transaction will not disappear if and when the database encounters some failure or shuts down.
RDBMS’s and Transaction support
Most RDBMS’s have support for transactions. What this means is that they are able to identify both the start and end of every transaction and also log all changes made by a transaction in order to be ready for a rollback if necessary. Of course, the way in which transactions are supported by each RDBMS varies from one RDBMS vendor to another – so Oracle is different from MySQL in the way it supports transactions, as is DB2 from SQL Server, etc.
What is a transaction log?
Most of the RDBMS’s that have transaction support record all of the transactions along with any changes made by those transactions inside a transaction log. Inside the transaction log there is copy of what the database looked like before and after any changes made by a transaction. This means that if a rollback is necessary, then the record of what the database looked like before the changes were applied can be used to reverse those changes that were made by the transaction. Also, a commit of a transaction is not really considered finished until the transaction log has a record of the commit. If there is some sort of power failure that brings down a database, then the transaction log may be the only way that data can be recovered, especially because database changes are not written to disk immediately, and may not have made it to disk before the database outage.
An example of a database transaction
While transaction support differs from one database to another, it’s hard to give an example of a transaction without going into the specific syntax details of a particular RDBMS. But, some RDBM’s allow you to start a transaction with a SQL statement that looks like “START TRANSACTION OR BEGIN TRANSACTION”. Then, you follow that statement with the SQL that you would like to run as part of the transaction.
Which databases support transactions?
Here are some articles on different RDBMS’s and how they each support transactions: