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?
Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.