Databases
StackOverflow2013
Postsdbo
POHandling SQL Server concurrency issues
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHandling SQL Server concurrency issues
    primarykey
    data
    text
    <p>Sometimes I need to acquire a unique ID and store it with a record, but I am unable to use an identity column. So instead I have a table which provides unique IDs using a label field and an integer. When a unique ID is needed, I call a stored procedure and pass in the label, and it spits out the next ID associated with it. Of course it's important for this to be reliable in an environment with concurrent transactions. That is, the stored procedure should never return the same value twice for a given label. My limited understanding of transaction isolation has led me to do the following:</p> <p>1) Set transaction isolation level to serializable</p> <p>2) SELECT id FROM UniqueIdTable WHERE label = @inputLabel</p> <p>3) UPDATE UniqueIdTable SET id = id + 1 WHERE label = @inputLabel</p> <p>4) Return the id retrieved in 2)</p> <p>But is this actually safe? Isn't it still possible for two threads to concurrently execute up to step 2), even with serializable isolation? It's my understanding that the highest isolation level only guarantees that a single transaction will execute without experiencing phantom rows or changing data from other threads. If this is the case, two simultaneous calls to the GetID function could return the same value.</p> <p>Am I misunderstanding something about the isolation levels? How can I guarantee this won't occur?</p> <hr> <p>I have another problem I need to sort out. Suppose I have a table with a field in it which holds foreign keys for a second table. Initially records in the first table do not have a corresponding record in the second, so I store NULL in that field. Now at some point a user runs an operation which will generate a record in the second table and have the first table link to it. This is always a one-to-one relationship, so if two users simultaneously try to generate the record, a single record is created and linked to, and the other user receives a message saying the record already exists. How do I ensure that duplicates are not created in a concurrent environment?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USTrent
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHandling SQL Server concurrency issues
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHandling SQL Server concurrency issues
      1. This table or related slice is empty.
      1. VTUpMod
    1. COwhy are you unable to use an identity column? thats's what it's for...?
      singulars
      1. POHandling SQL Server concurrency issues
      1. USDave Markle
    2. COYes, I can never quite understand the "I need feature xxx, but I am unable to use the built-in xxx feature..." type of questions....
      singulars
      1. POHandling SQL Server concurrency issues
      1. USE.J. Brennan
    3. COLook at this [SO question](http://stackoverflow.com/questions/1542886/getting-the-next-id-without-inserting-a-row)
      singulars
      1. POHandling SQL Server concurrency issues
      1. USBogdan Sahlean
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload