Relationship between locks and isolation levels

Relationship between locks and isolation levels

One. net project, occasionally some strange problems, recently made up effort to troubleshoot and found that it is caused by a deadlock, the following is a deadlock situation map tracked using SQL Server Profiler:

When I see this picture, it is really drunk to tell the truth. It is rare that such a complicated deadlock occurs in a business system that is not complex yet. The cause of this problem is very simple, that is, the system writes 90% of the services in the storage process, and almost every step of the operation needs to be completed through the operation table repeatedly. As a result, the memory usage of a large server is always below 20%, the instantaneous CPU pressure is huge, and it is difficult to search for bugs without logs. Due to this architecture design problem, it seems impossible to modify it from the business point of view. Only some temporary solutions can be found, for example, to modify the isolation level and deadlock timeout time, I don't want to talk much about this architecture solution consisting of stored procedures. presumably many architecture designers won't do this design, to write some basic information about database transaction isolation levels and locks, This article focuses on Microsoft's SqlServer database.

A necessary condition for deadlock is that two or more processes compete for the same resource in a concurrent environment. For example, an ellipse indicates a process, and a rectangle indicates a resource, it can be seen that multiple processes occupy the Batch table, causing a deadlock. To solve this problem, you must first understand why there is a lock and the basic knowledge of the lock.

In actual business processing, many steps are required. For example, for the most typical bank transfer, A must first subtract 100 from account A to transfer RMB 100 to account B, then add 100 to account B. This action is uninterrupted. From the program perspective, in order to ensure the completion of this action, the transaction has four features: ACID, however, transactions are just a concept. They are not specific technical means. How can we ensure the four features of ACID in transactions? Relational databases are generally implemented by means of transaction logs and locks. Transaction logs ensure atomicity and consistency. Transaction logs are completed by the database itself. Therefore, developers generally cannot access transaction logs, the general principle is to write logs before performing all the actual data operations. If the database encounters an accident (such as power failure), the logs can be recovered from the row in time. The lock ensures isolation, and ensures that multiple transactions can request the same data in a serialized manner.

But in the real world, many operations require parallel operations. Simply and rudely serial-based operations (adding, deleting, modifying, and querying) through locks will inevitably not work. Therefore, resources need to be locked in a more refined manner, starting from two aspects, first, add the lock type for the lock: Shared lock (S), exclusive lock (X), and a special update lock (U ), there are also intent locks and architectural locks that are almost not in touch. for explanations of each lock, see lock mode. for compatibility between them, see lock compatibility. Second, for the lock resource range (Lock granularity), there are a total of 11 granularities. For the description of each granularity, MSDN has more detailed information and will not paste it here. The link is attached: lock granularity and level. As for the optimistic and pessimistic locks we often see at ordinary times, you can refer to the relevant content in Hibernate. I have provided several articles at the end of this Article for your reference.

In general, in actual development, there is relatively little chance of directly operating the various locks in the database, and more is the use of the four isolation levels provided by the database, uncommitted read, committed read, Repeatable read, and serializable, there is also a special row-based version of the committed read isolation level, it can also be included in the committed read, the row version-based isolation level is the optimistic lock processing method. What is the relationship between the isolation level and the lock? In general, the isolation level is an overall packaging solution for locks. I understand that isolation encapsulates locks. They have their own advantages and disadvantages for these four types of isolation, as shown in the following table:

Isolation level	Dirty read	Update loss	Non-repeated read	Phantom read
Uncommitted Read: Read Uncommited	Yes	Yes	Yes	Yes
Committed Read: Read commited	No	Yes	Yes	Yes
Repeatable Read	No	No	No	Yes
Serializable read: Serializable	No	No	No	No

From: http://blog.csdn.net/shuaihj/article/details/14163713

From the perspective of tables, the isolation level increases from top to bottom. The lower the level, the more problems are caused, such as dirty reads and loss of updates. However, the higher the level, this means that more locks need to be managed, parallel processing is not possible, and performance is damaged. Therefore, when designing a system, we only need to select a suitable isolation level based on business needs. There is a set of lock-making solutions at an isolation level. This design aims to balance performance and functionality.

To select a proper isolation level, you must first understand the problems caused by dirty reads, lost updates, non-repeated reads, and Phantom reads. Here is an example: after reading the examples in this article, most people basically understand the interaction between transactions. The following is a summary in their own language:

Dirty read: transaction A rolls back transaction B after reading the uncommitted changes (update, Insert, Delete) Record of transaction B, and transaction A reads non-existent data;

Unrepeatable read: After transaction A reads data for the first time, transaction B changes (Delete and Update). When transaction A reads data, it is found that the data is inconsistent in one transaction;

Phantom read: similar to non-repeated read, but Phantom read is used for Insert operations. When transaction A first reads 10 rows of data in the table, transaction B inserts (Insert) one, when transaction A reads data again, it finds that it has become 11 rows, resulting in an illusion;

Lost update: Transaction A and transaction B get the same data 1. After A is added to 1 and changed to 2, B also adds 2 to 3 on the basis of 1, due to the late submission of B, the final data is changed to 3, covering the operation of transaction A, which is called the loss update;

The following figure shows a dynamic picture of the isolation level and lock relationship from the network. (After uploading, the figure does not move, and CSDN is no longer capable of speaking -_-!) :

Http://static.oschina.net/uploads/img/201207/09074335_5YM8.gif

The following are some excellent articles:

Http://blog.itpub.net/13651903/viewspace-1091664/

Http://www.cnblogs.com/wghao/archive/2010/01/17/1650120.html

Http://www.cnblogs.com/chillsrc/archive/2013/04/13/3018386.html

Http://www.cnblogs.com/xwdreamer/archive/2012/07/30/2615357.html