Transaction is an important concept in database. According to Wikipedia database is an organized collection of data (also known as a data store) stored. To know basic of database, you can access a blog. In this blog, we will learn about transaction and transaction isolation levels. So, what is transaction?
A transaction is a sequence of multiple operations implemented on a database. Taking place wholly or not, it means each transaction will be performed completely. On the other hand, there is no any case that only half of the operation is implemented and the results were stored.
For example, when you transfer money to a person called A, if failures happen. Database will be rollbacked. So you still have entire your money and A person also doesn't receive money that you transfer.
Transaction has 2 type distinguished by type of how to distribute operations of transaction :
- Flat transaction: has a single begin point and a single end point. It means that before moving on to the next request, a flat transaction must complete a previous one. Flat transaction is used for short activities rather than large ones. Because it has some limitations such as:
- Nested transaction: each transaction has other transactions within its begin point and its end point called as nested transaction. Nested transaction also is known as sub-transaction. Nested transaction maybe has higher performance.
How is Transaction work?
Failures of systems are inevitable. So transaction provide a way to ensure that the outcome is reliable and consistent. The states of transaction will reflect all transactional changes committed before transactions were in-flight at the failure point. This helps database is rolled back cleanly. Besides, some conflicts also occur when multiple concurrent requests are hitting the database server and changing the same data simultaneously. Transaction must adhere isolate property to prevent conflict.
- Active states: it is initial state when the transaction starts executing the instructions
- Partially committed: in this state, changes have been executed but databases have not yet committed the changes on disk. This means that the changes are not written on the disk and now they are written on memory buffer not stored on the disk.
- Committed: All the transaction updates are permanently stored on databases, in this state. So, transaction can not roll back after this point.
- Failed: This state happens when transactions is failed or aborted in the active state and partially committed.
- Terminated: After committed state and failed state, terminated state happens. It is the end point of transactions and mark end of the transaction life cycle.
Acid properties
In relative database, transaction always must adhere ACID properties stood for 4 characters atomic, consistent, isolated, durable. ACID properties ensure that the transaction is a reliable process.
- Atomicity: implementing to follow all or nothing rule. If any of the actions is failed, the entire transaction is rolled back like the transaction had not ever happened.
- Consistency: data is in the begin point and the end point of transaction is consistent.
- Isolation: transactions have been implemented at the same time must not affect together. This help to avoid conflict when many transactions execute the same data
- Durability: a successful transaction will survive permanently.
Isolation levels
- Dirty read: this situation happens when a transaction read the data that has not yet been committed. For example, when your mom give you a gift and then you say to your friend and promise bring it to school tomorrow. But because of some reasons, your mother returned it while you are in school and promise bring to school. And of course, you don't have it to bring to school tomorrow. This likes transaction A updates data, not commits and leave meanwhile, transaction B read the updated row. Then transaction A rolls back. This means transaction B use the data that does not exist
- Non repeatable read: this happens when a transaction reads twice the same row but their results are not the same. For instance, your exercise has a result is A but it wrong, your teacher revise it. And when you receive your exercise from your teacher. Obviously, the result in your book has been different. This similars to transaction A read a data and not commit, transaction B comes, update it and commit. When transaction A reread, the outcome is a different value.
- Phantom read: it occurs when two the same queries are executed, but the row retrieved by the two, are different. For example, transaction A retrieved a set of row by the following search standard, but now transaction B create some rows that satisfy search criteria of the transaction A. Then transaction A re-executes this statement that read the rows, it gets different outcome compared to the first result.
- Read uncommitted: it is a lowest isolation level. Read uncommitted allow dirty read that can happens because a transaction may read not yet committed changes made by other transactions.
- Read committed: in this level, no allowing dirty read and clearly state that because any uncommitted data is committed, immediately it is read.
- Repeatable read: this level is the most restrictive isolation level. The transaction holds read locks on all the rows it references and write locks over all the rows it updates/inserts/deletes. So non-repeatable reads have no chance to happen.
- Serializable: it is the highest isolation level. Executing this level is guaranteed to be serializable. It means that all concurrent transactions be executed serially.
Summary
Transaction in database is an important concept to know when you play a role as a user of system or developer. To know how transaction work is an essential thing that help us can use it effectively and limit failures.
Transaction adhere ACID properties that help with avoiding conflicts or failures happen. Besides, it ensure that the transaction is a reliable process.
Isolation levels came exist, they provide user with the ability to trade off between ensured isolation and improved performance. So, we should use them carefully and effectively.
Reference
Database. Wikipedia. https://en.wikipedia.org/wiki/Database
Flat & nested distributed transaction. Geeksforgeeks. https://www.geeksforgeeks.org/flat-nested-distributed-transactions/
What is a database transaction?. (Aug 2nd, 2021). Fauna. https://fauna.com/blog/database-transaction
Mô tả định nghĩa về giao dịch, cách xử lý lỗi và các isolation level. (May 5th, 2022). Funix. https://funix.edu.vn/chia-se-kien-thuc/dinh-nghia-ve-giao-dich-cach-xu-ly-loi-va-cac-isolation-level/
Demystifying Database Systems, Part 1: An Introduction to Transaction Isolation Levels. (May 3rd, 2019). Daniel J. Abadi. https://fauna.com/blog/introduction-to-transaction-isolation-level
Transaction Isolation Levels in DBMS. Geeksforgeeks. https://www.geeksforgeeks.org/transaction-isolation-levels-dbms/
Transaction ở mức độ cô lập (Isolation Level). Viblo. https://viblo.asia/p/transaction-o-muc-do-co-lap-isolation-level-1ZnbRlWNv2Xo