Database-the practice of locking

One: The concept of the lock

According to the Writing technology blog, should be the concept of the lock to do an introduction, I think, can click into this blog students, presumably have heard of the lock. So I try to express it in concise language.

Lock definition: Locks are mainly used in multi-user environments to ensure database integrity and consistency of the technology.

Lock interpretation: When multiple users access data concurrently, the database generates multiple transactions concurrently accessing the same data. Without control of concurrent operations, incorrect data can be read and stored, destroying the integrity and consistency of the database. When a transaction makes a request to the system before it operates on a data object, it locks it. After locking, the transaction has some control over the data object.

II: Classification of Locks

The concept of lock is very simple, simple to use a few words to describe its purpose. But the classification of locks is obviously a bit more complicated.

The classification of locks, in the textbook, the network is a lot of two dimensions to describe. A dimension is divided by the function of lock, and one dimension is divided by concept. 09, I did a database training tutorial, the lock classification to cut out the pendulum.

After a few years, it seems that PPT looks very rough. I can't compare these ppt templates, but the content is still classic.

Three: Key words of the lock

Shared locks, locks such as locks, the database engine is automatically managed and optimized, usually when writing SQL, there is little to care about the lock keyword.

But today is a learning attitude to look at the blog, so you have to have to use a few key words.

SELECT *  fromApplog with(HOLDLOCK)/*Shared Locks*/SELECT *  fromApplog with(UPDLOCK)/*Update lock*/ SELECT *  fromApplog with(XLOCK)WHERELogid='aa599a4e-b727-4a65-8010-00001661765e';/*Exclusive Lock*/  SELECT *  fromApplog with(Rowlock)WHERELogid='6BE2C680-0C9F-43FA-9B4E-00000A6C1CEF';/*Row Lock*/ SELECT *  fromApplog with(Tablockx)/*Large Capacity Update lock*/ SELECT *  fromApplog with(Xlock,rowlock)WHERELogid='aa599a4e-b727-4a65-8010-00001661765e';/*combination of locks used*/ /*The XLOCK itself locks the data line, and Tablockx locks the entire table .*/SELECT *  fromApplog with(NOLOCK)/*no lock, dirty data occurs when a transaction is rolled back*/   SELECT *  fromApplog with(READPAST)/*Ignore lock-out data (row data, page data)*/

Four: The occurrence of the deadlock

For example, the current database with two users in use,

User 1:

BEGIN TRAN SELECT *  from WHERE Logid  ='a10ba165-6e52-4afb-9ea8-000000d6b90a'; UPDATE SET = + WHERE = ' a10ba165-6e52-4afb-9ea8-000000d6b90a ';

User 2:

 begin  tran  select  *  from  Applog where  logid  '    ; 

For example, user 1, user 2 simultaneously execute SELECT, user 1 to record a shared lock, user 2 to record also added a shared lock, when the user 1 SELECT execution, ready to perform update, according to the lock mechanism, the user 1 of the shared lock needs to rise
class to an exclusive lock to perform the next update.

Before you can upgrade an exclusive lock, you must wait for other shared locks on the record to be released, but the shared lock is released only after the transaction has ended. Because the user 2 of the shared lock does not release the user 1 and so on (such as User 2 release the shared lock, oneself good upgrade to an exclusive lock), the same time, also because the user 1 shared lock is not released and cause the user 2 wait. The deadlock is happening.

Five: Lock-free Query Tips

Open two query windows: one executes the following statement:

CREATE TABLE A (    INT  ,    NVARCHAR(BEGINTRAN  INSERTVALUES ('1','a' )-- open a transaction without committing or rolling back, at which point the INSERT statement produces an exclusive lock that will not be released

In another window, do the following:

Select COUNT (*from with (NOLOCK)-- No lock query, will find the result is 1selectCOUNT( *  from  with (Readpast) -- ignores all locked records, at this time 0

Then execute SELECT * from a-the result is not found, will wait indefinitely, because the exclusive lock is not released, the default query is not compatible with the shared lock, so it waits for the release of the lock, it will return the results, even if there is a lot of data in the table, and the exclusive lock only a record, but, The query statement also waits for the lock of this record to be released before returning the result. This is manual manually set because the lock does not release the deadlock caused by deadlocks (not waiting for each other, but one side endless waiting!) ）。

Welcome to the discussion.

Database-the practice of locking