SQL Server lock

1. exclusive lock
Write in an SQL connection as follows:
Begin tran
-- Select * From A with (updlock)
Update a set [name] = 'wq' where [ID] = 2 -- the set value here cannot be the same (that is, it cannot be originally name = 'wq' and set name = 'wq' ), otherwise, SQL Server will optimize

Without locks
Waitfor delay '00: 00: 08'
Commit tran
In another SQL connection, write as follows:
Select * from
It is found that the SQL statement in the second connection must wait until the transaction in the first connection is completed. This is because the update statement in the first connection automatically adds an exclusive lock.

 

 

2. default SQL Server lock
That is, when the insert, update, or delete command is run, SQL Server automatically uses the exclusive lock.
Select statement: 1. When the transaction isolation level is read committed and read uncommitted, no lock is applied.
2. When the isolation level of the transaction is Repeatable read and serializable, it is a shared lock, both holdlock

 

 

3. transaction isolation level:
1. Repeatable read
After this is set in the transaction of the first SQL connection, the update and delete operations cannot be performed on the transaction tables in the first connection in the second SQL connection.

Insert operation, and the newly inserted rows are included in the subsequent reading of the current transaction of the first connection.
2. serializable
Place a range lock on the dataset to prevent other users from updating the dataset or inserting rows into the dataset before the transaction is completed. This is the most restrictive level among the four isolation levels. Because of the concurrency level

Low, so this option should be used only when necessary. This option is used to set holdlock for all tables in all select statements in the transaction. That is, the first SQL connection

After the preceding settings, you cannot perform update, delete, or insert operations on the transaction tables in the first connection in the second SQL connection.

 

4. Update locks
Write in an SQL connection as follows:
Begin tran
Begin tran
Select * From A with (updlock) Where [ID] in (2, 3, 4)
Waitfor delay '00: 00: 04'
Commit tran
In another SQL connection, write as follows:
Select * From A with (updlock) Where [ID] = 4
It is found that the SQL statement in the second connection must wait until the transaction in the first connection is completed, this is because the update lock of the second connection thinks that the update lock in the first connection may be modified.

The transaction is converted to an exclusive lock. Therefore, the transaction will be executed only after the execution of the first connection is completed. If the SQL statement in the second connection is written as follows: Select * From A with (holdlock) Where [ID]

= 4, it is not necessary to wait until the execution of the first connection transaction is completed.