The ACID property is the cornerstone of database theory. It defines four properties that a theoretically reliable database must have: atomicity, consistency, isolation and persistence. Although these four properties are important, isolation is the most flexible. Most databases provide some isolation levels to choose from, and many libraries now add additional layers to create finer grained isolation. The reason why isolation levels are applied so widely is that relaxing isolation constraints often improves scalability and performance by several orders of magnitude.
The main goal of concurrency control is to ensure that transactions are isolated and will not affect other transactions. High levels of isolation are achieved at the expense of performance. Concurrency control can be realized by pessimistic or optimistic mechanism. Most relational databases use the pessimistic mechanism to achieve write optimization. The pessimistic mechanism uses locks, which can block some operations or conduct some forms of conflict detection. When a table, page or row is modified, the lock in the pessimistic mechanism can be used to block other potential transactions that access and modify resources. However, the optimistic mechanism does not use any locks. It only relies on conflict detection to maintain transaction isolation. The conflict detection adopted by the optimistic mechanism allows all read operations and verifies their consistency at the end of the transaction. If a conflict is detected, the transaction will be rolled back or redone. Most web servers are read in optimization, so optimistic mechanism is used. By allowing all read in operations, the optimistic mechanism can not only ensure high read and write throughput, but also ensure data consistency when resources are not always changing. Serial consistency is one of the oldest and highest isolation levels available. It is favored because of its simple programming model, that is, only one transaction can operate on a given resource at a time, which avoids many potential resource problems. However, most applications (especially Web applications) do not adopt this high level of isolation because it is impractical from the perspective of end users - any application with a large number of user groups will have a delay of several minutes when accessing shared resources, which will rapidly reduce the number of users. Weak consistency and final consistency can be seen everywhere in large-scale distributed data sources, such as the Web. Several successful large-scale Web applications (for example, eBay and Amazon) show that the optimistic weak consistency mechanism is much better than the traditional pessimistic mechanism in terms of scalability. This article takes a look at eight different isolation levels. Learn to properly relax the constraints of data consistency. You can use these eight isolation levels in your own applications to achieve better performance and scalability.
The isolation levels listed below are used to help Web developers better understand the constraints placed in their programming models, and help system architects and developers discuss how to select the most effective isolation level while maintaining necessary data integrity. They are listed in the order from least isolated (uncommitted reads) to most isolated (serialized).
1. Read Uncommitted
The uncommitted read isolation level requires very little isolation between transactions. Each read operation can see the pending write operations (dirty reads) in the transaction. However, the committed write operations must have a serial order to prevent dirty writes. The pessimistic mechanism blocks conflicting writes until other writes have been committed or rolled back. Optimism does not lock these operations, it allows all operations to pass. If a connection is rolled back, other operations that modify the same piece of data will also be rolled back. At this level, shared buffers can be used without verification. This isolation level is best used when transactions are not required (such as read-only data sets), or transactions are modified only when the database is exclusive.
example : An archive database that is updated only offline, or an audit/logging table that is not used in transactions.
2. Read Committed
Submitted read can read any submitted status in the system, and can be buffered without verification (mixed status), as long as the changes in the current connection can be reflected in the results. The pessimism mechanism implements it as a monotonic view. Optimistic transactions isolate and store all changes until they are committed. Read committed uses a very optimistic mechanism that delays writing all changes until the transaction is committed. This form of optimistic isolation can implement complex write operations without blocking read operations, and it has no verification mode. Shared buffering can only be used in the committed state. This isolation level is best used when the result can use the old value and the transaction can only be used for write operations.
example : An online forum that does not need to display the latest posts, and its posts do not conflict with each other.
3. Monotonic View
The monotone view is an extension of read committed. The transactions in the monotone view will observe a monotone rising state in the database when executing. At this level, if there are obvious write transactions, pessimistic transactions will be blocked in read operations. Optimistic transactions will operate like read committed, isolate and save all changes, and verify their buffers to ensure that they are still valid. This level can periodically synchronize database replicas, and is best used when no transactions are required or only write transactions exist.
example : A user preference table that can only be modified by one person.
4. Snapshot Reads
Snapshot reading extends the monotonic view, which can ensure that query results can be reflected in a consistent snapshot of the database. The pessimism mechanism will block other write operations that affect the results during read operations. The optimistic mechanism allows other write operations, and notifies the read transaction that some part has changed and rolls back. To implement an optimistic mechanism, you must verify whether any parallel write operation has modified the results before the end of the read operation. If any, the results may be redone or rolled back. This verification process may simply check whether a write operation has occurred in the same table, or just check the changed query results. The optimistic isolation level can easily detect conflicts, and supports write operations while allowing concurrent read in operations. At this level, as long as the snapshot can be read, the database copy can be synchronized regularly. It is better to use this isolation level when there are few write operations, do not want to conflict with read operations, and the query results need to be consistent.
example :: A currency transposition table or query table that is frequently modified and only retains the latest value.
5. Cursor Stability
Cursor stability isolation extends read committed and is the default isolation level for many relational data. In this isolation level, if a pessimistic transaction is executed in a separate statement, it must specify the record it will modify. This can usually be achieved by appending the "FOR UPDATE" keyword after the "SELECT" query. In this case, other conflicting read/write pessimistic transactions will be blocked until the transaction ends. Optimistic transactions will track the version numbers of all modification records/entities that are verified at the time of submission. This is a popular optimistic isolation level and is supported by all mainstream object relational mapping libraries. In the Java persistence API, you can use FLUSH_ON_COMMIT (although the query may not affect local changes) to approach this level. If a conflict is detected, an OptimisticLockException exception can be thrown. This isolation can also be used in If Match or If Unmodified Since in the HTTP header. It can be used to compare the version or timestamp of the previous resource before updating. This level is best used when entities are changed by external information (not read from the database), or when changes do not overwrite each other.
example : A shared company directory or a wiki.
6. Repeatable Read
The repeatable read level extends cursor stability. It ensures that any data in a transaction will not be modified or removed during the transaction. Pessimistic transactions need to read locks on all records and block other services to modify these records. Optimistic transactions will track all records or entities and check whether they have been modified at the time of submission. This level is best used when the entity status can affect other entities, or when the transaction is composed of read/write operations.
example : An order tracking database that reads values from one entity and uses it to calculate other entity values.
7. Snapshot Isolation
Snapshot isolation extends snapshot read and repeatable read. It ensures that all read operations in a transaction can see the consistent snapshot in the database. Any read operation executed by the transaction will have the same result, regardless of whether they are executed in the transaction sooner or later. This is different from repeatable reads because snapshot isolation can prevent phantom reads (query results are constantly changing). Many relational databases use multi version concurrency control (also known as SERIALIZABLE) to support this level. The implementation method is a combination of lock and conflict detection. At this level, considering that it may conflict with the pessimistic or optimistic mechanism, the transaction must be prepared for rollback. The pessimistic mechanism will try to reduce the chance of conflict by locking resources, but these changes must be merged after the transaction is committed. The optimistic mechanism will also use multi version concurrency control, but it will not block other transactions that may cause potential conflict operations. Instead, it will roll back the conflicting transactions. This level of isolation is best used when transactions can read and modify multiple records.
example : A workflow system based on system state rules.
8. Serializability
Seriality is an extension of snapshot isolation. It requires all transactions to appear one after another, just like they have been serialized. The pessimism mechanism needs to lock all evaluated queries to prevent write operations from affecting these results. The optimistic mechanism tracks all evaluated queries, and uses a backward or forward verification mode at the end of the transaction to check whether there are parallel write operations that affect the parallel read in operation. If there are, it will roll back all transactions outside the conflict transaction. In this isolation level, any committed transaction will not change the token state of the system. This level of isolation is best used when full data consistency is required.
example : An accounting system that performs range queries to calculate new values.
summary
The following is a summary of the isolation levels mentioned in this article, which can help you find the level most suitable for your application.
Possible conflict types of transactions in different isolation levels:
|
Dirty writing |
Dirty reading |
Mixed state |
Inconsistent reading |
Overwrite |
Not repeatable |
Phantom reading |
Inconsistency |
| Read not submitted |
may not |
sure |
sure |
sure |
sure |
sure |
sure |
sure |
| Read committed |
may not |
may not |
sure |
sure |
sure |
sure |
sure |
sure |
| Monotonic view |
may not |
may not |
may not |
sure |
sure |
sure |
sure |
sure |
| Snapshot Reads |
may not |
may not |
may not |
may not d |
sure |
sure |
sure |
sure |
| cursor stability |
may not |
may not |
sure |
sure |
may not |
sure |
sure |
sure |
| Repeatable read |
may not |
may not |
sure |
sure |
may not |
may not |
sure |
sure |
| Snapshot Isolation |
may not |
may not |
may not |
may not |
may not |
may not |
may not |
sure |
| Serializability |
may not |
may not |
may not |
may not |
may not |
may not |
may not |
may not |
Best prerequisites for different isolation levels:
|
buffer |
Data synchronization |
Optimistic conflict mode |
Recommended actions |
example |
| Read not submitted |
Allow buffering |
Intermittent |
Detect dirty writes |
Cannot read and write concurrently |
archives |
| Read committed |
Allow buffering |
Intermittent |
No conflict detection |
Monotonous reading/writing |
Web forum |
| Monotonic view |
Must be verified |
Periodic |
No conflict detection |
Combined read in |
User preferences |
| Snapshot Reads |
Must be verified |
Periodic |
Compare the contents read in and modified |
Consistent read in |
Query Table |
| cursor stability |
Allow buffering |
Intermittent |
Compare the modified entity version |
CRUD service |
catalog |
| Repeatable read |
Allow buffering |
Intermittent |
Compare the read in entity version |
Read/write entities |
Order tracking |
| Snapshot Isolation |
Must be verified |
Periodic |
Compare the read in entity version |
Synchronize Entities |
Workflow |
| Serializability |
Must be verified |
Full synchronization |
Compare query and modification contents |
Improve data consistency |
account |
Data consistency is critical in database applications - it allows developers to use data in a distributed environment. Although strong consistency levels such as serializability provide a simple programming model, they can lead to excessive overhead, operation blocking or transaction rollback, which is unnecessary for many applications. If there are other problems, more appropriate isolation levels can be used to help developers and system architects better understand the requirements for data consistency while maintaining a balance between performance and overhead.
View the original English text: Eight Isolation Levels Every Web Developer Should Know 。
