March 29, 2011 at 9:11 am
I'm trying to delete some rows in a production database, in a responsible manner (minimizing locking).
WHILE EXISTS (select 1 from [dbo].TICKETGRANTINGTICKET with (NOLOCK)
WHERE [LAST_TIME_USED] < 1301198400000)
BEGIN
delete top(1000) from [dbo].TICKETGRANTINGTICKET
where [LAST_TIME_USED] < 1301198400000
CHECKPOINT
END
This is actually going to delete 95% of the 1,300,000 rows in the table.
However, it took about a dozen attempts, as the query "was choosen as the deadlock victim" multiple times.
To further my understanding of locking...Am I doing something that is aggravating the situation?
What are the ramifications of using CHECKPOINT, above, as opposed to a BEGIN TRAN/COMMIT?
Thank you!
-Joseph
March 29, 2011 at 9:48 am
You could try putting a ROWLOCK hint on your delete statement that might help.
March 29, 2011 at 9:53 am
I'd do while @@rowcount <> 0 and flush the exists.
I'd do a checkpoint every X loops.
I'd insert a waitfor for a coouple seconds so that transactions can run between loops.
However, since you keep only 5 of the data, I'd export the valid data to a new table, truncate the table and then reimport.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply