Enforcing unique constraint with trigger causes deadlock - sql server sp1
GC
I have a user table which must have a unique constarint on 3 fields:
first_name, last_name, login. Each one of these 3 fields is a nvarchar
(255) and we support unicode, so I can't put a unique constraint on
three of them because I would get a "too long index" message (maximum
index length in sql server is 900 cahracters). The table has a
clustered index on user_id. user_id is an identity column. Also, it
has a non unique index on first_name, on last_name and on login.
To enforce the unique constraint, I added a simple "AFTER insert /
update" trigger to the table which says:
select count(*) from user_table group by first_name, last_name, login
having count > 1
If count > 1 raise an error.
Now, in my .net application I have a serializable transaction which
adds a new user, and then updates the new record with 2 new values.
The problem is when many users are added at the same time (lets say
10), we have a deadlock. The deadlock is a simple one:
Transaction1 has a X lock on primary key n1 (which is one of the new
user_ids)
Transaction2 has a X lock on primary key n2 (which is one of the new
user_ids)
Transaction1 is scanning the clustered index in order to perform the
group by part of the after trigger, it requests a S lock on key n2
Transaction2 is scanning the clustered index in order to perform the
group by part of the after trigger, it requests a S lock on key n1
and we have a DEADLOCK!
I tried many different ways to solve the problem, and nothing was
helpful:
1) I changed the isolation level within the AFTER trigger to be
readCommitted
2) I changed the query within the trigger to a simple where (select
count(*) where first_name = inserted.first_name etc)
3) I tried the hints with tablock, updlock within the trigger and it
did not help
There were 3 things which helped:
1) use the hint readUncommitted in the trigger - I can't use this
hint, because I can't read dirty data
2) Make the trigger an "instead of" trigger. But this makes the code
very complicated!
3) Adding an index on first_name+last_name+login - this changed the
execution plan so to perform the group by part it does not do a
clustered index scan - it goes over the first_name+last_name+login
index. But this is the problem I was trying to solve!
Maybe you have an idea how to get rid of this annoying deadlock.
Please note I can't make the fields shorter because of backward
comaptibility!
Thanks,
GC
Erland Sommarskog
> I have a user table which must have a unique constarint on 3 fields:
> first_name, last_name, login. Each one of these 3 fields is a nvarchar
> (255) and we support unicode, so I can't put a unique constraint on
> three of them because I would get a "too long index" message (maximum
> index length in sql server is 900 cahracters).
That applies to the actual key length, so you can still have a unique index
on these columns as there are no values which in total exceed 900 bytes in
length.
> select count(*) from user_table group by first_name, last_name, login
> having count > 1
> If count > 1 raise an error.
>
> Now, in my .net application I have a serializable transaction which
> adds a new user, and then updates the new record with 2 new values.
Serializable overall is prone to cause deadlocks.
The trigger condition could be written in a better way, but it seems that
you've tried that.
One alternative is to add a computed column with the value
hash_bytes(First_name + ';' + last_name + ';' + login) and the add a unique
index on this column.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Gene Wirchenko
<esq...@sommarskog.se> wrote:
>GC (gil...@gmail.com) writes:
>> I have a user table which must have a unique constarint on 3 fields:
>> first_name, last_name, login. Each one of these 3 fields is a nvarchar
>> (255) and we support unicode, so I can't put a unique constraint on
>> three of them because I would get a "too long index" message (maximum
>> index length in sql server is 900 cahracters).
>
>That applies to the actual key length, so you can still have a unique index
>on these columns as there are no values which in total exceed 900 bytes in
>length.
I do not understand this. Does he not have too long of a key
length?
255*2 + 255*2 + 255*2 = 1530
[snip]
Sincerely,
Gene Wirchenko
Erland Sommarskog
the row. If first_name = "Gene", Last_name = "Wirchenko" and Login = "genew"
the key length is 2*4 + 2*9 + 2*5 = 36.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
Gene Wirchenko
<esq...@sommarskog.se> wrote:
>Gene Wirchenko (ge...@ocis.net) writes:
>> On Tue, 31 May 2011 10:24:35 +0000 (UTC), Erland Sommarskog
>><esq...@sommarskog.se> wrote:
>>>That applies to the actual key length, so you can still have a unique
>>>index on these columns as there are no values which in total exceed 900
>>>bytes in length.
>>
>> I do not understand this. Does he not have too long of a key
>> length?
>> 255*2 + 255*2 + 255*2 = 1530
>
>That's the maximum key length. The actual key length depends on the data on
>the row. If first_name = "Gene", Last_name = "Wirchenko" and Login = "genew"
>the key length is 2*4 + 2*9 + 2*5 = 36.
Thank you.
Sincerely,
Gene Wirchenko