SQL Server lock mechanism overview and cursor lock

Recently I encountered a problem related to cursor locking. In short, it is related to http://support.microsoft.com/kb/953948.

So far, reading Uncommitted has been a good experience, but the customer does not want dirty data.

Method. I have never studied the cursor in depth because I think the cursor efficiency is low. This time I will talk about the lock and mention the cursor.

1. Basic Concepts
The lock is used to solve the contention for resources (rows, pages, tables. It mainly solves the following two problems:
A. Read Old Data
B. How can I handle the changes when multiple users try?
(That is, to solve the problem of preventing you from reading or reading)
Concurrent Control is required to handle the B problem.

Concurrency Control:
There are two types of optimism and pessimism.
Pessimistic means taking data into possession until you are willing to let it go. High contention of data)

.
Optimistic is that the public data is very generous. When user A submits A data update request, the system first checks whether the data has been read by user

Modified. If it has been modified, an error is reported and A's update request is rolled back.
By the way, concurrency control can be implemented at the API level, that is, ADO, ADO. NET, ole db, and ODBC.

Concurrency Control is defined at the transaction isolation level. It determines that when reading the row being modified by other transactions, it is returned:
1. stopped until other transactions are released (Serializable, highest level)
2. Read the version before other transactions are locked (Read committed, database default)
3. Read versions not committed by other transactions (Read uncommitted, lowest level)
Another Repeatable read has a lower level than Read committed and may be read repeatedly.

2. Lock
Generally, the lock is not automatically required. Instead, the lock manager of the SQL Server database engine is used to manage automatic settings. Several major locks

:
Shared (S) Shared lock. When there is a S lock on the resource, no other transactions can modify the data.
Update (U) updates the lock to prevent simultaneous operation of multiple sessions and avoid deadlocks. When two transactions compete, one transaction will replace the S lock with X

Lock, but this conversion may take some time, so two transactions may be converted to the X lock, waiting for the other party to release the S lock, resulting in death

Lock. To avoid the problem, it is best to use the U Lock. Only one transaction can obtain the U Lock at a time.
Exclusive (X) Exclusive lock to prevent multiple updates from working at the same time, and no other transaction can modify data
Intent (I) Intention lock, used to form the IS, IX, SIX lock, used to prevent other transactions from making lower-level locks modified in an invalid way

Advanced lock to improve lock conflict detection efficiency
Schema (Sch-S) Schema lock. When performing operations dependent on the table Schema, there are two types: Schema modification (Sch-M) and Schema stability (Sch-S ).

-S ).

To obtain the lock-related information, you can use the following methods:
1. SQL Server Profiler
You can choose to add
2. sys. dm_tran_locks
DMV Mode
3. sp_lock (in the past, it seems that sp_lock2 ?)
Backward compatible features, but still quite useful, as well as sys. syslockinfo
Other methods include SMO and DMO.

For deadlocks, refer to the U Lock mentioned above. SQL Server has a deadlock monitor. If circular dependency is detected, select

One of them as a victim (really poor ...), An error occurred while terminating the transaction.

3. cursor lock
The cursor type varies with the locking method.
The static cursor locks each row.
Dynamic cursors obtain the lock only when the row is extracted.
When submitting a cursor, you can choose to close it or choose to continue opening it. It is controlled by CURSOR_CLOSE_ON_COMMIT.

SQL Server has four options for concurrency control of cursors:
READ_ONLY
Locating and updating through a cursor is not allowed
OPTIMISTIC WITH VALUES
T-SQL cursors are not supported. Optimistic Concurrency Control. The interval between opening the cursor and updating allows other processes to update.
OPTIMISTIC WITH ROW VERSIONING
T-SQL cursors are not supported. Optimistic Concurrency Control. Based on timestamp
Scroll locks. Pessimistic Concurrency Control

Here, we will give you a chance to add more details.

References:
Locking and Row Versioning
Http://msdn.microsoft.com/en-us/library/ms187101.aspx

Cursor Locking
Http://msdn.microsoft.com/en-us/library/aa172580.aspx