0

I would create a custom id generator (without using identity nor stored proc) :

I've the following actions with EF :

  1. Read : get the row and read the counter field
  2. Checks: perform some checks, generate & increment counter
  3. Write : update the row for future use

Naturally we should not have a duplcate id !

I started by putting the operations (Read and Write) in a transaction _context.Database.BeginTransaction

  1. but not sure what is the appropriate isolation level ?
    • I read that Repeatable Read ensure that read-data can't be updated by conccurent transaction, but what about the scenario when : Tran1 Read 3, Tran2 Read 3, Tran1 Update to 4, Tran2 Update to 4 !!! Could the Tran1 update be done if so how Tran2 could read new value after Tran1 has completed ?
    • I found also that : SERIALIZABLE do the opposite, and locks everything. While READ COMMITTED releases the locks when it read the row, or when the statement is completed depending on the lock, SERIALIZABLE releases the locks once the transaction is committed. This means that another transaction that wants to update data that the transaction have read at least once, or another transaction wants to read data that the transaction has updated will be blocked until the transaction is committed.
  2. or should i use a locking mechanism to prevent read-data to be used by a concurrent tran for generating a wrong id ?
abdelgrib
  • 843
  • 7
  • 11

1 Answers1

0

I think that SERIALIZABLE is a secure isolation level for this scenario, those links are very useful :

  1. Populate a sql server column with a sequential number not using an identity

  2. Is snapshot isolation good

  3. Understanding isolation levels sql server

It's possible to add IsolationLevel to DbContext.Database.BeginTransaction as showed here :

  1. BeginTransaction with IsolationLevel in EF Core

Isolation levels :

Read commited :

  • Can't read ("while" cuncurrent tran updating or) "if" there are changes not commited yet => blocked & wait
  • Can read "when" changes committed
  • Can't update "while" cuncurrent tran is reading => blocked & wait
  • Can update "when" cuncurrent tran done reading

Serializable :

  • Locks everything, release lock after entire transaction was done (commited, rollback)
  • Can't read "if" data was read by concurrent tran
  • Can't update "if" data was read by concurrent tran

Repeatable :

  • Can't update "if" data was read by concurrent tran

UPDATE

Using Serializable is dangerous, better use LOCK TABLE table IN ACCESS EXCLUSIVE MODE; as table level lock to lock the entire table if needed or FOR UPDATE within your select as a row level lock to lock selected rows for update from other select for update. See my answer here

abdelgrib
  • 843
  • 7
  • 11