Avatar of rizzalynn
Flag for Philippines

asked on 

Locking in SQL server

I have 2 batch programs, 1 is a program that sends email and another one sends fax. They both access a table QUEUE.

In the email sender program, this is what happens in relation to QUEUE. For each record in QUEUE that satisfies criteria:
1) Locks record 1 in QUEUE table
  select 1 from QUEUE with (UPDLOCK) where id = 1
2) Process sending out of email
3) Delete record 1 in QUEUE table
  delete from QUEUE where id = 1
4) commit transaction (transaction is not auto-commit)

In the fax sender program, similar steps also happen, except that in step 2, we send out fax (of course).

The problem is that sometimes the delete from QUEUE throws out an exception that it is locked. Thereby, re-sending of the emails/fax happens. I am sure that the group of records processed by these programs don't intersect.

Based on my testing, it seems that e.g. when email sender program performs step 1 and then fax sender program performs step 3, fax sender program raises a lock request time out (I perform this testing via simulation in dummy tables of the locking and db operations that happened in the programs).

I would like to know if there is something wrong with the way I lock the records that makes another transaction unable to delete records in the same table. Because the locking statement in the other transaction doesn't throw an error, yet the delete statement in that transaction throws an error.

By the way, here are some information about the database (I'm not sure if they will help):
Read Committed Snapshot is turned on
Snapshot Isolation State is On
Microsoft SQL Server 2005

Avatar of undefined
Last Comment

8/22/2022 - Mon