SQL Server 2000 Exclusive Locking

Question

My setup - One server with two SQL Server 2000 instances, INSTANCE1 and INSTANCE2. Each instance has 1 DB, DBprod and DBstag.

I have a situation where I need to prepare invoices for several customers so I would like to place an exclusive lock on a table while I fetch an invoice number from INSTANCE1.DBprod.LastInvoiceNumber to INSTANCE2.DBstag, perform soem calculation, prepare an invoice and then insert the invoice (header and detail) to INSTANCE1.DBprod, then update INSTANCE1.DBprod.LastInvoiceNumber, repeat for the next customer and then release the lock after I am finished with all the customers.

begin trans inv

EXCLUSIVELY LOCK INSTANCE1.DBprod.LastInvoiceNumber

open customer cursor

fetch next from customer

get invoice number from INSTANCE1.DBprod.LastInvoiceNumber

prepare invoice

insert invoice to INSTANCE1.DBprod

update INSTANCE1.DBprod.LastInvoiceNumber (increment by 1)

fetch next from customer (prepare next customer invoice)

close customer cursor

commit trans inv

RELEASE LOCK ON INSTANCE1.DBprod.LastInvoiceNumber

Would this be my solution

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

There is an accounting application using INSTANCE1.DBprod.LastInvoiceNumber which is why I want to exclusively lock the table until I am finished posting all my invoices.

gbn gbn · Accepted Answer · 2011-04-15T18:40:02

One of the easiest ways is use sp_getapplock to allow only one session. Other sessions will wait/fail

This is independent of lock granularity and isolation and it often better in this scenario. Using SERIALIZABLE isn't exclusive : you'd need TABLOCKX. But then other readers on the table are blocked too

sp_getapplock will apply only to the scope of this code.

SQL Server 2000 Exclusive Locking

1 Answers