Solved

Lock record when editing ?!

Posted on 2004-09-08
14
399 Views
Last Modified: 2011-09-20
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...
0
Comment
Question by:Ivanov_G
  • 4
  • 2
  • 2
  • +5
14 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 12013242
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 ;-)
0
 
LVL 8

Expert Comment

by:RickJ
ID: 12013243
Hello
What type of database are you using??
Most database engines provide some sort of record locking.

RickJ
0
 
LVL 10

Accepted Solution

by:
Jacco earned 100 total points
ID: 12013318
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
0
 
LVL 12

Author Comment

by:Ivanov_G
ID: 12013571
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 ?
0
 
LVL 10

Expert Comment

by:Jacco
ID: 12013701
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
0
 
LVL 27

Assisted Solution

by:kretzschmar
kretzschmar earned 25 total points
ID: 12013747
>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 ;-)

0
 
LVL 17

Expert Comment

by:geobul
ID: 12013964
Hi,

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
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 10

Expert Comment

by:Jacco
ID: 12014013
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.
0
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 12014712
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...
0
 
LVL 13

Expert Comment

by:BlackTigerX
ID: 12018729
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
0
 
LVL 5

Expert Comment

by:Voodooman
ID: 12021970
Hi

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.

Voodooman  
0
 
LVL 10

Expert Comment

by:Jacco
ID: 12022855
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
0
 
LVL 5

Expert Comment

by:Voodooman
ID: 12024578
Hi

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.

Voodooman

0
 
LVL 12

Author Comment

by:Ivanov_G
ID: 12024659
@Voodooman
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...
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now