StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Server concurrency
text
Body
copied!<p>I asked two questions at once in my last thread, and the first has been answered. I decided to mark the original thread as answered and repost the second question here. Link to original thread if anyone wants it: <a href="https://stackoverflow.com/questions/7546169/handling-sql-server-concurrency-issues/7546452#7546452">Handling SQL Server concurrency issues</a></p> <p>Suppose I have a table with a field 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. If two users simultaneously try to generate the record, a single record should be 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> <p>The steps I need to carry out are:</p> <p>1) Look up x number of records in table A</p> <p>2) Perform some business logic that prepares a single row which is inserted into table B</p> <p>3) Update the records selected in step 1) to point to the newly created record in table B</p> <p>I can use scope_identity() to retrieve the primary key of the newly created record in table B, so I don't need to worry about the new record being lost due to simultaneous transactions. However I need to eliminate the possibility of concurrently executing processes resulting in a duplicate record in table B being created.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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