Lock record when editing ?!

I have a database, which is used by 4-5 users simultaneously. I want to prevent editing the same record from 2 users. For example:

User 1 opened Customer X for edition. At the same time User 2 is trying to edit the same Customer X, but he shouldn't be able to do it...
LVL 12
Who is Participating?
JaccoConnect With a Mentor Commented:
Hi there,

We use an integer field in the customer table. If this field is zero you may edit the record. When you start editing the record the user sessionnumber (something we use internally) is put in the record using an update statement. When editing is finished the number is reset to 0.

Regards Jacco
what database?

usual on bde desktop databases ( paradox,dbase)
this is done automatically. on sql-databases the concurrent
check is done on post by the database and results usual
in an errormessage for one client, that the record was changed
by another user.

some sql-databases are providing an explicit lock call, which is
mostly recommended to not to use this by the database developer

what you can do is a like audit table or a stateflag field on the table,
wher you can lookup in the before-edit event. a fallback would be
then abnormal ends of the editing client, so that the flag or auditrecord
tells further, that the record is about editing

meikl ;-)
What type of database are you using??
Most database engines provide some sort of record locking.

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Ivanov_GAuthor Commented:
I use MSSQL Server, but soon I will migrate to Oracle maybe. I want this to be universal - for all DBs.

Probably the idea with one additional column is what I need. It will be easier to implement on existing DB - just add one column for each table. OnBeforeEdit - set the value to 1 and commit in DB. Then open again for Edit, OnAfterEdit - set back to 0...

Am I missing something ?
We use a scheme where we store the session ID.

The session ID is a autoinc integer from a table where a login record for this session is stored (session is from when an application is started until it ends). This record also has as startdate, enddate, username, machinename fields. When the application closes noramlly the record is "closed" by setting the enddate from NULL to now. Now if the application crashed on the other hand the enddate is not properly "closed", and some customers might still be locked. Now we solved this by "closing" login records of the same user,machine when it next logs on. We check if a customer is logged by checking if the lockfield contains 0 or that the usersession of this nonzero lockfield is "closed".

Regards Jacco
kretzschmarConnect With a Mentor Commented:
>OnAfterEdit - set back to 0...

should be onAfterPost, i guess (no delphi available),
onAfterEdit is fired short after the record is set into editMode

there will be two fallbacks
- for a (very) short time two clients may detect the record as editable
(this could be solved, if u use an update-statement, where the readed value is in the whereclause)
- if the editor-client has an abnormal end the record keeps (pseudo) locked
(well, by max 5 clients this could be communicated and with an reset option could this be solved)

>Am I missing something ?
the edit may canceled, in this case the flag/additional field should be set back too

meikl ;-)


IMHO you are going to make your life harder. The way I use is (using queries only):

1. read the data (select query/queries)
2. edit some fields (non-databound controls for instance)
3. save the changes - i.e. when the user clicks OK
3.1. start a transaction
3.2. post the changes only (update query/queries)
3.3. commit the transaction

This way if two users edit one field, the second post will override the previous one. That is correct I think. Well, perhaps not in all cases but in most of them.

Regards, Geo
In customer systems where operators are calling customers this may lead to multiple operators calling the same client. Thats why we use the locking field.
Wim ten BrinkSelf-employed developerCommented:
In general, using locks in multi-user environments is often discouraged. A user might start editing, decide to drink some coffee or be balled while editing and as long as this user is busy editing the record, no one else can access it! It is considered to be a very bad practice...
The better solution is to wait for the user to make all changes and then check if someone else has modified the record before inserting them in the database. Thus if Marie is changing the address of X and decided to drink some coffee, and Charlene also decides to modify customer X for whatever reason and Charlene makes her change before Marie sends her change, Marie will get a warning that someone else has modified the record since she started editing the record.
The database offers some standard techniques for this but the easiest way is to add two additional fields to the records. A timestamp for when the record has changed for the last time and a username field telling who changed the record for the last time.

But in heaven's sake, DON'T LOCK RECORDS!!! Record locks are the mothers of all nightmares...
There are however sometimes when locks are absolutely needed, in my case I use them a lot for dataentry purposes

2 keyers can't key the same data!...
- so I lock the records (change a field to locked=1),
- assign the id of who has the record locked, (lockedbyuser=@User)
- save the time when it was locked (lockedtime=getdate())

all these locking is done inside a stored procedure when operators get work, works very well for me

then there is a monitor where production people can release records if they have been locked for more than whatever minutes

I agree that individual records are not locked in a Multi-User situation. With SQLServer for instance, we usually grab the record info onto a form without databound controls. After editing, the record changes are posted. If it has been changed by another user you should get a concurrency error.

Data bound controls are rarely used.

Likewise with Master Detail records we use batches - so the user who came back from coffee and can't remember what they were doing can get out of the records by a simple Cancel operation the ability for a user to Cancel all changes is very important.

This is combined with scenario's where the last user to edit 'wins' - i.e. their changes are the winners as they are posted last.

If the data is of any size it is rare for two users to be editing the recs at the same time.

The scenario you choose depends on how the data is used.

This is all very true but somtimes need a persitent lock as well, be it in the form of a field value or in the for of an extra record.

We have for example reminder records that get records from various sources. In it there is a custid and a reasoncode. All users can open the list of reminder records to start working. They all see the same list, and teams works on the same reason codes. Using a scheme like it is suddenly no longer very rare that to users start working on the same customer, and since some reasoncodes invlove calling the customer on the phone, without a fieldlocking system (or any other persistent locking system) customers will get called multiple times, and I assure you it will not contribute to a good customer relation if you do that a few times.

Another reason why two users will try working on the same customer is in a callcenter situation. A customer has called and a user is still busy with the customer record (in fact the customer record consists of many tables with many records but only the customer record needs a persitent lock). Then the user calls again because he forgot something. Because of the persitent locking mechanism the second user who tries to enter the customer record will immediately see that the customer is locked and who has the lock. The second users can than successfully transfer the call the that user.

I hope to have shown that I agree that record locking generally is a strategy you should not follow. Persistant locking is even worse. But that in certain situations you absolutely need it.

Regards Jacco

Record locking is a strategy that depends on the circumstances.

The idea of a record locking field seems unsustainable to my mind. This creates scenarios where if (for example) an error occurrs on a client and the record operation is not completed, the record becomes locked until the DBA clears the lock.  This scenario would never (in my experience be acceptable).

Also anyone attempting to access the record would naturally assume that the record is in use by another user which would not be the case.

I have also seen where this strategy was used (300 users), teams of people walking around trying to find who has gone home and left a record in 'edit' mode - an impossible task if you dont know the password for each PC.


Ivanov_GAuthor Commented:
well, there can be a batch running which clears all locks after specified interval. For example you can not keep record for editing more than 10 minutes... It the batch remove the lock, before posting the record, the user will receive error message that the record has been unlocked, or another user already locked it (after the interval)

In case of program termination, if there is a lock on a record, when the same user log again and select the same record - he should be able to edit it. So that I have to compare the user ID from my tables SYS_USERS. If it is different user - then the batch will remove the lock after X minutes...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.