) Introduction to a New Method for Solving deadlocks in SQLServer

Database Operation deadlocks are inevitable. This article does not discuss how deadlocks are generated, but focuses on solving deadlocks. Through SQLServer2005, 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. UseNorthwindbegintranins

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 ins

Database Operation deadlocks are inevitable. This article does not discuss how deadlocks are generated, but focuses onSolutionDeadlock, through SQL Server 2005, now seems to have a newSolutionMethod.

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 @#00:00:05@#　　

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, The T-SQL will not continue running once the statement has encountered an exception,

In the sacrificed connection above, the 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... catchSolutionDeadlock.

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 @#00:00:05@#　　　　

select * from Orders where CustomerId = @#ALFKI@#　　　　　　　　

commit　　　　

break　　

end try　　　　　　

begin catch　　　　

rollback　　　　

waitfor delay @#00:00:03@#　　　　

set @r = @r + 1　　　　

continue　　

end catchend

SolutionMethodRetry, of course, 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 there is another new problem: 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 it cannot directly throw the original exception, so you need to redefine the error. Now,SolutionThe solution becomes like this:

declare @r int set @r = 1

while @r <= 3begin　　

begin tran　　　　

begin try　　　　　　　

insert into Orders(CustomerId) values(@#ALFKI@#)　　　　

waitfor delay @#00:00:05@#　　　　

select * from Orders where CustomerId = @#ALFKI@#　　　　　　　　

commit　　　　

break　　

end try　　　　　　

begin catch　　　　

rollback　　　　

waitfor delay @#00:00:03@#　　　　

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.

========================================================== ======================================

Experience on deadlocks in SQL Server

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.

BelowMethodHelps 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.IntroductionOfMethodEasy 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 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 wantIntroductionA stored procedure can kill processes and locks through explicit calls.

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, hereIntroductionAnotherMethod.

-- 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

Summary

Although the deadlock cannot be completely avoided, we can minimize the deadlock andMethodTo detect deadlocks.

Bytes -------------------------------------------------------------------------------------------------

SQL Server deadlock Analysis

When a deadlock occurs in the SQL Server database, a trace file is not automatically generated as in ORACLE. Sometimes you can see the blocking information in [manage]-> [current activity] (sometimes the SQL Server Enterprise Manager does not respond because of too many locks ).

Set the trail 1204:

USE MASTER
Dbcc traceon (1204,-1)

Displays the status of all currently enabled TRACE tags:

Dbcc tracestatus (-1)

Cancel trail 1204:

Dbcc traceoff (1204,-1)

After tracking 1204 is set, some information about the SQL Server database deadlock is displayed in the database log file. However, the information is hard to understand and needs to be carefully checked against SQL Server online books. Find the database table based on the PAG lockMethod:

Dbcc traceon (3604)
Dbcc page (db_id, file_id, page_no)
Dbcc traceoff (3604)

Please refer to sqlservercentral.com for more details, but I learned a reason for the deadlock from CSDN.Method. With a slight modification, I removed the cursor operation and added some prompts. I wrote a system stored procedure sp_who_lock. SQL. The Code is as follows:

If exists (select * from dbo. sysobjects
Where id = object_id (n' [dbo]. [sp_who_lock] ')
And OBJECTPROPERTY (id, n' IsProcedure ') = 1)
Drop procedure [dbo]. [sp_who_lock]
GO
/*************************************** *****************
// Learn and rewrite
// Description: view the blocking and deadlock conditions in the database
**************************************** ****************/
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 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 ))
+ 'Process number. The SQL syntax executed by the process is as follows'
Else
Select 'process No. SPID: '+ CAST (@ spid as varchar (10) + 'be'
+ '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

Directly call the following when necessary:

Sp_who_lock

The process and SQL statement that cause the deadlock can be identified.

The built-in system stored procedures sp_who and sp_lock of SQL Server can also be used to find blocking and deadlocks.IntroductionOfMethodEasy to use. For more information about tracenum, see http://www.sqlservercentral.com /.

We can also set the lock timeout time (in milliseconds) to shorten the time range that a deadlock may affect:

For example:

Use master
Seelct @ lock_timeout
Set lock_timeout 900000
-- 15 minutes
Seelct @ lock_timeout

In fact, the deepest reason for all deadlocks is: Resource Competition
Performance 1:
A user A accesses Table A (locking table A) and then accesses Table B
Another user B accesses table B (Table B is locked) and then attempts to access Table
At this time, user A has locked table B because user B has to wait for user B to release table B to continue. Well, the old man will have to wait honestly.
Similarly, user B has to wait for user A to release Table A to continue.
SolutionMethod:
This deadlock is caused by bugs in your program. There is no other way to adjust the logic of your program.
Carefully analyze the logic of your program,
1: Try to avoid locking two resources at the same time
2: When two resources must be locked at the same time, ensure that the resources should be locked in the same order at any time.

Performance 2:
User A reads A record and modifies it.
This is the record modified by user B.
Here, the nature of the lock in user A's transaction is increased from the share lock attempt to the exclusive lock (for update), and the exclusive lock in user B must wait for A to release because A has A share lock.
The shared lock is released, and the exclusive lock that A cannot rise due to the exclusive lock of B cannot be released, so A deadlock occurs.
Such deadlocks are relatively hidden, but they often occur in projects that are a little larger.
SolutionMethod:
Let user A's transactions (that is, the first read and then write operations), In the select statement, Update lock is used.
Syntax:
Select * from table1 with (updlock) where ....

How to unlock a locked table in the database

Author: anonymous author: Unknown clicks: 106 updated:
When we operate databases, database tables may be locked due to improper operations, so we are often at a loss and do not know how to unlock these tables, the "sessions" in the menu "tools" of pl/SQL Developer can query existing sessions, but it is difficult to find that session is locked, it is more difficult to find the locked session. The following is a query statement that can query the locked session. As follows:
SELECT sn. username, m. SID, sn. SERIAL #, m. TYPE,
DECODE (m. lmode,
0, 'none ',
1, 'null ',
2, 'row Share ',
3, 'row Excl .',
4, 'share ',
5,'s/Row Excl .',
6, 'clusive ',
Lmode, LTRIM (TO_CHAR (lmode, '123 '))
) Lmode,
DECODE (m. request,
0, 'none ',
1, 'null ',
2, 'row Share ',
3, 'row Excl .',
4, 'share ',
5,'s/Row Excl .',
6, 'clusive ',
Request, LTRIM (TO_CHAR (m. request, '20140901 '))
) Request,
M. id1, m. id2
FROM v $ session sn, v $ lock m
WHERE (sn. SID = m. sid and m. request! = 0) -- the lock request is blocked.
OR (sn. SID = m. SID -- no lock request exists, but the locked object is locked by other session requests
AND m. request = 0
AND lmode! = 4
AND (id1, id2) IN (
SELECT s. id1, s. id2
FROM v $ lock s
WHERE request! = 0 AND s. id1 = m. id1
AND s. id2 = m. id2)
)
Order by id1, id2, m. request;

If you know sid and SERIAL # Through the above query, you can kill it.
Alter system kill session 'sid, SERIAL #';