Transactions in Databases

I am in charge of the backend database part of our capstone project. Therefore, I am taking CS340, Introduction to Database, at OSU at the same time. In the design and implementation of our capstone project’s database, I start to understand the importance of database transaction.

When creating a database management system to store, retrieve, and run queries on data, we usually design the entity relationship diagram and the database schema based on the entities and attributes of the objects in a system. Then after the creation, a database transaction performs the unit level of update to this system to provide the data updates. For example, in an e-commerce system, users, products, and locations are entities; orders are the database transactions. Each order contains the information about which user requires what quantity of which products from which locations. Based on this order, the user will pay some amount of money; the store will receive such an amount of money; some amount of product A will be removed from location X; the exact amount of product A will then be delivered to the user’s address. A straightforward transaction record would ensure the database not to be interfered by multiple concurrent access and to cause some errors. Plus, even when an error did happen, the database could rollback based on the related transaction. Therefore, a set of properties of database transactions was concluded as Atomicity, Consistency, Isolation, and Durability.

Atomicity guarantees that each transaction is treated as a single “unit”, which either succeeds completely or fails completely.[1] This is similar to what happens when we fail to install something on our laptop. If an error occurred, no matter at the 1% or the 99% of the processing bar, every installation would be rolled back. This means a single transaction only runs in a whole. If the transaction contains actions related to multiple entities, every action will be consistently done; one action’s failure means the whole transaction never happened. 

Because of the transaction’s atomicity, the database will have the property of consistency. This is saying that after the creation of a database with only entities and attributes and without any data, i.e. this is an empty database, the database has been ensured to be integrated as a whole database. Then, both before and after a transaction was updated, the database will remain the integration, which means the wholeness of the database would be invariant. This property prevents illegal transactions.

Transaction isolation determines how transaction integrity is visible to other users and systems.[2] It guarantees the database management system to be able to handle concurrency controls by setting different isolation levels. The precedence of isolation levels is a big topic to study. I will leave this topic for now. Lastly, a database needs to be durable, which means two things. First, after each transaction is processed, the update to the data in this database is permanent. Second, this update will be recorded by the database management system and shouldn’t be lost under any circumstances.

In general, the study of transactions and the set of properties allow us to remember the importance behind database design. Personally, I would highly recommend everybody to revisit these concepts before starting a system design.

References

  1. https://en.wikipedia.org/wiki/ACID
  2. https://en.wikipedia.org/wiki/Isolation_(database_systems)