SQL Server lock Summary

 

MSSQLConcurrency Control Summary

 

LProblems in concurrent operations:

1. Update loss

A) the original value in the database is 200, and user a updates it to 300 in a transaction, and user B updates it to 400 in a transaction, however, transaction B rollback is caused for some reason. After the execution of AB, the value in the database is still 200, so the update of A is lost.

2. Dirty read

A) the original value in the database is 200, and user a updates it to 300 in a transaction. Before the transaction does not have commit & rollback, user B reads 300, if transaction a is rollback later, dirty reads will occur to transaction B.

3. Repeated read is not allowed.

A) the original value in the database is 200. In a transaction, when user a first selects data, the result is 200, and then user B updates data to 300 in another transaction, then, when a then performs the Select Operation, the result is no longer 200, but 300, which indicates that the two select results for the same row of data in the same transaction are inconsistent, this row of data cannot be read repeatedly.

4. Virtual read

A) there are 10 rows in the database with a median value greater than 200. In a transaction, after the first select operation, user a returns 10 rows, then user B inserts a new data in another transaction, such as 300. Then, when user a performs the Select Operation, the result is no longer 10, but 11, the new row of data is a virtual read.

LIsolation level:

1. Last authorization read

A) The isolation level is the lowest and updates cannot be lost.

B) Allow multiple transactions to read data at the same time, but do not allow multiple transactions to write data at the same time

2. Authorized read

A) dirty reads and updates cannot be lost.

B) The Rows modified in the transaction are prohibited from being selected by other transactions before the transaction is committed.

C) Ensure that a transaction does not read the data modified by other transactions but committed at the end.

D) It is the default isolation level of SQL Server.

3. Authorize the read Snapshot

A) dirty reads and updates cannot be lost.

B) The row that has been modified in the transaction is allowed to be selected by other transactions before the transaction is committed. The historical version of the row is accessed (which can be set through read_commited_snapshot ).

C) Ensure that a transaction does not read the data modified by other transactions but committed at the end.

4. Repeatable reading

A) dirty reads, loss of updates, and repeated reads are not allowed.

B) The Rows modified in the transaction are prohibited from being selected by other transactions before the transaction is committed.

C) The row read in the transaction. Modification to other transactions is also prohibited before the transaction is committed, allowing other transactions to read

D) All shared locks must also be kept until the end of the transaction.

5. serialization

A) the strictest transaction isolation prohibits the concurrency of related transactions, and the related transactions can only be executed one by one.

B) All shared locks must also be kept until the end of the transaction.

C) not only need to lock the read data, but also use the 'key range lock' to lock the potential data to prevent other transactions from inserting new data.

LConcurrency Control Model

1. pessimistic

A) In a pessimistic model, writers always block readers and writers, and readers also block writers.

2. optimistic

A) In the optimistic model, the only possibility of blocking is that the writer blocks the writer.

B) among the five isolation levels above, only authorized snapshot reading is an optimistic model.

LVarious locks

1. Shared lock

A) when reading data, a shared lock will be added to one row, one page, or the entire table.

B) The shared lock will not impede the reading operations of other transactions, but will cause the modification operations of other transactions to wait.

2. exclusive lock

A) exclusive locks are applied to insert, update, and delete operations.

B) the exclusive lock is locked until the transaction ends.

3. Update the lock

A) When performing a modification operation, you must first find the location of the record waiting for modification. An update lock will be added to protect the data. When you find the correct location, update locks are upgraded to exclusive locks.

B) compatible with the shared lock

4. Intention lock

A) when a row of data is modified, an exclusive lock will be applied to the row, and the row will be locked on the page or the table will apply an exclusive lock to prevent other transactions from being on its parent container, for example, you can obtain exclusive locks at the page or table level.

LLock observation

1. SYS. dm_tran_locks

A) This view is used to replace the sp_lock stored procedure to observe the lock status in the system.

B) Select object_name (Object ID): Which table can be viewed?

2. deadlock

A) Cyclic deadlock

I. Two transactions hold each other's desired locks, and a deadlock will occur.

B) Conversion deadlock

I. Both transactions hold a shared lock on the same page and want to convert the shared lock into an exclusive lock. Because the two sides share the lock, it cannot be completed, resulting in a deadlock.

C) Priority of deadlocks

I. You can set deadlock_priority to set the priority. When a deadlock occurs, a transaction with a lower priority will be the victim.

Ii. When the priorities are the same, sessions with low rollback or sales volumes will be the victims.

 

A classic deadlock is attached:

Sqlconnection conn = new sqlconnection (connectionstring );
Conn. open ();
Sqltransaction TRAN = conn. begintransaction ();
String sql1 = "Update lock1 set C1 = C1 + 1 ";
String sql2 = "select * From lock1 ";
Executenonquery (Tran, sql1 );

Executenonquery (null, sql2 );

Recurrence lock:

Query1:

Begin tran
Insert [user] values (1, 'asdf ')
Waitfor delay '00: 00: 10 ';

Rollback Tran;

Query2:

Select * from [user]

Solution:

A ).Put the SELECT statement before the update statement:The SELECT statement is not in the transaction, and the S lock is released after execution;
B ).Add the SELECT statement to the transaction:Executenonquery (Tran, sql2 );

C ).SelectAdd With (nolock)