CODEWITHSUNDEEP
concurrencyisolation-level
Jonas Sourlier
Jonas Sourlier

Reputation: 14435

Is this a case of Non-Repeatable Reads?

I have the following tables (here modeled in Entity Framework, but my question has nothing to do with EF):

enter image description here

As you can see, this is a versioned Product table. The Id column is the primary key, but the combination (EntityId, VersionId) could be the primary key as well. EntityId denotes the Id of the entity which is constant between different versions of the entity. The entity gets deleted by writing a row with IsDeleted = 1.

The stored procedures that are responsible for data manipulation first check if the data operation is okay. For example, the UPDATE SP checks if the entity has already been deleted. If those checks succeed, the SPs generate a new row in the Version table, followed by a new row in the Product table:

(pseudo-code):

sp_Product_Update:
(1) IF EXISTS (SELECT Id FROM Product WHERE IsDeleted = 1 AND EntityId = @ProductId)
        RAISERROR "Entity has already been deleted"
        RETURN
(2) INSERT INTO Versions ...
(3) INSERT INTO Product ... (IsDeleted = 0)

sp_Product_Delete:
(1) IF EXISTS (SELECT Id FROM Product WHERE IsDeleted = 1 AND EntityId = @ProductId)
        RAISERROR "Entity has already been deleted"
        RETURN
(2) INSERT INTO Versions ...
(3) INSERT INTO Product ... (IsDeleted = 1)

This works all well.

Currently, I'm analyzing this for concurrency issues. Imagine the following concurrency scenario, where two SPs are invoked at the same time, for the same entity:

Transaction 1                          Transaction 2
sp_Product_Update                      sp_Product_Delete

(1) Check succeeds, entity has not yet been deleted.

                                       (1) Same check.

                                       (2) INSERT INTO Versions...
                                       (3) INSERT INTO Product.. (IsDeleted = 1)

(2) INSERT INTO Versions...
(3) INSERT INTO Product ... (IsDeleted = 0)

As you can see, this race condition leads to inconsistent data, namely an IsDeleted = 0 row which comes after an IsDeleted = 1 entry.

So we have to determine what level of isolation we need to avoid this race condition.

So I'm left with two questions:

Upvotes: 1

Views: 92

Answers (1)

Martin Podval
Martin Podval

Reputation: 1117

Your solution requires serializable isolation level as all commands need to be executed together as one atomic operation.

If you would not use stored procedures, I would encourage to use optimistic locking which is designed for this kind of situation with high throughput.

Upvotes: 1

Related Questions