Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Table locking for Inserting record

Posted on 2010-11-29
5
Medium Priority
?
730 Views
Last Modified: 2012-05-10
So we have a process where entrants are able to sign-up for a specific offering but there is a max number of spots.  Once the last spot is filled no others can enter that offering and we then create a new offering for others to join.

We have been having issues where 2 people sign up at the exact same time and we end up with 51 spots filled in a 50 spot offering.  I'm assuming we aren't locking the table or something like that.

Currently both person's requests hit the "AddToOffering" stored proc which currently starts a transaction, does a bunch of stuff and commits.  The first thing after the transaction starts is that we check the number of entrants, if there is room, we add them, do a few other things and then commit the transaction and exit the stored proc.    

What we want to achieve is that when both persons concurrently call that stored proc, the first one would put some type of Write Lock on the transaction or at the very least, maybe a table lock on the part of it where we check numbers and add them.  Then the second request waits until the lock is unlocked.  After unlocking, the second request would see that it is full and exit.

We still want the table to be readable for other stored procs though.  We are using sql 2008.  What is the best solution to resolve this issue?  I'm sure its probably basic but we aren't sure if its a system config where we make them serializable, or if the whole transaction has some lock on it, or if each table has a specific write lock and what type of lock that is suppose to be?

Thanks.


0
Comment
Question by:kruegerste
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34230767
you will need to do the transaction with isolation level SERIALIZABLE, that should fix that issue .
0
 
LVL 4

Author Comment

by:kruegerste
ID: 34230834
Thanks for quick response.... two questions though...

Can you post an example or reference to the exact syntax you are referring to?  Is this done in the stored proc?

 Also, how does this command act?  Does only one request get executed at a time, while others basically wait for the current transaction to be committed or aborted?  If so, is this going to be a large performance issue if the transaction contents take a bit to run?  If so, should we commit the part of the transaction that needs the locking and then start of a new transaction for the rest so other requests can get access quicker?

Thanks.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 34230899
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 34231577
SERIALIZABLE will work but it will cause a lot of waits, even early in the process when you have only 1 or 2 entrants.

You might want to consider serializing only the count of entrants update.  Update the count first in a separate, serialized transaction, and the rest of the modifications with the default isolation level.

For an example, see code below.

You would  need to make sure that if an error occurs during the rest of the tran that you go back and reduce the count.

Just a suggestion as a possibility to maybe reduce locking/contention.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT <entrant_count>
FROM tablename
WHERE ...

IF < <max_number>
    UPDATE <entrant_count>

COMMIT TRAN

SET TRANSACTION ISOLATION LEVEL READ COMMITTED 

BEGIN TRAN

...rest of code...

Open in new window

0
 
LVL 4

Author Comment

by:kruegerste
ID: 34238627
Thanks ScottPletcher, I was wondering about performance.  Looks like multiple transactions are the way to go.  



0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question