How to lock a line of a table

Question

Hello.

I have an application that runs on a terminal.

The terminal is registered on a database table.

During all session that application is running on a terminal, I need to ensure that the line of the table corresponding to the terminal is locked and nobody can update that.

What is the best way to do this?

I use C#.NET as programming language and SQL Server as DBMS.

Thanks :D

Answer 1

Databases do not work for this kind of locking. You can lock items, but its only for the duration of the operation (SELECT, INSERT, UPDATE, DELETE).

Perhaps you should try adding a bit column called "inUse" and set it to True(1) when the terminal is using it. Program the terminals to respect the inUse value if its already set, and make sure that its set to False (0) when the terminal is finished.

Why does the row need to be locked anyway, wouldn't it be better to have a row for each terminal?

Answer 2

The easiest way to do this is to assume that you have cooperative processes and have the terminal application (which I assume creates the row) generate and store a unique value associated with that terminal when the row is created. That way each process that inserts a row in the table has it's own unique value and can use that value as a check when updating rows in the table. The database can generate the unique value for you (Guid or UniqueIdentifier or even just an integer identity field) if you do the insert and read the inserted value back in a transaction.

Answer 3

in many sql dialects you could do this kind of locking:

begin transaction

select * from FOOTABLE f for update where ID = '12345'

[... do any other stuff here ... ] 

commit transaction

Answer 4

In general, I try to work with short-lived locks where I can do an entire unit of work in a single (serializable) transaction; this avoids the need for an additional locking mechanism, as the db locks will do everything needed.

When working with long-lived locks: It is a bad idea to use rdbms-locks for that purpose - it simply doesn't scale. Timestamp or row-version columns are a good way of doing optimistic concurrency to prevent against accidental over-writing.

To publish / enforce the fact that a row is being edited, I would store the user-id/user-name of the locking user in a column (null if not locked). This makes it clear who owns the row - i.e. when you want to edit the row, first UPDATE it, setting your user-id (and ensuring that it isn't already locked).

To cope with locks that aren't removed correctly (because of a dead terminal etc) there are 3 common options:

• when the lock owner logs in again, allow them to break their own locks
• allow an administrator to break the user's locks
• store a lock-timeout column (datetime), and have a scheduled task automatically unlock rows that are overdue

So to summarise - consider something like:

Foo
===
Id | ...data... | Timestamp | LockOwner | LockTimeout
---+------------+-----------+-----------+------------

etc