I would like to start a thread to discuss the way you manage record locking using ADO.
My question is simple:
Lets say that you have a record, and that you need to edit it and meanwhile you edit it, other users should not be able to edit it.
How are you managing this situation ?
I appreciate your comments
Antonio,
I have created a table 'FILELOCK' in my SQL table, with a field 'table','table_id','user','pcname'.
When I want to lock a record, I write te table,record-ID, user and PC-name to that table. (I created a function SQLLock)
An other user that want to open the record check with a function SQLlock that the record is available.
If not, it returns FALSE, otherwhise is locks the record by writing the info to the table FILELOCK.
I use the info USER and PCNAME, so I can display the purson who have locked the record...
So my function SQLlock works the same as rlock()
If I want to unlock the record, I delete the record in FILELOCK.
Antonio
I use a numeric signature file in the table that gets incremented +1 each time I save a record... user one fetches a record that has a current value ( lets say ) 1 and before I UpDate() that record, I have a function that opens a new recordset just on the rows primary key ( or rowid ) and the Signature field to check its value.
If the Value is the same, I know no one has modified the record before I did. If the value has changed, then I know my edit has become 'stale' and someone has modified the same record before I did, in which case I cancel the Edit with a message like "Sorry .. someone has modified the record you are trying to change"....
Rick Lipkin
ps .. this assumes you buffer all your fields to memory values when you first read a record.
Marc, Rick,
And how do you control if a user gets disconnected without releasing the locked record (using any of your techniques) ?
In that situation the record would appear as locked but in fact nobody is locking it anymore...
Antonio
I do not look for a lock .. whom ever gets to update the record first gets the Signature field+1 Update(). Then if workstation two tries to update the same record .. Within the Update() routine, I create a new recordset on the unique rowid ( primary key ) and see if the signature field matches .. if it doesn't .. I stop workstation 2 from Update() forcing them to cancel their edit and try again.
In the case of a disconnect .. that record never got it signature field updated+1 so when workstation 2 tries to update .. the signature field matches and WS two gets the update.
If WS 1 comes back online .. more than likely they would have had to re-boot and would have to go back into the program afterward WS 2 made their changes.
Hope you understand my logic ?
Rick Lipkin
Antonio Linares wrote:Marc, Rick,
And how do you control if a user gets disconnected without releasing the locked record (using any of your techniques) ?
In that situation the record would appear as locked but in fact nobody is locking it anymore...
SELECT SUBSTRING_INDEX(host, ':', 1) AS host_short FROM Â information_schema.processlist WHERE db = "MyProgram" GROUP Â BY host_short ORDER Â BY host_shortMarc,
But if u dont reconect then register to continue lock, or if u reconnect with another pc its the same...
Good thread
Carles wrote:Marc,
But if u dont reconect then register to continue lock, or if u reconnect with another pc its the same...
Good thread
Marc,
Now i'm undertsand the process. Exist this table in all rdbm ?
Antonio Linares wrote:I would like to start a thread to discuss the way you manage record locking using ADO.
My question is simple:
Lets say that you have a record, and that you need to edit it and meanwhile you edit it, other users should not be able to edit it.
How are you managing this situation ?
I appreciate your comments
Enrico,
Could you post an example ? thanks
