January 8, 2013 at 9:50 am
I got an error that i thought was impossible. I am not sure if i understand what is going on, so here i am!
With these two tables :
Table1
BinaryKey binary(15)
Table2
BinaryKey binary(15)
Flag bit
Both tables have a PK on BinaryKey and Table2 have a index on the bit column.
With the profiler i saw that only two stored procedure are called when the error happen
usp_Table1
(
Truncate Table1
insert into Table1
select BinaryKey from Table2
)
usp_Table2
(
update Table2
set Flag = 1
where BinaryKey = @BinaryKey
)
Sometimes i get the following error on usp_Table1
"Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key in onject 'dbo.Table1'
So..Table1 is truncated and both table have a PK on BinaryKey.
The profiler showed that the update was called while the truncate/insert was processing, so i guess that the select somehow returned a duplicate row because of the update, but this is where im not sure what is going on. I thought a select generated a shared lock for the exact purpose of blocking the update.
While i was debating with myself if the bit index was useless or not, i removed it for performance testing and i found out that the duplicate key error never showed up again.
Now, everything is working fine without the bit index, but i think i am missing some really key info on what was going on. It would be really help if someone can explain why this can happen.
Thank you!
January 9, 2013 at 1:18 am
hi2u (1/8/2013)
Sometimes i get the following error on usp_Table1"Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key in onject 'dbo.Table1'
i am not agree you will get this error instead use should get "incorrect syntax near table1 as truncate missing the 'table' word moreover , do you have any DML trigger ?
hi2u (1/8/2013)
The profiler showed that the update was called while the truncate/insert was processing
Confusion here, whatever the sequence you use to exec the Sps, thet are not going to conflict or block eachother..unless you explicitly not COMMITing the transaction in case of second SP.
hi2u (1/8/2013)
I thought a select generated a shared lock for the exact purpose of blocking the update.
from BOL
The shared lock is placed on the "resource" only as long as it is needed. Shared (S) locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction
hi2u (1/8/2013)
the bit index was useless or not, i removed it for performance testing and i found out that the duplicate key error never showed up again.
index ae not related with error or vice versa. index are here to speed up the query result.
What i can sense here that you need to go through folowing topics.indexes, locks, how to read profiler trace,transations
can you post the sequence you used to exec the SPs plus the screenshot of profiler trace where u get PK violation error.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 9, 2013 at 7:27 am
Yeah sorry, i made a mistake while typing the truncate.
I think i found an explanation to this behavior :
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx
The update of the bit field made the select return the row twice. Before today, i was 100% sure that this was impossible!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply