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

duplicate row after a truncate Expand / Collapse
Author
Message
Posted Tuesday, January 8, 2013 9:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 21, Visits: 1,124
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!
Post #1404353
Posted Wednesday, January 9, 2013 1:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:42 AM
Points: 2,837, Visits: 3,956
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
Post #1404591
Posted Wednesday, January 9, 2013 7:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 21, Visits: 1,124
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!
Post #1404781
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse