Custom locks in SQL Server

1. Handle deadlocks and set deadlock priority

A deadlock occurs when multiple users apply for different blockages. The applicant has part of the blockages and waits for the blockages of other users.

Set deadlock_priority can be used to control the session response mode when a deadlock occurs. If both processes lock data and wait until other processes release their own locks, each process can release its own locks, that is, deadlock occurs.

2. Process timeout and set the lock timeout duration.

@ Lock_timeout returns the current lock timeout settings for the current session, in milliseconds

Set lock_timeout settings allow applicationsProgramSets the maximum time for a statement to wait for a blocked resource. When the waiting time of a statement is greater than the lock_timeout setting, the system automatically cancels the blocking statement and Returns Error 1222 to the application that "exceeds the lock request timeout period ".

Example

In the following example, the lock timeout period is set to 1,800 milliseconds.

Set lock_timeout 1800

3) set the transaction isolation level.

4) use the table-level locking prompt for select, insert, update, and delete statements.

5) configure the index lock Granularity

You can use the sp_indexoption system stored procedure to set the locking granularity for indexes.

6. view the lock Information

1. Execute exec sp_lock to report lock information.

2. Press Ctrl + 2 in the query analyzer to view the lock information.

Seven precautions

How to avoid deadlocks

1. When using a transaction, try to shorten the logical processing process of the transaction and commit or roll back the transaction as soon as possible;

2. Set the deadlock timeout parameter to a reasonable range, for example, 3 minutes to 10 minutes. If the timeout period is exceeded, the operation is automatically abandoned to avoid process suspension;

3. Optimize the program to check and avoid deadlock;

4. All scripts and SP should be carefully tested before the version is correct.

5. All SP should have error handling (via @ error)

6. do not modify the default transaction level of SQL Server. Force lock not recommended

How to lock a row-table database

Eight lock problems

1. How to lock a row in a table

SET transaction isolation level read uncommitted

Select * from Table rowlock where id = 1

2. Lock a table in the database

Select * from table with (holdlock)

Lock statement:

SYBASE:

Update table set col1 = col1 where 1 = 0;

MSSQL:

Select col1 from table (tablockx) where 1 = 0;

ORACLE:

Lock table in exclusive mode;

No one else can operate after the lock, until the locked user is unlocked and unlocked with commit or rollback

Several examples help you better understand

Set Table1 (A, B, C)

A B C

A1 B1 C1

A2 B2 C2

A3 B3 C3

1) exclusive lock

Create two connections

Execute the following statement in the first connection:

Begin tran

Update Table1

Set a = 'A'

Where B = 'b2'

Waitfor delay '00: 00: 30' -- wait 30 seconds

Commit tran

Execute the following statement in the second connection

Begin tran

Select * From Table1

Where B = 'b2'

Commit tran

If the preceding two statements are executed at the same time, the SELECT query must wait 30 seconds until the update statement is executed.

2) shared lock

Execute the following statement in the first connection:

Begin tran

Select * From Table1 holdlock-holdlock artificial lock

Where B = 'b2'

Waitfor delay '00: 00: 30' -- wait 30 seconds

Commit tran

Execute the following statement in the second connection

Begin tran

Select a, c from Table1

Where B = 'b2'

Update Table1

Set a = 'A'

Where B = 'b2'

Commit tran

If the preceding two statements are executed simultaneously, the SELECT query in the second connection can be executed.

However, update can only be executed 30 seconds after the first transaction releases the shared lock and changes it to the exclusive lock.

3) deadlock

Add Table2 (D, E)

D e

D1 E1

D2 E2

Execute the following statement in the first connection:

Begin tran

Update Table1

Set a = 'A'

Where B = 'b2'

Waitfor delay '00: 00: 30'

Update Table2

Set d = 'd5'

Where E = 'e1'

Commit tran

Execute the following statement in the second connection

Begin tran

Update Table2

Set d = 'd5'

Where E = 'e1'

Waitfor delay '00: 00: 10'

Update Table1

Set a = 'A'

Where B = 'b2'

Commit tran

At the same time, the system detects a deadlock and terminates the process.

Add:

Table-level locking prompt supported by SQL Server

Holdlock holds the shared lock until the entire transaction is completed. It should be released immediately when the locked object is not needed, equal to the serializable transaction isolation level.

The nolock statement does not issue a shared lock when it is executed. Dirty reads are allowed, which is equal to the read uncommitted transaction isolation level.

Paglock uses multiple page locks when a table lock is used

Readpast allows the SQL Server to skip any locked rows and execute transactions. This applies to the read uncommitted transaction isolation level, which only skips the RID lock and does not skip pages, regions, and table locks.

Rowlock force row lock

Tablockx forces the use of an exclusive table lock, which prevents any other transactions from using this table during the transaction.

Uplock forces the use of updates during table reading without sharing locks

Application lock:

The application lock is the client.CodeThe generated lock, instead of the lock generated by SQL Server.

Two processes for processing application locks

Sp_getapplock: Lock application resources

Sp_releaseapplock unlock application resources

Note: What is the difference between locking a database table?

Select * from table with (holdlock) other transactions can read the table, but cannot update or delete the table.

Select * from table with (tablockx) other transactions cannot read, update, and delete tables.

SQL code

1. How to lock a row in a table

Execute in connection

SET transaction isolation level Repeatable read

Begin tran

Select * From tablename with (rowlock) Where id = 3

Waitfor delay '00: 00: 05'

Commit tran

If you execute

Update tablename set colname = '10' where id = 3 -- Wait 5 seconds.

Update tablename set colname = '10' where ID <> 3 -- immediate execution

2. Lock a table in the database

Select * from table with (holdlock)

Note: What is the difference between locking a database table?

Select * from table with (holdlock)

Other transactions can read tables, but cannot update or Delete tables.

Select * from table with (tablockx)

Other transactions cannot read, update, or delete tables.