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.
Distributed systems can benefit from serialization, but it adds overhead to concurrent transactions. Database servers must replicate their data across distributed systems for data integrity, but queries could return disparate information depending on the database server where the code executes. Isolation levels could cause deadlocks, which would harm performance.
Developers can use serialization with NoSQL databases as well, but they are responsible for serializing data. Data is often stored in a key-value pair in standard NoSQL databases. Developers can store the value as a serialized information and deserialize it as data loads in an application. Pure Storage FlashArray products handle high-performance serialization for NoSQL and SQL database storage.
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.
Administrators can set concurrent control in a database to reduce likelihood of conflicts during serialization and recovery. Pessimistic data controls are for high-volume transaction servers where concurrent reads and writes could affect data integrity. Administrators use pessimistic control to lock data as transactions are pending. Optimistic data controls allow reads during concurrent transactions, but the assumption is that a conflict is less likely. Most high-volume modern database engines handle data integrity while preserving performance during concurrent transactions.
Bring Incredible Simplicity to Your Data Storage Management
Work in a self-service environment to experience Purity management for FlashArray.
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.
On a high-traffic database, isolation levels help with concurrent transactions. A concurrent transaction is when the database is performing an action when it’s also retrieving the data. This phenomenon is called a “dirty read.” You can set isolation levels in SQL procedures. Higher isolation levels improve accuracy, but they also negatively affect performance. The most common control mechanisms are Two-Phase Locking (2PL) and Multiversion Concurrency Control (MVCC). 2PL locks data while it’s being modified, and MVCC takes a snapshot of it to deliver data while it’s being updated.
Two common use cases for serialization are ecommerce and financial databases. Both industries work with potentially millions of transactions a day. Having a reliable high-performance serialization database keeps transactions running under a few milliseconds while maintaining data integrity. Having these two features requires modern database technology, infrastructure, and the right administration to configure and manage it.
In emerging technology, serializability benefits blockchain and artificial intelligence and machine learning applications. Both these technologies have potentially millions of transactions, depending on their popularity and userbase. Working with serialization could also help these applications with data integrity and performance. For example, machine learning applications work with huge data silos, and serialization ensures that data is updated and presented without errors.
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.
Conclusion
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.
