Subject RE: [firebird-support] Auto version no, or DateTimeStamp per row?
Author Myles Wakeham
The reason why I'm asking this is to try and work out a development strategy
to handle multiple users attempting to update the same record at the same
time. I'm sure this is a commonplace strategy that every SQL developer has
to contend with.

Here's a hypothetical scenario... I'm trying to find the best way to design
to accommodate this....

User A is looking at a grid containing rows of data in a table. They double
click on a row, to open a window containing its detailed elements. They
elect to 'modify' this record. The record is now available exclusively on
their workstation for modification. Then while this record is on their
screen, and before they save their changes, they go to lunch.

User B decides to run some function that will update a column in the same
table that User A is looking at. The function executes and the data is

User A returns from lunch to finish their update, saves the record.

Now the data in the database is wrong. The information that was on the
screen of User A's computer was old, and didn't take into consideration the
updates that had occurred while they were away. But when they saved this
'old' information to the database, it completely trashed User B's update to
the database.

So clearly what I need is some form of record locking mechanism that will
ensure that User B can't run their function until User A has saved their
changes. Or that because of some form of a 'timeout' User A was not allowed
to save their changes because the record had changed while they were looking
at it.

What are the best strategies to use to accommodate this situation?


Myles Wakeham
Director of Engineering
Tech Solutions Inc.
Scottsdale, Arizona USA
Phone (480) 451-7440