SQL Server concurrency

Question

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: Handling SQL Server concurrency issues

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?

The steps I need to carry out are:

1) Look up x number of records in table A

2) Perform some business logic that prepares a single row which is inserted into table B

3) Update the records selected in step 1) to point to the newly created record in table B

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.

Do you want to prevent a subsequent connection from achieving (1) and (2a (business logic)), or just ignore the redundant computation at (2b (insert row))? — Damien_The_Unbeliever, Sep 26 '11 at 13:07
I need to avoid the creation of a duplicate in table B, or at least detect the duplicate and erase it by rolling back the transaction. B does not hold a key for A, as the relationship from A to B can be many to one. — Trent, Sep 26 '11 at 21:54

score 3 · Answer 1 · answered Sep 26 '11 at 12:48

3

In SQL Server 2008, this can be handled with a filtered unique index:

CREATE UNIQUE INDEX ix_MyIndexName ON MyTable (FKField) WHERE FkField IS NOT NULL

This will require all non-null values be unique, and the database will enforce it for you.

answered Sep 26 '11 at 12:48

JNK

63,321
15
122
138

Haven't read the question but filtered indexes not available in 2005. would need an indexed view. – Martin Smith Sep 26 '11 at 12:50
@MartinSmith - can you enforce uniqueness in underlying tables in a view index? I've never tried it. post it as an answer! – JNK Sep 26 '11 at 12:52
@JNK - Yes. First index you create on an indexed view needs to be unique clustered index. – Martin Smith Sep 26 '11 at 12:53
@MartinSmith - I knew that, but I guess I didn't make the connection. Post it! – JNK Sep 26 '11 at 12:54
Unfortunately table B is used for other things apart from the process with table A. Table B actually handles purchase orders and this process will pick records from table A to generate a single purchase order for. So the relationship from A to B is many to 1, or no relationship. I'm not sure how I could prevent a duplicate insertion into B in this case – Trent Sep 26 '11 at 21:51

score 2 · Answer 2 · answered Sep 26 '11 at 12:57

The 2005 way of simulating a unique filtered index for constraint purposes is

CREATE VIEW dbo.EnforceUnique
WITH SCHEMABINDING
AS
SELECT FkField
FROM dbo.TableB
WHERE FkField IS NOT NULL

GO

CREATE UNIQUE CLUSTERED INDEX ix ON dbo.EnforceUnique(FkField)

Connections that update the base table will need to have the correct SET options but unless you are using non default options this will be the case anyway in SQL Server 2005 (ARITH_ABORT used to be the problem one in 2000)

score 2 · Answer 3 · answered Sep 26 '11 at 13:04

2

Using a computed column

ALTER TABLE MyTable ADD
   OneNonNullOnly AS ISNULL(FkField, -PkField)

CREATE UNIQUE INDEX ix_OneNullOnly ON MyTable (OneNonNullOnly);

Assumes:

FkField is numeric
no clash of FkField and -PkField values

answered Sep 26 '11 at 13:04

gbn

422,506
82
585
676

score 0 · Answer 4 · edited Sep 27 '11 at 10:42

Decided to go with the following:

1) Begin transaction

2) UPDATE tableA SET foreignKey = -1 OUTPUT inserted.id INTO #tempTable FROM (business logic) WHERE foreignKey is null

3) If @@rowcount > 0 Then

3a) Create record in table 2.

3b) Capture ID of newly created record using scope_identity()

3c) UPDATE tableA set foreignKey = IdOfNewRecord FROM tableA INNER JOIN @tempTable ON tableA.id = tempTable.id

Since I write junk into the foreign key field in step 2), those rows are locked and no concurrent transactions will touch them. The first transaction is free to create the record. After the transaction is committed, the blocked transaction will execute the update query, but won't capture any of the original rows due to the WHERE clause only considering NULL foreignKey fields. If no rows are returned (@@rowcount = 0), the current transaction exits without creating the record in table B, and returns some sort of error message to the client. (e.g. Error: Record already exists)

SQL Server concurrency

4 Answers4