Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Transaction deadlock error Expand / Collapse
Author
Message
Posted Wednesday, December 23, 2009 10:39 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, March 30, 2013 6:06 AM
Points: 532, Visits: 224
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
Post #838855
Posted Thursday, December 24, 2009 12:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 41,528, Visits: 34,444
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 2008, MVP
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

Post #838883
Posted Thursday, December 24, 2009 1:54 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, March 30, 2013 6:06 AM
Points: 532, Visits: 224
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>
<deadlock victim="process17b390">
<process-list>
<process id="process17b390" taskpriority="0" logused="0" waitresource="METADATA: database_id = 9 USER_TYPE(user_type_id = 257)" waittime="3952" ownerId="6605296" transactionname="@Dummy" lasttranstarted="2009-12-24T13:59:29.017" XDES="0x8ec4e78" lockMode="Sch-S" schedulerid="1" kpid="5936" status="suspended" spid="61" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2009-12-24T13:59:29.017" lastbatchcompleted="2009-12-24T13:59:29.013" clientapp="Microsoft SQL Server Management Studio - Query" hostname="WIN-PW5HZFYFFM5" hostpid="7808" loginname="WIN-PW5HZFYFFM5\Administrator" isolationlevel="read committed (2)" xactid="6605293" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="adhoc" line="4" sqlhandle="0x01000900f8a93230b04e3c06000000000000000000000000">
create proc dummy_sp
as
begin
Declare @Dummy table(dummy_col Udd_dummy)
end </frame>
</executionStack>
<inputbuf>
create proc dummy_sp
as
begin
Declare @Dummy table(dummy_col Udd_dummy)
end
</inputbuf>
</process>
</process-list>
<resource-list>
<metadatalock subresource="USER_TYPE" classid="user_type_id = 257" dbid="9" id="lockc1d8700" mode="Sch-M">
<owner-list>
<owner id="process17b390" mode="Sch-M"/>
</owner-list>
<waiter-list>
<waiter id="process17b390" mode="Sch-S" requestType="wait"/>
</waiter-list>
</metadatalock>
</resource-list>
</deadlock>
<deadlock victim="process17b1c8">
<process-list>
<process id="process17b1c8" taskpriority="0" logused="0" waitresource="METADATA: database_id = 9 USER_TYPE(user_type_id = 257)" waittime="4608" ownerId="6609845" transactionname="@Dummy" lasttranstarted="2009-12-24T14:13:38.333" XDES="0x56bf9a8" lockMode="Sch-S" schedulerid="1" kpid="4460" status="suspended" spid="61" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2009-12-24T14:13:38.333" lastbatchcompleted="2009-12-24T14:13:38.330" clientapp="Microsoft SQL Server Management Studio - Query" hostname="WIN-PW5HZFYFFM5" hostpid="7808" loginname="WIN-PW5HZFYFFM5\Administrator" isolationlevel="read committed (2)" xactid="6609842" currentdb="2" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame procname="adhoc" line="4" sqlhandle="0x01000900f8a9323040208d08000000000000000000000000">
create proc dummy_sp
as
begin
Declare @Dummy table(dummy_col Udd_dummy)
end </frame>
</executionStack>
<inputbuf>
create proc dummy_sp
as
begin
Declare @Dummy table(dummy_col Udd_dummy)
end
</inputbuf>
</process>
</process-list>
<resource-list>
<metadatalock subresource="USER_TYPE" classid="user_type_id = 257" dbid="9" id="lockc1d89c0" mode="Sch-M">
<owner-list>
<owner id="process17b1c8" mode="Sch-M"/>
</owner-list>
<waiter-list>
<waiter id="process17b1c8" mode="Sch-S" requestType="wait"/>
</waiter-list>
</metadatalock>
</resource-list>
</deadlock>
</deadlock-list>
Post #838905
Posted Thursday, December 24, 2009 4:01 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:55 AM
Points: 911, Visits: 682
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
Post #838933
Posted Thursday, December 24, 2009 4:44 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, March 30, 2013 6:06 AM
Points: 532, Visits: 224
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?




Post #838941
Posted Thursday, December 24, 2009 6:57 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:19 AM
Points: 1,101, Visits: 5,271
Use CREATE TYPE instead of SP_ADDTYPE

What is the purpose using a transaction here?
Post #838959
Posted Sunday, December 27, 2009 9:52 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, March 30, 2013 6:06 AM
Points: 532, Visits: 224
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




Post #839311
Posted Monday, December 28, 2009 12:21 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, March 30, 2013 6:06 AM
Points: 532, Visits: 224
Atlast we found that this is known bug from sql server 2005 sp1

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=365876
Post #839328
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse