Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "sample analysis of the three locking modes of SQL Server", which is easy to understand and well-organized. I hope it can help you solve your doubts. Let the editor lead you to study and study the "sample analysis of the three locking modes of SQL Server".

There are three sql server locking modes: shared (S lock), updated (U lock), and exclusive (X lock).

The S lock is a shared lock. If transaction T adds a shared lock to data A, other transactions can only add a shared lock to A, not an exclusive lock. Transactions that are allowed to share locks can only read data and cannot modify it.

Personally, it is understood that the operation on data A can only be SELECT, (in association, S lock is the initials of Select), and other transactions cannot carry out UPDATE or DELETE of A data.

The U lock is an update lock. Used in updatable resources. Prevents a common form of deadlock when multiple sessions are reading, locking, and possible subsequent resource updates.

Principle explanation:

Update lock

Updating the (U) lock prevents the usual form of deadlock. The general update mode consists of a transaction that reads the record, acquires the shared (S) lock of the resource (page or row), and then modifies the row, which requires the lock to be converted to an exclusive (X) lock.

If two transactions acquire a shared mode lock on a resource and then try to update the data at the same time, one transaction attempts to convert the lock to an exclusive (X) lock.

The conversion from shared mode to exclusive lock must wait for some time because the exclusive lock of one transaction is not compatible with the shared mode lock of other transactions; lock waiting occurs.

The second transaction attempts to acquire an exclusive (X) lock for update.

A deadlock occurs because both transactions are converted to exclusive (X) locks, and each transaction waits for the other transaction to release the shared mode lock.

To avoid this potential deadlock problem, use an updated (U) lock. Only one transaction at a time can obtain an update (U) lock for a resource.

If the transaction modifies the resource, the update (U) lock is converted to an exclusive (X) lock. Otherwise, the lock is converted to a shared lock.

I personally understand that, for example, Agraine B points a gun at each other and says, "I'll put down the gun when you put it down." everyone waits for each other to put down the gun, so there is a stalemate.

X lock is an exclusive lock, also called exclusive lock. The transaction should monopolize the insert, update and delete of A resources, otherwise it will be out of order; this is to protect the accuracy of the data operation again.

Personal understanding is, one at a time.

The above is all the contents of the article "sample Analysis of three locking modes of SQL Server". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.