These days in a project, not how to engage in a lot of threads before, and now start, only learn to do the side.
At the beginning, the program error is, is the hint of a deadlock, just beginning, I have no experience, thought is the program code deadlock, with lock code, the program to lock up, run the program, found that there is a time-out phenomenon, access to data, later concluded that the problem is that the deadlock occurs on the SQL Server, Specially find the next SQL2008 book, read the next, the explanation of the deadlock.
Shared locks
Exclusive lock
Update lock
Custom Locks
And then we checked the information,querying for SQL deadlock stored procedures
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 (SMALLINT,BL), spid smallint)
IF @ @ERROR <>0 RETURN @ @ERROR
Insert into #tmp_lock_who (SPID,BL) Select 0, blocked
From (SELECT * from sys.sysprocesses where blocked>0) a
Where NOT EXISTS (SELECT * from sys.sysprocesses where blocked>0) b
where A.blocked=spid)
Union select spid,blocked from sys.sysprocesses where blocked>0
IF @ @ERROR <>0 RETURN @ @ERROR
--Find the number of records for the staging table
Select @intCountProperties = Count (*), @intCounter = 1
From #tmp_lock_who
IF @ @ERROR <>0 RETURN @ @ERROR
If @intCountProperties =0
Select ' now has no blocking and deadlock information ' as message
--Cycle start
While @intCounter <= @intCountProperties
Begin
--Take the first record
Select @spid = spid, @bl = bl
From #tmp_lock_who where id = @intCounter
Begin
If @spid =0
Select ' Causes the database deadlock: ' + CAST (@bl as VARCHAR (10)) + '
The process number, which executes the SQL syntax as follows '
Else
Select ' Process number spid: ' + CAST (@spid as VARCHAR (10)) + ' by ' + '
Process number spid: ' + CAST (@bl as VARCHAR (10)) + ' block,
The SQL syntax the current process executes is as follows '
DBCC InputBuffer (@bl)
End
--The loop pointer moves down
Set @intCounter = @intCounter + 1
End
drop table #tmp_lock_who
return 0
end analyzed some of the deadlocks by customizing the lock wiht NOLOCK (note dirty reads, use with the business logic) with Rowlock, but still a deadlock occurred looking for code, The main problem was found in some begin TRAN transactions because there is no commit or rollback when using the Delete and update commands in the transaction, and if another thread is also executing the current table Delete command, There will be blocking, blocking is the premise of the deadlock, Oracle does not seem to have this problem, Oracle is a lock line, this time, I based on the SQL command, the update after the condition of the field, the database is indexed, such as delete from table where b= 3 Indexing the field B automatically turns the lock table into a lock row, noting that if the index establishes a B field and your condition is the C field, no lock-line effect will occur
