SQL Server Deadlock solving

Question

Hi expert,

My Developer created Application script with BEGIN TRAN statement respectively to update data, I know this cause the Deadlock

hmm, what should I do with this script as my advice to my Developer..??

thanks 4 all U'reply

regards

Answer 1

Hi,

BEGIN TRAN alone cannot cause deadlock. Deadlock can occur eg if 2 trans are waiting for each other, etc. If you would like to resolve deadlocks take a look on these articles:

• http://support.microsoft.com/kb/832524
• http://msdn.microsoft.com/en-us/library/ms178104.aspx

I hope it helps.

Janos

---

There are 10 type of people. Those who understand binary and those who do not.
My Blog

Answer 2

Hi Janos,

There are 2 transaction was executed at same time with 2 BEGIN TRAN in Updating data, it cause deadlock??

Regards

Answer 3

Hi,

It depends. as per BOL:

"A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. The following graph presents a high level view of a deadlock state where:

• Task T1 has a lock on resource R1 (indicated by the arrow from R1 to T1) and has requested a lock on resource R2 (indicated by the arrow from T1 to R2).

• Task T2 has a lock on resource R2 (indicated by the arrow from R2 to T2) and has requested a lock on resource R1 (indicated by the arrow from T2 to R1).

• Because neither task can continue until a resource is available and neither resource can be released until a task continues, a deadlock state exists."

Janos

---

There are 10 type of people. Those who understand binary and those who do not.
My Blog

Answer 4

Deadlocks are common in some respects, an can pretty much never be completely avoided. Your code should be ready for this error to occur, and deal with it.

Generic advice aside, deadlocks more typically occur when processes lock too much, and my immediate guess for your situation is that the begin tran is just shining a light on a case where you are doing modifications without using a key, or minimally an indexed column.  So two concurrent processes are locking most of a table and they get into the situation that is gong to be described in the articles posted by Janos Berke.  It generally happens because something seems to work in a single user/dev machine, then as you start to move up in the chain locks start hurting you.

So I would suggest (without actually seeing your batch(s), or plan(s), or anything else, that you need to optimize the queries within the transaction batch to try to limit the amount of data locked.  It could be something more difficult, but that would take more analysis of your exact situation.

---

Louis

Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

Answer 5

Hi Urang,

As Louis said, in most part of the cases, deadlocks caused by excessive locking. And in a lot of cases (assuming you don't update data in the large batches) it happens due non-optimized queries.

Let I try to give you the example and some background. When you modify the row, you place exclusive lock (X) which is held till end of transaction. When you search for the row for update (e.g. non-optimized update), SQL Server scans the data and places update (U) lock to the row while reading/comparing it with query filter. In case, if the row needs to be updated, SQL Server converts (U) lock to (X) lock. Otherwise it releases U lock. The problem is that (U) are incompatible with other (U) and (X) locks. So you can have your update (U) lock blocked by another session (X) lock during the scan, even if you're trying to update absolutely different rows. And 2 sessions could easily deadlock each other when you have multiple statements involved in the transactions.

In fact, you can even have the situation when deadlocks caused by readers in addition to writers. In read committed, repeatable read and serializable isolation levels SQL Server uses shared (S) locks when reading the data. In Read committed mode it releases locks immediately when row is read, in other modes locks held till end of transaction. (S) locks are incompatible with (X) locks and would be blocked. Obviously during the scans, you have much more rows to read/more locks to acquire.

Example below shows you the scenario when you have the deadlock when 2 sessions deals with completely separate sets of data. First update statements set (X) locks on 2 rows. Second select statements SCAN the table and as result tries to acquire locks on every row. They would be blocked when they try to read rows updated before (with X locks) and as result you'd have the deadlock (both sessions are waiting on each other)

use tempdb
go

set nocount on
go

/********************
Create Data
*********************/
create table dbo.Data
(
	Id int not null,
	Value int not null,
	
	Placeholder char(200) not null
		constraint Def_Data_Placeholder
		default 'a',
			
	constraint PK_Data
	primary key clustered(Id)
)
go

;with CTE(ID)
as
(
	select 1 
	
	union all
	
	select ID + 1
	from CTE
	where ID < 50000
)
insert into dbo.Data(Id, Value)
	select ID, ID
	from CTE
option (MAXRECURSION 0)
go 

/***********************
Session 1 code
************************/
-- STEP 1
set transaction isolation level read committed
begin tran
	update dbo.Data
	set Value = 0
	where ID = 0
	/* Stop here and run first update from the session 2 below */
-- STEP 2	
	select * 
	from  dbo.Data
	where Value = 1
	
-- do not commit or rollback
-- rollback


/***********************
Session 2 code
************************/
-- Run after Step 1 of session 1
set transaction isolation level read committed
begin tran
	update dbo.Data
	set Value = 40000
	where ID = 40000
	
	select * 
	from  dbo.Data
	where Value = 40001
-- RUN Step 2 from above	
-- do not commit or rollback
-- rollback

BEGIN TRAN cannot directly cause deadlock. Same time, as I said, exclusive locks (and in some transaction isolation levels -  shared locks) held till end of transaction. So longer your transaction is, longer you have locks in place. On the other hand, removing transactions would hurt data consistency which could be even bigger problem.

I put set of blog posts with high level overview how locking works in SQL Server. This can help you to understand how it works internally and hopefully gives you some ideas how to troubleshoot the issue. http://aboutsqlserver.com/2011/09/28/locking-in-microsoft-sql-server-table-of-content/ 

---

Thank you!

My blog: http://aboutsqlserver.com

Answer 6

Can you post the script here? 

BEGIN TRAN used with proper ISOLATION LEVELS actually prevents deadlocks. In your case, BEGIN TRAN is not responsible for deadlock. Can't say much without seeing the script.

Answer 7

Please read Bible" of deadlocks

http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx
http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/