Firebird Interview Question:

How to lock records in a table?

Submitted by: Administrator

While there shouldn't be many reasons to do this in MGA database system like Firebird, there are ways to do it.

One is to use a dummy update for all the records you wish to lock. Many developers do this by accident and get the deadlocks. Example that locks employee 8:

-- start transaction
update employee set emp_no = emp_no where emp_no = 8;
...
update employee set ... where emp_no = 8;
-- end transaction


A more elegant way is to use the SELECT ... WITH LOCK syntax.

-- start transaction
select * from employee where emp_no = 8 WITH LOCK;
...
update employee set ... where emp_no = 8;
-- end transaction


Please note that locking easily leads to deadlocks with NO WAIT and application hanging with WAIT transactions. Use it only if you're really sure you know what you are doing and why.
Submitted by: Administrator