Transaction deadlock error

Question

Transaction deadlock error

jagadeesanpv

Ten Centuries

Points: 1006
More actions
December 23, 2009 at 10:39 pm

#214990

Hi Experts,
We are trying to do a package to execute all sql scripts
when we try below mentioned script we got transaction deadlock error
begin tran
exec sp_addtype 'Udd_dummy','int'
go
create proc dummy_sp
as
begin
Declare @Dummy table(dummy_col Udd_dummy)
end
go
rollback
Error:
Msg 1205, Level 13, State 55, Procedure dummy_sp, Line 4
Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Msg 3903, Level 16, State 1, Line 1
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
Please provide your suggestions
Thanks,
Jagadeesan

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.

DBCC TRACEON(1222,-1)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jagadeesanpv Ten Centuries Points: 1006 More actions · Answer 2

I am getting this problem only in Sql server 2008(SP1) server, when I try the same script in Sql server 2005 it was working fine

Here is the Deadlock graph

<deadlock-list>

<process-list>

create proc dummy_sp

as

begin

Declare @Dummy table(dummy_col Udd_dummy)

end </frame>

</executionStack>

create proc dummy_sp

as

begin

Declare @Dummy table(dummy_col Udd_dummy)

end

</inputbuf>

</process>

</process-list>

<resource-list>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</metadatalock>

</resource-list>

</deadlock>

<process-list>

create proc dummy_sp

as

begin

Declare @Dummy table(dummy_col Udd_dummy)

end </frame>

</executionStack>

create proc dummy_sp

as

begin

Declare @Dummy table(dummy_col Udd_dummy)

end

</inputbuf>

</process>

</process-list>

<resource-list>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</metadatalock>

</resource-list>

</deadlock>

</deadlock-list>

Abhijit More SSCertifiable Points: 6897 More actions · Answer 3

the reason you are getting the deadlock is sql server executes both the statments as two different batch

it first executes the " begin tran exec sp_addtype 'Udd_dummy','int' part in one batch and the "create proc" in another bacth

if you see here in first bacth you are creating the type which is in transaction , not committed. that's why you are getting the deadlock.

Abhijit - http://abhijitmore.wordpress.com

jagadeesanpv Ten Centuries Points: 1006 More actions · Answer 4

Thanks Abhijit More for your effort

I tried in a different ways

begin tran

exec sp_addtype 'Udd_dummy','int'

go

create table dummy_tbl

(name Udd_dummy)

go

rollback

begin tran

exec sp_addtype 'Udd_dummy','int'

go

create proc dummy_sp

as

begin

Declare @Dummy Udd_dummy

select @dummy

end

go

rollback

both are working fine in sql server 2008

but when I try with table variable I am getting deadlock error

begin tran

exec sp_addtype 'Udd_dummy','int'

go

create proc dummy_sp

as

begin

Declare @Dummy table(dummy_col Udd_dummy)

end

go

rollback

but this script is working fine in Sql server 2005(RTM)

Is it microsoft problem?

Suresh B. SSC-Insane Points: 22986 More actions · Answer 5

Use CREATE TYPE instead of SP_ADDTYPE

What is the purpose using a transaction here?

jagadeesanpv Ten Centuries Points: 1006 More actions · Answer 6

Hi Suresh,

Even create type also throwing same deadlock error

We have some set of deployment scripts if a script contains user defined data type, that UDD is used in some create stored procedure in the same script, either all should commit or all should rollback

it should not happen partially(like UDD alone created and stored procedure got some error so it was not created)

I am getting this transaction deadlock when we declaring table variable with that UDD

all other scenarios working fine

Thanks for your effort

jagadeesanpv Ten Centuries Points: 1006 More actions · Answer 7

Atlast we found that this is known bug from sql server 2005 sp1 :w00t:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=365876