Delphi Firedac SQL Server: no error raised when update fails because record is locked

Question

I'm currently working on a project to migrate code base from using Advantage Database Server to SQL Server.

I'm using Firedac of XE8 linked to a Microsoft SQL Server 2014 Express.

I have a small test project. There's a TDBGrid showing the content of a table (the query lock mode is Pessimistic, lockpoint immediate).

I have another TQuery with a SQL command like this:

update myTable 
set firstName = 'John'
where id = 1

What I do :

1. I put the first row in Edit mode (by writing something in the cell)
2. When I press a button, it runs executeSQL on the Update query
3. Nothing happens -- the update query does not go through

That's fine ... but I was expecting an error message telling me the UPDATE didn't go trough...

How can I get the same behavior but with an error message triggered ?

Answer 1

Essential connection settings to work with row locks :

TFDConnection.UpdateOptions.Lockmode := lmPessimistic;

TFDConnection.UpdateOptions.LockPoint := lmImmediate;

TFDConnection.UpdateOptions.LockWait := False;

Behaviour described is SQL Server waiting for the lock to be removed to finish commiting the UPDATE. By setting your FireDACconnection to 'no wait' it's going to raise an exception as soon as you attempt to do something on the row you've locked by putting the dataset in Edit. Then you can catch this exception to do what you want.