When you have multiple applications sending transactions to a database, you can execute them concurrently or serially. The way a database executes these transactions will have an effect on data, so it will result in possible data corruption if it’s not done properly. The database management system (DMBS) you choose will determine the way queries are managed.
What is Serializability in DBMS?
It’s unlikely that you only have one person using an application at a time. You may have hundreds and even thousands of concurrent users accessing your application at any given time. Each page on the web application makes calls to the database to either retrieve data or manipulate it in some way.
In a serializable DBMS, queries do not interfere with each other. The database will execute the first transaction, and then it will execute the next transaction. By using a serialized order to execute transactions, changes from other transactions do not interfere with data currently being read or changed in the database.
How Does Serializability Work?
A DBMS that works with several transactions must schedule each one to avoid data corruptions. Serializability is important in data integrity. Without data integrity, your database would have inconsistent data and would provide incorrect information to your front-end application.
Serializability works by scheduling transactions in the order in which they are executed. For example, if a user sends a transaction to the database to read and then write to a table, the next user might also send the same set of queries. The DBMS manages the transactions by first executing the first read and write query and then executing the next transaction. By executing queries using serializability, the data contain values consistent with business logic.
Serializability and Recoverability: How the Two Interact
Developers writing queries can include commit and rollback statements. A commit statement tells the DBMS to finalize the transaction and execute instructions. A rollback statement tells the database to abandon the query and return data to its original state. This type of transaction statement is used in recoverability.
A serialized list of statements helps with recoverability by ensuring subsequent queries have consistent data without any corruption. Without recoverability, the next transaction might work with corrupted or inaccurate data. Should the first statement fail without using a rollback procedure, the next transaction in serializability would be working with incorrect data. This error in execution would have a domino effect on data integrity.
Types of Serializability with Examples
Suppose that you have the following transaction schedule:
- Transaction 1: Read data from the Customer table and then update the customer first name.
- Transaction 2: Read the customer first name from the Customer table and return it to the application.
If Transaction 2 executes before Transaction 1 is finished, transaction 2 would have an inaccurate first name. For data to stay accurate, Transaction 1 must finish first and then Transaction 2 can execute.
In a serialized schedule, Transaction 1 executes both the read and write operations first. Only after Transaction 1 is finished will Transaction 2 execute its statements. By forcing the first transaction to finish first, it also avoids the phenomenon of dirty reads. A dirty read happens when concurrent transactions execute on the same data, and a conflict causes the database to read a value before a previous transaction updates it.
How to Test Serializability
Testing for serializability is done using a serialization graph. The graph is a representation of a specific schedule usually with the name S, and each transaction is laid out on the graph. The graph is laid out as a set of transactions labeled A, B, and C.
For each transaction, all queries must execute before subsequent transactions start their procedures. If transaction A executes a read on data, then this must happen before transaction B performs an update query. If any transactions execute concurrently, then the DBMS does not use serializability.
As you navigate through the many database choices for your applications, a DBMS that schedules transactions using serializability ensures that your data will stay accurate and applications will not suffer from dirty reads. You do not need to configure serializability, because your chosen DBMS will support it as part of its data management functionality.