Results 1 to 13 of 13

Thread: Table locking

  1. #1
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316

    Unanswered: Table locking

    I need 2 unique sequential numbers to be stored in the same table. Obviously, for the first number I can use an auto-number field. For the second number, I would like to use a 2nd table with just 1 field that would only ever store 1 record. The idea being that if a user needs to generate a new unique number, they lock the 2nd table, lookup the current value, store the value in a variable for use, add 1 to the value of the number in the table, then unlock the table.

    Presumably, I'd use something like this:

    Code:
    SELECT OrderNo
    FROM tblUniqueOrderNumber (WITH TABLOCK)
    Does this lock the table so no other users can read/write the table? If so, how do I unlock the table once I've updated OrderNo? What happens if another user then reads the table while it has a TABLOCK applied? I'm using SQL Server 2005 and the database is a multi-user database. I have a feeling I'm not going about this the best way possible, but I'd like to know how the table lock works eitherway.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You'll need to encapsulate it into a transaction for it to be guaranteed to be locked. I had a similar requirement in several apps to have a module-level locking introduced. After thoroughly analyzing the impact of the approach you are inquiring about, I ended up using sp_getapplock/sp_releaseapplock mehcanism. The difference is that with just wrapping TABBLOCK into a transaction you force the other connection to wait before it either times out or gets a "green light" to proceed. With applock all other connections regain control over their execution immediately after encountering the lock flag, and can branch out according to the logic you specify.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2007
    Posts
    62
    Out of interest, why would you need 2 unique numbers?

  4. #4
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Good question - in the end, it turned out that I didn't. However, the details in rdjabarovs post are useful as there are a couple of instances where I need to lock tables to ensure that only 1 person at a time can peform inserts on them. For example, a table containing record IDs and usernames of records that are currently locked and who has locked them.

  5. #5
    Join Date
    Feb 2007
    Posts
    62
    Hmmm. Careful - you are going to end up with a very non-scalable system. I'm not conviced you ever need to do this but it's your system...

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    sp_getapplock/sp_releaseapplock makes an app unscalable? Why?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Feb 2007
    Posts
    62
    Well anything that serialises access to a resource will hinder scalability around processes that use the resource. If that resource is an often-used table then you are are only going to be able to go as fast as a single transaction can complete. When you don't have resource contention then you can run many transactions simultaneously greatly improving your throughput. To be fair, there will be situations when the number of users is so small you won't notice.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The beauty of applock approach is that transaction duration is equal to the duration of execution of applock_test function, which is rounded to 1ms.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Besides, there is no serialization taking place (unless you mean something else other than the concept of serialization of data). Before the "meet" of the transaction is being processed, the applock flag is being interrogated with the above function. If the lock is present, - the transaction continues, else - immediate rollback and return. When rollback occurrs, - it's only to satisfy syntactical requirements (every begin_tran needs to be completed with either commit_tran or rollback_tran [xxx_tran is my terminology]). There is actually NOTHING to rollback, so no log-related IO takes place.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Feb 2007
    Posts
    62
    But my point is that you can only do one thing at a time so no matter how fast the operation is you are still lining up one by one to do the work (which is what I mean by serialisation). This is always going to be a bottleneck and should be avoided is what I am saying. Scalability goes hand in hand with being able to execute things in parallel and contending for/locking resources is the complete opposite.
    Hope that makes sense.

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by bcass
    ...there are a couple of instances where I need to lock tables to ensure that only 1 person at a time can peform inserts on them...
    The recommendation I made was based on these requirements.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Feb 2007
    Posts
    62
    And my recommendation was to consider whether this was really the best solution to solving whatever the business requirements are because locking tables is rarely necessary or a good way of going about anything.
    Anyway, I think we're done here don't you?

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Locking is a mechanism that every "real" RDBMS has to ensure data consistency. You cannot avoid it, you can only architect your app and db in such a way that locking becomes your "friend" rather than your foe. Application module level locking can be either ignored (which is what you suggest), or implemented. The discussion here is what is the best way to implement it, not how to ignore it. Now we're done
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •