*/ BEGIN TRANSACTION GO CREATE TYPE EmailAddress FROM VARCHAR ( 320 ) GO CREATE PROCEDURE dbo. */ BEGIN TRANSACTION GO CREATE TYPE EmailAddress FROM VARCHAR ( 320 ) GO DECLARE TABLE (e EmailAddress ) GO ROLLBACK TRANSACTION /*Īlias type - create procedure with param & table var
#Deadlock sql code
I tested various potential deadlock scenarios involving alias types, table variables, and table-valued parameters against all of these builds the code is as follows:Īlias type - declare in local table variableĪlways deadlocks on 2005 SP4 -> 2014, except in 2005 RTM I had reviewed several Connect items and various comments that left me wondering which use cases were supported and where, and I had a strange compulsion to find out which aspects of this issue had actually been fixed. So I decided to go through some tests in the initial and most recent builds of all of the major versions: SQL Server 2005 RTM, 2005 SP4, 2008 RTM, 2008 SP3, 2008 R2 RTM, 2008 R2 SP2, 2012 RTM, 2012 SP1, and 2014 CTP2 ( and yes, I do have them all installed). This could be extremely cumbersome, or even impossible, to have often automated testing frameworks and harnesses completely change the way they operate to account for this limitation. The only way to resolve this is to create the table type before starting the transaction, and explicitly drop it afterward (or otherwise break it up into multiple transactions). So my unit tests would succeed as long as all I wanted to do was test that I could create the procedure – forget about declaring the type as a local variable or as a column in a local table variable. So I tend to believe the comments on the Connect item – that this portion of the bug was fixed in 2008 SP R2 SP1, and has never been an issue in more modern versions.īut this still leaves out the ability to actually put the alias type through any sort of true testing. However, this does not deadlock in SQL Server 2008 SP3 () or 2008 R2 SP2 (). foo EmailAddressĪS BEGIN SET NOCOUNT ON DECLARE TABLE (e EmailAddress ) INSERT SELECT END GO ROLLBACK TRANSACTION This made the use of these types much more prevalent, and introduced another case where a transaction that tried to create and use such a type would deadlock:īEGIN TRANSACTION GO CREATE TYPE EmailAddress FROM VARCHAR ( 320 ) GO CREATE PROCEDURE dbo. Therefore, somewhere between RTM and SP4, they changed the internal handling for transactions that involved table variables using alias types.įast forward a few years to SQL Server 2008, where table-valued parameters were added ( see a good use case here). In SP4, however, they would all deadlock. In RTM, all of the above code samples worked just fine with no deadlocks. This didn't really solve the problem with changing the types, it just made the syntax a little cleaner. SQL Server 2005 came around, and introduced new DDL syntax for creating alias types: CREATE TYPE. While they may make your metadata more self-documenting and data-definition-like, they are a royal pain if you ever want to change them, which may be a topic for another post. Of course, this scenario wasn't very widespread at the time because, after all, not many people used alias types in the first place. foo = N'whatever' GO DECLARE EmailAddress SET = N'whatever' EXEC dbo. foo EmailAddressĪS BEGIN SET NOCOUNT ON SELECT END GO EXEC dbo.
BEGIN TRANSACTION GO EXEC sp_addtype = N'EmailAddress', = N'VARCHAR(320)' GO CREATE PROCEDURE dbo.
0 kommentar(er)
