Visual Basic&database, Workaround for the Lack of Record-Level Locking

Workaround for the Lack of Record-Level Locking

Author	Message
Mike Roberts #1 / 8	Workaround for the Lack of Record-Level Locking Quote: > I'm interested in hearing how other VB/Access programmers have dealt with > Access' lack of record-level locking. At work, we ended up using > username/date-last-changed fields in conjunction with unbound forms. When > the record data is read for use on the form, the username & > date-last-changed are stored in memory. When the record is going to be > written back to the table, the program checks to see if the > username/date-last-changed has been changed. If so, the user is informed > that the data has changed and may cancel the operation. If the data is > written to the table, the username & date-last-changed are updated. > This seems rather kludgey and I'd like to hear some alternatives. > ...lars AS far as I understood the JET database already did this for you. You choose optimistic locking then when you do the .Update if the data has changed since the .Edit you get an error (I forget which one). I#You can then abandon your update or do the .Update again and it will go through without error. -- Regards Lerwick, Shetland, UK Compuserve: 100016,1727 Phone: +44 1595 695762 Fax: +44 1595 694459 =================================================================
Sun, 06 Jul 1997 04:13:29 GMT

Jim Sea #2 / 8	Workaround for the Lack of Record-Level Locking Quote: >I'm interested in hearing how other VB/Access programmers have dealt with >Access' lack of record-level locking. At work, we ended up using >username/date-last-changed fields in conjunction with unbound forms. When >the record data is read for use on the form, the username & >date-last-changed are stored in memory. When the record is going to be >written back to the table, the program checks to see if the >username/date-last-changed has been changed. If so, the user is informed >that the data has changed and may cancel the operation. If the data is >written to the table, the username & date-last-changed are updated. >This seems rather kludgey and I'd like to hear some alternatives. >...lars Another approach you could take is to create a "logical lock" table. When you retrieve a record for update, you insert the record key, the username, date and time into the lock table. If the record was already in the table it was previously locked and you do not get access unless the lock is held by the same user who may have left the application incorrectly (Make up your own rules). This has the advantage of not keeping physical locks on the records if the user may hold them for a relatively long time while filling in many screens of data. The disadvantage in this case for Access and other DBMSs without stored procedures is that you can't force all users to access the table with your applcation logic. Jim Seach
Sun, 06 Jul 1997 12:48:35 GMT

Michael C Amunds #3 / 8	Workaround for the Lack of Record-Level Locking : > : >I'm interested in hearing how other VB/Access programmers have dealt with : >Access' lack of record-level locking. At work, we ended up using : >username/date-last-changed fields in conjunction with unbound forms. When : >This seems rather kludgey and I'd like to hear some alternatives. : > : >...lars : > : > : Another approach you could take is to create a "logical lock" table. : When you retrieve a record for update, you insert the record key, : the username, date and time into the lock table. : Jim Seach I have had the beset luck with the "logical lock" table. I have used this for years in several db systems. It can even be used as a type of referential integrity, too. If you want to work on a 'child' rec, add the 'parent' rec to the lock table. Now folks can't{filter}of the parent while you diddle with the child. A side benefit from this model is that, if a user 'hangs' during a record edit, all recs 'held' by that user are still locked to others. Thus, if integrity was mucked up, dba will hear about it before all heck breaks lose on your tables. MCA +-------------------------------------------------------------------------+ \| Voice....: 513/381-4610 FAX.....: 513/421-9657 \| \| SnailMail: Aviation Planning Assoc, Inc. 421 Arch St. Cinti, OH 45202 \| +-------------------------------------------------------------------------+
Sun, 06 Jul 1997 15:50:37 GMT

Ebbe Jonss #4 / 8	Workaround for the Lack of Record-Level Locking Quote: > Another approach you could take is to create a "logical lock" table. > When you retrieve a record for update, you insert the record key, > the username, date and time into the lock table. If the record > was already in the table it was previously locked and you do not > get access unless the lock is held by the same user who may have What happens if a workstation goes down in the middle of an edit session? The lock table will indicate a lock where there should be none, and you'll have a heck of a time trying to figure out which locks are real, and which ones aren't. You'd need to couple the locking management with some sort of user session control system, which would require a concurrent DBMS monitor process, something which Access does not support. -- DBM Database Marketing Oy, Helsinki, FINLAND " Q: What is the opposite Tel: +358 0 605 900, Fax: +358 0 605 991 of information? "
Sun, 06 Jul 1997 15:54:42 GMT

Mark MacIv #5 / 8	Workaround for the Lack of Record-Level Locking Quote: Robertson) writes: > > I'm interested in hearing how other VB/Access programmers have dealt with > > Access' lack of record-level locking. At work, we ended up using [SNIP] > AS far as I understood the JET database already did this for you. > You choose optimistic locking then when you do the .Update if the > data has changed since the .Edit you get an error (I forget which > one). I#You can then abandon your update or do the .Update again > and it will go through without error. I response to the original article I too impletemented an similar method for record locking, which I agree is a bit of a kludge but it works. The problem I found with using optimistic locking (as described by Mike) is that you don't know if the record is locked until the user has made all of thier changes and clicked on the 'Update' button. This means that a user may change quite a few fields before discovering that the record is locked, from the users point of view they have wasted time entering data which can't be written due to the record being locked. Mark MacIver Vision Technology Limted Intuitive Computer Solutions Tel: UK 0268 724154 Fax: UK 0268 590005
Sun, 06 Jul 1997 17:29:05 GMT

Mike Roberts #6 / 8	Workaround for the Lack of Record-Level Locking Quote: > Robertson) writes: > > > I'm interested in hearing how other VB/Access programmers have dealt with > > > Access' lack of record-level locking. At work, we ended up using > [SNIP] > > AS far as I understood the JET database already did this for you. > > You choose optimistic locking then when you do the .Update if the > > data has changed since the .Edit you get an error (I forget which > > one). I#You can then abandon your update or do the .Update again > > and it will go through without error. > I response to the original article I too impletemented an similar > method for record locking, which I agree is a bit of a kludge but it > works. > The problem I found with using optimistic locking (as described by Mike) is > that you don't know if the record is locked > until the user has made all of thier changes and clicked on the > 'Update' button. This means that a user may change quite a few fields > before discovering that the record is locked, from the users point of > view they have wasted time entering data which can't be written due to > the record being locked. Yes, this is definitely a case of horses for courses. If the database is heavily used and it is quite likely that a lock conflict would occur then this method may not be appropriate. Remember, though, that the alternative is that they cannot make any changes at all which may be more frustrating. In most of the system I have worked on (and some of them have been quite heavily used) lock conflicts are rare. They do not in any case have to lose what they have just entered. When the conflict occurs they can be given the choice as to whether they continue to write the data anyway or abandon what they've just entered. -- Regards Lerwick, Shetland, UK Compuserve: 100016,1727 Phone: +44 1595 695762 Fax: +44 1595 694459 =================================================================
Sun, 06 Jul 1997 19:04:06 GMT

Jim Sea #7 / 8	Workaround for the Lack of Record-Level Locking Quote: >What happens if a workstation goes down in the middle of an edit >session? The lock table will indicate a lock where there should >be none, and you'll have a heck of a time trying to figure out >which locks are real, and which ones aren't. >You'd need to couple the locking management with some sort of >user session control system, which would require a concurrent >DBMS monitor process, something which Access does not support. >-- >DBM Database Marketing Oy, Helsinki, FINLAND " Q: What is the opposite >Tel: +358 0 605 900, Fax: +358 0 605 991 of information? " The way we usually handle it is to let someone access the record if they are the ones who originally locked it. If their workstation hung up, they can get back into the application. In our environment, records are typically "owned" by a particular user for a period of time. For example, a user is responsible for all of a particular type of input for a branch. Also, we allow a lead user to "unlock" records if there are reported problems, and we clear locks during overnight processing. Jim Seach
Mon, 07 Jul 1997 11:36:24 GMT

Eric Park #8 / 8	Workaround for the Lack of Record-Level Locking Quote: >> Another approach you could take is to create a "logical lock" table. >> When you retrieve a record for update, you insert the record key, >> the username, date and time into the lock table. If the record >> was already in the table it was previously locked and you do not >> get access unless the lock is held by the same user who may have >What happens if a workstation goes down in the middle of an edit >session? The lock table will indicate a lock where there should >be none, and you'll have a heck of a time trying to figure out >which locks are real, and which ones aren't. >You'd need to couple the locking management with some sort of >user session control system, which would require a concurrent >DBMS monitor process, something which Access does not support. A programmer in our dept has a decent work around: include a "refresh date/time stamp" on the lock record, and periodically update the date/time stamp; every couple of minutes seems OK. Have a VB timer initiate the periodic update. The pseudo code for a lock attempt goes something like this: Attempt to insert a record If the insert is successful Return SUCCESS Else If refresh date/time stamp is outdated (workstation went down) Delete the lock record Reattempt to insert a record If the insert fails (unknown reason) Return FAIL Else (the lock is still current) Return FAIL Warning: be sure the clock used for the refresh date/time is reliable; the workstation clock generally is not (we are using the clock of our Sybase database server).
Sun, 20 Jul 1997 20:37:19 GMT

Page 1 of 1

[ 8 post ]