LockTable/ReadIsolation(UpdLock) should be placed before a read operation, if a write operation is performed later

[christophstuber]

Recently stumbled over a piece of code like this:

if MyTable.Get(...) then begin
  MyTable.LockTable();
  ...
  MyTable.Modify();
end;

With native DB this was the way to go. LockTable was it's own operation and more importantly, it locked the entire table, which is where the (now misleading) name comes from and is why it should always be used as late as possible.

But with SQL DB things work differently. LockTable itself doesn't call a DB operation, but instead adds WITH(UPDLOCK) to the next read operation on that record. Therefore it must be placed before the first read.

Maybe this can be used as a starting point for a more general rule. Lock before you write

Typical code you find is like this:

MyTable.Get()/Find()/FindFirst()/FindLast();  // SELECT TOP(1) ... FROM MyTable WITH(READUNCOMMITED)
...
MyTable.Modify()/Delete();

Because the record is read with READUNCOMMITED, SQL Server cannot guarantee at the time of Modify(), that

• The record has not been changed between the last read and now
• The state which was read can actually be written to, because it could have been read while being modified by another transaction

This leads to an additional implicit read right before the modify, this time with UPDLOCK set. This is also where the Another user has modified the record comes from. Depending on how much is done between the read and the write a rather large rollback will be done.

The following rules should apply:

• When you are performing a Modifiy, Delete or Rename, the record should be read with LockTable or better ReadIsolation(IsolationLevel::UpdLock). For FindSet, the ForUpdate parameter can also be used.
• Locking behavior doesn't apply to temporary tables.
• Locking before Insert is context dependent. With ledger tables it's required to guarantee no gaps in Entry Nos., in all other cases it can even be bad for performance and concurrency.