SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


duplicate row after a truncate


duplicate row after a truncate

Author
Message
hi2u
hi2u
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 1245
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!
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13298 Visits: 4077
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;-)
hi2u
hi2u
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 1245
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
http://www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2009/04/10/selects-under-read-committed-and-repeatable-read-may-return-incorrect-results.aspx

The update of the bit field made the select return the row twice. Before today, i was 100% sure that this was impossible!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search