In the context of database, a transaction is a logical unit which is independently executed for data retrieval or insert & updates.
In relational databases, a transaction must be;
- Atomic
- Consistent
- Isolated
- Durable
.. summerized as the ACID acronym.
Basic Definitions of ACID
Atomicity:
A transaction must be fully completed, saved (committed) or completely undone (rolled back) .
For example; a sale scenario, the sale consists of inventory reduction and a record of incoming cash. Both either happen together or do not happen. It’s all or nothing.
Consistency:
Any given database transaction must change affected data only in allowed ways. Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers.
Database consistency is important because it regulates the data that is coming in and rejects the data that doesn’t fit into the rules.
For example; int type column can store integer values in (-2^31 to 2^31) because int is signed 32-bit integer. So, transaction which will try to manipulate int type column must has value in this integer region. If transaction has 2^35 value, transaction will be rolled back because of consistency problem of column and database.
Isolation:
Transaction data must not be available to other transactions until the original transaction is committed or rolled back.
For example; while one transaction(T1) is trying to withdraw money from account in bank system, the another transaction(T2) could not deposit money to same account. T2 must wait to be performed untill T1 committed or rolled back.
Durability:
Transactions that have been committed will survive permanently. For clear
understanding;
- Firstly, DB engine marks the transaction committed.
- Then permanently stores this transaction data in DB
- Then sends the confirmation to user
What if DB crashes after step 1? While startup, DB engine can read that transaction was committed in logs but this transaction data is not written the DB block. So it will complete this transaction.
Many DBMSs implement durability by writing transactions into a transaction log. Transaction logs is used for reprocessing to recreate the system state right before any later failure. A transaction is marked as committed only after it is entered in the log.
An Example Transaction
A transaction do not means only one SQL query will be executed. A transaction could have multiple queries as its seems below:
These two queries either both will be executed or neither will. Let’s try to commit some wrong statements;
As you can see, at the line 3, we are trying to update Balance column of AccountId = 1. But Balance column type is decimal and we are trying to add ‘a’ char value to its value. So, the line 3 query will not affect the value of target row because consistency rule will be violated. And also because of atomic rule, line 4 query will not be executed. Let’s see the result;
As a result, transaction is rolled back. See you next post 🙂