hi,

i am developing a multi user database application using vb.net and

sql server 2000/2005.

for example, five user is working on sales module, for the first time

every one is getting bill no : 1

which is max no of the sales table. at click of "save" button, i have

written like any one has saved the

current bill, if yes then i am incrementing the bill no by +1 after

again getting max no of the sales table

and saving the bill/record. also i am checking meanwhile any user is

saving the bill then again i am

incrementing the bill no by +1 and saving the bill/record, means it

is in the loop. But still some time bill no

is being duplicated like i am getting two records/bills for the same

bill no.

i want to lock the table for fraction of time while saving the data.

---

Developer

One option is to use application locks in your stored procedure so that only one process can execute the specific part of the procedure at a time. If another thread tries to run those statements, it will wait till the process that locked it first, completed. see sp_getapplock:http://msdn.microsoft.com/en-us/library/ms189823(SQL.90).aspx

---

http://jacobsebastian.blogspot.com/

Well, I would not lock the entire table.
If you need subsequent numbers, I would do the following:

- set transaction isolation level to serializable (locks the entire range at the end of the table)
- make sure you have an index on ProductID (which should be there naturally as it's the Primary Key)

- BEGIN TRAN
- issue aSELECT MAX(ProductID) FROM Production.Product WITH(updlock)
- or issue a select top 1 (ProdcutID) from ... order by ProductID desc (which has the same effect as select max(...)

This should only lock the end of the table so that no other process can append records, but others can still read other records.

I hope this is helpful.

---

Bodo Michael Danitz - MCITP Database Administrator - free consultant - performance guru - www.sql-server.de