how to lock a table/records - problem in coding for local area network application - vb.net 2005/sql server RRS feed

  • Question



    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.

    Friday, January 30, 2009 7:27 AM

All replies

  • 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

    Another option is to ask SQL Server to lock the table by specifying TABLOCKX table hint. For example:

    SELECT MAX(ProductID) FROM Production.Product WITH(TABLOCKX)

    This query will put an exclusive lock on the Product Table (AdventureWorks) untill the transaction is rolledback or committed. Other processes that tries to run a MAX(ProductID) on this table will have to wait. 

    Note that this could block queries from other parts of the application running against this table. If your SAVE process is quite long, you might consider putting the LAST order number to another table (with one row) and locking that table. 

    Friday, January 30, 2009 8:03 AM
  • 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)

    - issue a SELECT 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
    Friday, January 30, 2009 9:54 AM