[SQL Server] Lock knowledge

Not sorted.

 

 

-- 1
Although deadlocks cannot be completely avoided, the number of deadlocks can be minimized. Minimizing deadlocks can increase transaction throughput and reduce system overhead because there are only a few transactions:
Rollback cancels all tasks performed by the transaction.
The application resubmit the rollback during the deadlock.
The following methods help minimize deadlocks:
Access objects in the same order.
Avoid user interaction in transactions.
Keep the transaction brief and in a batch.
Use a low isolation level.
Use bind connection.
Access objects in the same order
If all concurrent transactions access objects in the same order, the possibility of deadlock will be reduced. For example, if two concurrent transactions obtain the lock on the supplier table and then obtain the lock on the part table, the other transaction is blocked on the supplier table before one transaction is completed. After the first transaction is committed or rolled back, the second transaction continues. No deadlock occurs. The stored procedure is used to modify all the data to standardize the order of objects to be accessed.
Avoid user interaction in transactions
Avoid writing transactions that contain user interaction, because running batch processing without user interaction is much faster than manually responding to queries, for example, responding to application request parameter prompts. For example, if the transaction is waiting for user input, and the user goes to lunch or even goes home for a weekend, the user suspends the transaction so that it cannot be completed. This will reduce the system throughput, because any lock held by the transaction will be released only when the transaction is committed or rolled back. Even if no deadlock occurs, other transactions accessing the same resource will be blocked, waiting for the transaction to complete.
Keep the transaction brief and in a batch
A life-and-death lock is often used to concurrently execute multiple transactions that require long running in the same database. The longer the transaction runs, the longer it takes to hold the exclusive lock or update the lock, blocking other activities and possibly causing a deadlock.
Keeping transactions in one batch can minimize the number of network communications to and from the transaction, reduce the possible latency of completing the transaction and release the lock.
Low isolation level
Determine whether the transaction can run at a lower isolation level. The committed read operation allows the transaction to read the data that has been read (not modified) by another transaction without waiting for the completion of the first transaction. Using a lower isolation level (for example, commit read) instead of a higher isolation level (for example, serializable read) can shorten the time for holding shared locks, thus reducing lock contention.
Bind a connection
Bind the connection so that two or more connections opened by the same application can cooperate with each other. Any lock obtained by the secondary connection can be held as the lock obtained by the primary connection, and vice versa, so it will not block each other.
Deadlock Detection
If a deadlock occurs, how can we check which SQL statement or stored procedure is causing the deadlock?
At this time, we can use the following stored procedures to detect the process and SQL statements that cause the deadlock. The built-in system stored procedures sp_who and sp_lock of SQL Server can also be used to find blocking and deadlocks, but the method described here is not easy to use.
 
Use master
Go
Create procedure sp_who_lock
As
Begin
Declare @ spid int, @ BL int,
@ Inttransactioncountonentry int,
@ Introwcount int,
@ Intcountproperties int,
@ Intcounter int
Create Table # tmp_lock_who (
Id int identity (1, 1 ),
Spid smallint,
BL smallint)
 
If @ error <> 0 return @ Error
 
Insert into # tmp_lock_who (spid, BL) Select 0, blocked
From (select * From sysprocesses where blocked> 0)
Where not exists (select * from (select * From sysprocesses where blocked> 0) B
Where a. Blocked = spid)
Union select spid, blocked from sysprocesses where blocked> 0
If @ error <> 0 return @ Error

-- Find the number of records in the temporary table
Select @ intcountproperties = count (*), @ intcounter = 1
From # tmp_lock_who
 
If @ error <> 0 return @ Error
 
If @ intcountproperties = 0
Select 'no blocking and deadlock information' as message
-- Start of Loop
While @ intcounter <= @ intcountproperties
Begin
-- Retrieve the first record
Select @ spid = spid, @ BL = bl
From # tmp_lock_who where id = @ intcounter
Begin
If @ spid = 0
Select '+ Cast (@ BL as varchar (10) +' causes database deadlock. The SQL statement executed by 'process No. 'is as follows'
Else
Select 'process No. spid: '+ Cast (@ spid as varchar (10) +' quilt '+' process no. spid: '+ Cast (@ BL as varchar (10 )) + 'blocking, the SQL syntax executed by the current process is as follows'
DBCC inputbuffer (@ BL)
End
-- Move the loop pointer down
Set @ intcounter = @ intcounter + 1
End
Drop table # tmp_lock_who
Return 0
End
 
Killing locks and processes
How to manually kill processes and locks? The simplest way is to restart the service. But here we will introduce a stored procedure. through explicit calling, the process and lock can be killed.
Use master
Go
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [p_killspid] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [p_killspid]
Go
Create proc p_killspid
@ Dbname varchar (200) -- Name of the database for which the process is to be disabled
As
Declare @ SQL nvarchar (500)
Declare @ spid nvarchar (20)
Declare # TB cursor
Select spid = cast (spid as varchar (20) from Master .. sysprocesses where dbid = db_id (@ dbname)
Open # TB
Fetch next from # TB into @ spid
While @ fetch_status = 0
Begin
Exec ('Kill '+ @ spid)
Fetch next from # TB into @ spid
End
Close # TB
Deallocate # TB
Go
-- Usage
Exec p_killspid 'newdbpy'
 
View lock Information
How can I view the details of all locks in the system? In the enterprise management manager, we can see some process and lock information. Here we introduce another method.
-- View lock Information
Create Table # T (req_spid int, obj_name sysname)
Declare @ s nvarchar (4000)
, @ Rid int, @ dbname sysname, @ ID int, @ objname sysname
Declare TB cursor
Select distinct req_spid, dbname = db_name (rsc_dbid), rsc_objid
From master .. syslockinfo where rsc_type in (4, 5)
Open TB
Fetch next from TB into @ RID, @ dbname, @ ID
While @ fetch_status = 0
Begin
Set @ s = 'select @ objname = Name from ['+ @ dbname +'] .. sysobjects where id = @ id'
Exec sp_executesql @ s, n' @ objname sysname out, @ ID int ', @ objname out, @ ID
Insert into # T values (@ RID, @ objname)
Fetch next from TB into @ RID, @ dbname, @ ID
End
Close TB
Deallocate TB
Select process id = A. req_spid
, Database = db_name (rsc_dbid)
, Type = case rsc_type when 1 then 'null Resource (not used )'
When 2 then 'database'
When 3 then 'file'
When 4 then 'index'
When 5 then 'table'
When 6 then 'page'
When 7 then 'key'
When 8 then 'Extended disk region'
When 9 then 'rid (row ID )'
When 10 then 'application'
End
, Object ID = rsc_objid
, Object name = B. obj_name
, Rsc_indid
From master .. syslockinfo a left join # t B on A. req_spid = B. req_spid
Go
Drop table # T

-- 2
-- Try

Set nocount on;
If object_id ('T1 ') is not null
Drop table T1
Go
Create Table T1 (ID int primary key, col1 int, col2 nvarchar (20 ))
Insert T1 select 1,101, 'A'
Insert T1 select 2,102, 'B'
Insert T1 select 3,103, 'C'
Go

If object_id ('t2 ') is not null
Drop table T2
Go
Create Table T2 (ID int primary key, col1 int, col2 nvarchar (20 ))
Insert T2 select 1,201, 'X'
Insert T2 select 2,202, 'y'
Insert T2 select 3,203, 'Z'

Go
Table data generation:
/*
T1:
Id col1 col2
------------------------------------------
1 101
2 101 B
3 101 C

T2:
Id col1 col2
------------------------------------------
1 201 X
2 201 y
3 201 Z
*/

Prevent dead ends:
1. Minimal blocking. The less blocking, the less chance of death and death
2. Sort the table in descending order of transactions (for example, "dead letter 2)
3. Check the transaction details 1205 in the processing handler and resubmit the task at the time of the startup.
4. Add a route entry in the processing handler to log on.
5. Proper Use of indexes (for example, "Death limit 1" and "Death limit 3)
When a life-or-death event occurs, the event is automatically submitted, and the event can be aborted by the day.

Dead shard 1 (INDEX ):
-- Accept window 1
-- Step 1:
Begin tran
Update T1 set col2 = col2 + 'a 'where col1 = 101

-- Step 3:
Select * From T2. where col1 = 201
Commit tran

-- Accept window 2

-- Step 2:
Begin tran
Update T2 set col2 = col2 + 'B' where col1 = 203

-- Step 4:
Select * from T1 where col1 = 103
Commit tran

 

-- Receive window 1: receive the dead rows, and then click window 2. The result is as follows:

/*
Interest 1205, 20171000013, 20171000051, Row 3
Transaction (processing order recognition 53) is killed by another processing order on the indexing resource and has been selected as a dead object. Please renew the transaction.
*/

-- Answer window 2: Result

/*
------------------------------------------
3 103 C
*/

Handling Method:
-- Create an index for columns col1 in Table T1 and table T2
Create index ix_t1_col1 on T1 (col1)
Create index ix_t2_col1 on T2 (col1)
Go

-- Accept window 1
-- Step 1:
Begin tran
Update T1 set col2 = col2 + 'a 'where col1 = 101

-- Step 3:
Select * From T2 with (Index = ix_t2_col1) Where col1 = 201 -- because the number of tables is small, you can only specify the index prompt to ensure that SQL server uses the index.
Commit tran

 

-- Accept window 2

-- Step 2:
Begin tran
Update T2 set col2 = col2 + 'B' where col1 = 203

-- Step 4:
Select * from T1 with (Index = ix_t1_col1) Where col1 = 103 -- because the number of tables is small, you can only specify the index prompt to ensure that SQL server uses the index.
Commit tran

 

-- Accept window 1:
/*
Id col1 col2
------------------------------------------
1 201 X

(A data column is affected)

*/
-- Accept window 2
/*
Id col1 col2
------------------------------------------
3 103 C

(A data column is affected)
*/

Dead limit 2 (in the descending order of the orders table ):

-- Accept window 1:
-- Step 1:
Begin tran
Update T1 set col1 = col1 + 1 where id = 1

-- Step 3:
Select col1 from T2 where id = 1
Commit tran

 

-- Accept window 2:
-- Step 2:
Begin tran
Update T2 set col1 = col1 + 1 where id = 1

-- Step 4
Select col1 from T1 where id = 1
Commit tran

-- Accept window 1:

/*
Col1
-----------
201

(A data column is affected)
*/

-- Accept window 2:

/*
Col1
-----------
Interest rate: 1205, 20171000013, 20171000051, Row 1
Transaction (processing order recognition 54) is the final object of another processing order and has been selected as a dead object on the indexing resource. Please renew the transaction.
*/

Handling Method:

-- Change the sequence of the problem table

-- Accept window 1:
-- Step 1:
Begin tran
Update T1 set col1 = col1 + 1 where id = 1

-- Step 3:
Select col1 from T2 where id = 1
Commit tran

-- Accept window 2:
-- Step 2:
Begin tran
Select col1 from T1 where id = 1 -- Wait Until window 1 is submitted
-- Step 4
Update T2 set col1 = col1 + 1 where id = 1
Commit tran

Dead limit 3 (single table ):

-- Accept window 1:

While 1 = 1
Update T1 set col1 = 203-col1 where id = 2

-- Accept window 2:
Declare @ I nvarchar (20)
While 1 = 1
Set @ I = (select col2 from T1 with (Index = ix_t1_col1) Where col1 = 102); -- because the number of tables is small, you can only specify the index prompt to ensure that SQL server uses the index

-- Accept window 1
/*
Interest 1205, 20171000013, 20171000051, row 4
Transaction (processing order recognition 53) is killed by another processing order on the indexing resource and has been selected as a dead object. Please renew the transaction.
*/

Handling Method:
1. Except the non-clustered index on col1, this affects the select speed and is not desirable.
Drop index ix_t1_col1 on T1
2. Create a overwriting Index
A. Drop index ix_t1_col1 on T1
B. Create index ix_t1_col1_col2 on T1 (col1, col2)

The statement information is queried through SQL Server Profiler:

Dynamic SQL Server Profiler -- samples the actual example -- selects the dynamic listener for the event -- displays all events
Optional values:
TSQL--SQL: stmtstarting
Locks -- deadlock graph (this is a new sql2005 event that generates an XML value containing the dead part information)
-- Lock: The progress event in the deadlockchain dead queue, which can mark the ID of the dead queue and perform operations with the queue.
-- Lock: deadlock: the event is killed.

-- 3
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 lock generated by the client code, not the lock generated by the SQL server itself.

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.

-- 4
-- Handle deadlocks

View the current process or deadlock process and automatically kill the dead Process

Because it is intended for dead, if there is a deadlock process, you can only view the deadlock Process
Of course, you can use Parameter Control to check only the deadlock process, whether there is a deadlock or not.

-- Producer build 2004.4 --

-- Call example

Exec p_lockinfo
--
Create proc p_lockinfo
@ Kill_lock_spid bit = 1, -- whether to kill the deadlock process; 1: Kill; 0: only show
@ Show_spid_if_nolock bit = 1 -- if there is no deadlock in the process, whether the normal process information is displayed, 1 is displayed, 0 is not displayed
As
Declare @ count int, @ s nvarchar (1000), @ I int
Select id = identity (INT, 1, 1), flag,
Process id = spid, thread id = kpid, block process id = blocked, Database ID = dbid,
Database Name = db_name (dbid), user id = uid, user name = loginame, accumulated CPU time = CPU,
Login Time = login_time, number of opened transactions = open_tran, Process status = status,
Workstation name = hostname, application name = program_name, workstation process id = hostprocess,
Domain name = nt_domain, NIC address = net_address
Into # T from (
Select flag = 'deadlocked process ',
Spid, kpid, A. Blocked, dbid, uid, loginame, CPU, login_time, open_tran,
Status, hostname, program_name, hostprocess, nt_domain, net_address,
S1 = A. spid, S2 = 0
From master .. sysprocesses a join (
Select blocked from Master... sysprocesses group by blocked
) B on A. spid = B. Blocked where a. Blocked = 0
Union all
Select '_ victim _',
Spid, kpid, blocked, dbid, uid, loginame, CPU, login_time, open_tran,
Status, hostname, program_name, hostprocess, nt_domain, net_address,
S1 = blocked, S2 = 1
From master .. sysprocesses A where blocked0
) A order by S1, S2

Select @ COUNT = @ rowcount, @ I = 1

If @ COUNT = 0 and @ show_spid_if_nolock = 1
Begin
Insert # T
Select flag = 'normal process ',
Spid, kpid, blocked, dbid, db_name (dbid), uid, loginame, CPU, login_time,
Open_tran, status, hostname, program_name, hostprocess, nt_domain, net_address
From master .. sysprocesses
Set @ COUNT = @ rowcount
End

If @ count0
Begin
Create Table # T1 (ID int identity (255), a nvarchar (30), B INT, eventinfo nvarchar ))
If @ kill_lock_spid = 1
Begin
Declare @ spid varchar (10), @ sign varchar (10)
While @ I = @ count
Begin
Select @ spid = process ID, @ sign = sign from # t where id = @ I
Insert # T1 exec ('dbcc inputbuffer ('+ @ spid + ')')
If @ sign = 'deadlocked process' exec ('Kill '+ @ spid)
Set @ I = @ I + 1
End
End
Else
While @ I = @ count
Begin
Select @ s = 'dbcc inputbuffer ('+ Cast (process ID as varchar) +') 'from # t where id = @ I
Insert # T1 exec (@ s)
Set @ I = @ I + 1
End
Select a., the SQL statement of the Process = B. eventinfo
From # t a join # t1 B on A. ID = B. ID
End
Go

-- 5

SQL Server lock type (SQL) favorites
1. holdlock: Keep the shared lock on the table until the entire transaction ends, instead of releasing the added lock immediately after the statement is executed.
2. nolock: no shared or exclusive locks are added. When this option takes effect, uncommitted data or "dirty data" may be read. This option is only applicable to select statements.
3. paglock: Specify to add a page lock (otherwise, a table lock may be added ).
4. readcommitted performs scanning with the same lock semantics as transactions running at the committed read isolation level. By default, SQL Server 2000 operates at this isolation level ..
5. readpast skips the data rows that have been locked. This option will allow the transaction to skip the data rows that have been locked by other transactions when reading data, rather than blocking until other transactions release the lock, readpast is only applicable to select statement operations in transaction operations at the Read committed isolation level.
6. readuncommitted: equivalent to nolock.
7. repeatableread: Set the transaction to a read-only isolation level.
8. rowlock: Use row-level locks instead of coarse-grained page-level locks and table-level locks.
9. serializable: scan with the same lock semantics as transactions running at the serializable read isolation level. Equivalent to holdlock.
10. tablock: Table-level locks are used instead of Row-level or page-level locks. After the statement is executed, SQL Server releases the lock. If both holdlock is specified, the lock remains until the transaction ends.
11. tablockx: Specifies the use of the exclusive lock on the table. This lock can prevent other transactions from reading or updating data in the table until the statement or the entire transaction ends.
12. updlock: Specify the update lock when reading data in the table, instead of the shared lock. The lock remains until this statement or the entire transaction ends, updlock is used to allow users to read data first (and does not block other users from reading data), and ensure that when the data is updated later, during this period, the data was not modified by other users.

This article is from the csdn blog. For more information, see httpblog. csdn. netarrow_gxarchive200806012501368.aspx.

-- Solve the deadlock problem in SQL Server 2005

Database Operation deadlocks are inevitable. This article does not discuss how deadlocks are generated, but focuses on solving deadlocks. through SQL Server 2005, there seems to be a new solution.

When the following SQL statement is placed in two different connections and executed simultaneously within five seconds, a deadlock will occur.

Use northwind
Begin tran
Insert into orders (customerid) values (@ # alfki @#)
Waitfor delay @ #000005 @#
Select from orders where customerid ==## alfki @#
Commit
Print @ # End Tran @#

The SQL Server method to deal with deadlocks is to sacrifice one of them, throw an exception, and roll back the transaction. In SQL Server 2000, once a statement exception occurs, the T-SQL will not continue to run, the above is sacrificed in the connection, print @ # End Tran @ # statement will not be run, so it is difficult for us to further process the deadlock in the T-SQL of SQL Server 2000.

Now it's different, SQL Server 2005 can capture exceptions in the T-SQL, which provides us with a way to handle deadlocks:

Try... catch is used below to solve the deadlock.

Set xact_abort on
Declare @ r int
Set @ r = 1
While @ r = 3
Begin
Begin tran
　　
Begin try
Insert into orders (customerid) values (@ # alfki @#)
Waitfor delay @ #000005 @#
Select from orders where customerid ==## alfki @#
　　　　
Commit
Break
End try
　　　　
Begin catch
Rollback
Waitfor delay @ #000003 @#
Set @ r = @ r + 1
Continue
End catch
End

The solution is retry, but error capture is a prerequisite. Waitfor after rollback does not

The solution is retry, but error capture is a prerequisite. Waitfor after rollback is indispensable. It takes some time to wait after a conflict. The number of @ retries can be adjusted to meet different requirements.

But now we are faced with a new problem and the error is hidden. Once the problem occurs more than three times, the exception will not be thrown. SQL Server 2005 has a raiseerror statement that can throw an exception, but cannot directly throw the original exception. Therefore, you need to redefine the error. Now, the solution becomes like this.

Declare @ r int
Set @ r = 1
While @ r = 3
Begin
Begin tran
　　
Begin try
Insert into orders (customerid) values (@ # alfki @#)
Waitfor delay @ #000005 @#
Select from orders where customerid ==## alfki @#
　　　　
Commit
Break
End try
　　　　
Begin catch
Rollback
Waitfor delay @ #000003 @#
Set @ r = @ r + 1
Continue
End catch
End
If error_number () 0
Begin
Declare @ errormessage nvarchar (4000 );
Declare @ errorseverity int;
Declare @ errorstate int;
Select
@ Errormessage = error_message (),
@ Errorseverity = error_severity (),
@ Errorstate = error_state ();
Raiserror (@ errormessage,
@ Errorseverity,
@ Errorstate
);
End

I hope that in the future, SQL Server 2005 will be able to directly throw the original exception, for example, providing a non-parameter raiseerror.

So the scheme is a bit bloated, But encapsulating the deadlock problem into the T-SQL helps to clarify the responsibility, improve the clarity of the high-level system. Now, the dataaccess code may no longer need to consider the deadlock issue.