Eight isolation levels required for Web Development

Acid is the cornerstone of database theory. It defines four properties required by a theoretically reliable database: atomicity, consistency, isolation, and persistence. Although these four attributes are important, isolation is the most flexible. Most databases provide some available isolation levels, and many databases now have additional layers to create finer isolation granularity. The wide range of isolation-level applications is mainly because the relaxation of isolation constraints often increases scalability and performance by several orders of magnitude.

 

The main goal of concurrency control is to ensure that the transaction is isolated without affecting other transactions. To achieve high-level isolation, you must sacrifice performance. Concurrency control can be implemented using a pessimistic or optimistic mechanism. Most relational databases use a pessimistic mechanism to achieve write optimization. The pessimistic mechanism uses a lock, which can block some operations or perform 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 transactions that may access and modify resources. However, the optimistic mechanism does not adopt any locks. It only relies on conflict detection to maintain transaction isolation. The optimistic mechanism uses conflict detection to allow all read operations and verify the consistency at the end of the transaction. If a conflict is detected, the transaction will be rolled back or redone. Most Web servers use read optimization, so optimistic mechanisms are used. By allowing all read operations, the optimistic mechanism ensures high read/write throughput and Data Consistency without changing resources. Serial consistency is one of the oldest and highest isolation levels available. It is favored because it provides a simple programming model, that is, each time only one transaction can operate on a given resource, this avoids many potential resource problems. Despite this, most applications (especially Web applications) do not adopt such high-level isolation, from the end user's point of view, this is impractical-any application with a large user base will have a few minutes of latency when accessing shared resources, which will quickly reduce the number of users. Weak Consistency and eventual consistency are everywhere in large-scale distributed data sources, such as the web. Several successful large-scale Web applications (such as eBay and Amazon) show optimistic (optimistic) weak consistency, which is much better in terms of scalability than traditional pessimistic (pessimistic) mechanisms. This article will look at eight different isolation levels. Learn to relax Data Consistency constraints as appropriate. You can use these eight isolation levels in your applications for better performance and scalability.

 

The isolation levels listed below are used to help Web developers better understand the constraints placed in their programming models, helps System Architects and developers discuss how to select the most effective isolation level while maintaining the necessary data integrity. They are listed in the order of least isolation (uncommitted read) to maximum isolation (serialization.

1. Read uncommitted)

The uncommitted read isolation level requires very few isolation between transactions. For each read operation, you can see the write operations (dirty reads) waiting for in the transaction ). However, the committed write operations must have a serial sequence to prevent dirty writes. The pessimistic mechanism blocks conflicting write operations until other write operations have been committed or rolled back. Optimistic mechanism does not lock these operations, it will allow all operations to pass. If a connection is rolled back, other operations to modify the same data block will also be rolled back. At this level, the shared buffer can be used without verification. It is recommended that you use this isolation level when you do not need a transaction (such as a read-only dataset), or when the transaction is only modified when the database is exclusively occupied.

Example: An archive database that is updated only offline, or an audit/login (audit/logging) table that is not used in a transaction.

2. Read committed)

Committed read can read any submitted status in the system, and buffer the status without verification (mixed state). Only the changes in the current connection can be reflected in the results. The pessimistic mechanism implements a monotonous view. Optimistic transactions isolate all changes in the storage so that they are not available 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 authentication mode. The shared buffer can only be used in the submitted status. The isolation level is best when the old value can be used in the result, 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 monotonic view is an extension of the read committed, in which the transaction will observe a monotonically rising state in the database during execution. At this level, if there is a significant write transaction, the pessimistic transaction will be blocked in the read operation. Optimistic transactions operate as they have been committed, isolate and save all changes, and verify their buffering to ensure that they are still valid. At this level, you can regularly synchronize Database Replicas. It is recommended that you do not need a transaction or only have a write operation transaction.

Example: A user preference table that can be modified by only one person.

4. snapshot read (snapshot reads)

Snapshot reading extends the monotonous view, which ensures that the query results are reflected in consistent snapshots of the database. The pessimistic mechanism hinders write operations that affect other results during read operations. The optimistic mechanism allows other write operations, and notifies the read transaction that a part has changed and rolled back. To implement an optimistic mechanism, you must verify whether any parallel write operations have modified the results before the reading operation ends. If so, the results may be redone or rolled back. This check process may simply check whether a write operation is performed in the same table, or simply check the modified query results. Optimistic isolation can easily detect conflicts and support write operations when concurrent read operations are allowed. At this level, you can regularly synchronize database copies as long as you can read snapshots. It is best to use this isolation level when there are few write operations and you do not want to conflict with the read operation, and the query results must be consistent.

Example: A query is more frequent than a query, and only the latest value of the currency transposition table or query table is retained.

5. cursor Stability)

Cursor stability isolation extends Read committed and is the default isolation level for many relational data. At this isolation level, if a pessimistic transaction is executed in a separate statement, you 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 track all modified records/entity version numbers verified at the time of submission. This is a very popular optimistic isolation level, so it is supported by all mainstream object relationship ing libraries. In Java persistent API, you can use flush_on_commit (although the query may not affect local changes) to reach this level. If a conflict is detected, you can throw an optimisticlockexception. This isolation can also be used in if-match or if-unmodified-since of the HTTP header domain. It can be used to compare the version or timestamp of the previous resource before update. It is best to use this level when the entity is changed from external information (not read from the database) or the changes are not overwritten.

Example: A shared company directory or wiki.

6. Repeatable read)

The Repeatable read level extends the stability of the cursor, ensuring that any data in the transaction is not modified or removed during the transaction. Pessimistic transactions need to read the locks on all records and block other services to modify these records. Optimistic transactions track all records or entities and check whether they have been modified at the time of submission. This level is recommended when the entity state 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 the snapshot reading and repeatable reading, which ensures that all read operations in the transaction can see consistent snapshots in the database. Any read operations performed by transactions have the same results, regardless of whether they are executed in the transaction. This is different from repeated reads, because Snapshot isolation can prevent Phantom reads (the query results are constantly changing ). Many relational databases use multi-version concurrency control (also called serializable) to support this level. The implementation method is a combination of lock and conflict detection. At this level, the transaction must be prepared for rollback, considering that it may conflict with the pessimistic or optimistic mechanism. The pessimistic mechanism attempts to reduce the chance of conflict by locking resources, but these changes must be merged after the transaction is committed. The optimistic mechanism also uses multi-version concurrency control, but it does not block other transactions that may generate potential conflicting operations. Instead, it rolls back conflicting transactions. It is best to use this isolation level when transactions can read and modify multiple records.

Example: A workflow system based on system status rules.

8. serializability)

Serializability is an extension of Snapshot isolation. It requires that all transactions must appear one by one, just as they have been serialized. Pessimistic mechanisms need to lock all evaluated queries to prevent write operations from affecting these results. The optimistic mechanism tracks all evaluated queries and uses a backward verification or forward verification mode at the end of the transaction to check whether parallel write operations affect the parallel read operations, if yes, it will roll back all transactions outside the conflicting transaction. At this isolation level, any committed transaction will not change the system's characterization status. It is best to use this isolation level when full data consistency is required.

Example: An account system that performs a range query to calculate the new value.

Summary

The following is the isolation level summary table, which helps you find the level that best suits your application.

Possible Conflict types of transactions at different isolation levels:

	Dirty writing	Dirty read	Hybrid status	Inconsistent read	Overwrite	Repeatable	Phantom read	Inconsistency
Uncommitted read	No	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Committed read	No	No	Yes	Yes	Yes	Yes	Yes	Yes
Monotonous View	No	No	No	Yes	Yes	Yes	Yes	Yes
Snapshot reading	No	No	No	NoD	Yes	Yes	Yes	Yes
Cursor Stability	No	No	Yes	Yes	No	Yes	Yes	Yes
Repeatable reading	No	No	Yes	Yes	No	No	Yes	Yes
Snapshot isolation	No	No	No	No	No	No	No	Yes
Serializability	No	No	No	No	No	No	No	No

Optimal prerequisites for different isolation levels:

	Buffer	Data Synchronization	Optimistic conflict Mode	Recommended actions	Example
Uncommitted read	Buffer allowed	Intermittent	Detect dirty writes	Concurrent read/write failures	Archives
Committed read	Buffer allowed	Intermittent	No conflict detection	Monotonous read/write	Web forum
Monotonous View	Must be verified	Periodic	No conflict detection	Combined reading	User preferences
Snapshot reading	Must be verified	Periodic	Compare read and modify content	Consistent reading	Query a table
Cursor Stability	Buffer allowed	Intermittent	Compare the modified object version	Crud Service	Directory
Repeatable reading	Buffer allowed	Intermittent	Compare the read object version	Read/write entities	Order Tracking
Snapshot isolation	Must be verified	Periodic	Compare the read object version	Synchronize objects	Workflow
Serializability	Must be verified	Complete synchronization	Compare query and modification content	Improve data consistency	Accounts

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 cause overhead, Operation blocking, or transaction rollback, this is unnecessary for many applications. If there are other problems, you can use a more appropriate isolation level to help developers and System Architects better understand the data consistency requirements while maintaining a balance between performance and overhead.

View Original English text:Eight isolation levels every Web Developer shocould know.