Locking a row of a database

The_Chancer

Is there a way of "locking" a row of a database when someone access it ?

I have thought about adding a column that is either a 0 or 1 depending on whether someone has "opened" the row and resetting it when they have updated the information. But wondered if it was possible by another means.

I am using MS SQL 7....

[deleted]

I assume you mean locking a row outside of a transaction, for use in an html-form.

The best thing I've found so far is to add two new columns, one to contain the ID of the user that locked the record, and one to contain the date that the record was locked.

When someone locks the record you enter his userid and the current date-time in the record.
The next time someone want's to access the record you can check his userid to see if it was he who locked the record, and you can use the date to check wether the lock has expired (if someone locks a record and then logs off to go home, the lock must be removed after X minutes/hours)

tomhath

AFAIK any database locks should be released when the connection ends (or as soon as the DBMS gets around to releasing them).

What you are thinking about will work, but make absolutely sure you will release the lock if the user just goes away (maybe a timestamp instead of a 0/1 flag)...

The_Chancer

Hmmm, and then it checks the timestamp for a difference of more than say 5mins, if so, you can't update the record... I see your meaning and I like it....

Cheers.

[deleted]

"AFAIK any database locks should be released when the connection ends (or as soon as the DBMS gets around to releasing them). "

Database locks yes, like when you do a 'SELECT FOR UPDATE" etc.
But in a web-environment you select records, print them in an HTML form, and then you disconnect from the database server, which would release the lock....

"What you are thinking about will work, but make absolutely sure you will release the lock if the user just goes away (maybe a timestamp instead of a 0/1 flag)..."

This is why I suggested using both a userid and a timestamp, so you can identify who locked the record, and when it happened.

Also, releasing the lock 'when the user goes away' can be replaced by "when another user wants to access a record who'se lock has expired"