SQL record locking
in [Visual Objects]
|
Prev: Vulcan lastest version
Next: VO CDX and Comix CDX
From: Rob van Erk on 20 Jul 2008 15:41 I'm reading a lot (in SQL books, on this forum) about record locking. Frankly speaking I find it a rather complex part of my SQL (Vo2Ado) 'study'. My idea is to follow this, simple, approach: - when a user opens a record I use the UPDATE SQL command to directly put the users' name in a special field named as "INUSEBY" in same table; - other users will now see that the record is in use. When another user tries to open the record it will only be possible if the "INUSEBY" field is empty. I think I will just allow them to view the record and I could simply disable an available "Save" button" my EditWindow. What is wrong with the above approach? Is there something I'am overlooking? My only concern is that if a user edits a records and his Pc (or OS) crashes the record will still be in use by that person. But, no doubt that SQL Express (and in a later stage MS SQL 2005) will have options to release all locks with a special command which, I hopefully, could even run from within Vo. Thks for comments on the above. Will be my last topic for the coming weeks. Starting to read a lot of this stuff on the camping.... I'am glad with Vo2Ado and I'am happy that I took the decision to focus on SQL from now on. Brgds, Rob
From: Geoff Schaller on 20 Jul 2008 18:48 Rob. Good question and a very important concept. And guess what, the issue of contention (simultaneous edits) is the same for SQL as it is in DBF. It is also worthy of a 90 min discussion so it is very difficult to summarise in a news post. Mostly, as it was with DBF, optimistic locking and double buffering is a perfectly valid concept to live with. However when the prospect for simultaneous edit exists you either need to protect one edit with a transaction or do it the manual way you speak of. There is no right and wrong and both require a mechanism to release held locks. The transaction way is a real lock (and is harder to release). Your way is what is often termed a 'soft lock' because it is implemented in code. Both are quite useful in the right circumstances Geoff "Rob van Erk" <erk.v(a)hotmail.com> wrote in message news:c460e78d-ee3e-4ee0-8aae-abe27b1c7255(a)x41g2000hsb.googlegroups.com: > I'm reading a lot (in SQL books, on this forum) about record locking. > Frankly speaking I find it a rather complex part of my SQL (Vo2Ado) > 'study'. My idea is to follow this, simple, approach: > > - when a user opens a record I use the UPDATE SQL command to directly > put the users' name > in a special field named as "INUSEBY" in same table; > - other users will now see that the record is in use. When another > user tries to open the > record it will only be possible if the "INUSEBY" field is empty. I > think I will just allow them to > view the record and I could simply disable an available "Save" > button" my EditWindow. > > What is wrong with the above approach? Is there something I'am > overlooking? My only concern is that if a user edits a records and his > Pc (or OS) crashes the record will still be in use by that person. > But, no doubt that SQL Express (and in a later stage MS SQL 2005) will > have options to release all locks with a special command which, I > hopefully, could even run from within Vo. > > Thks for comments on the above. Will be my last topic for the coming > weeks. Starting to read a lot of this stuff on the camping.... I'am > glad with Vo2Ado and I'am happy that I took the decision to focus on > SQL from now on. > > Brgds, > Rob
From: Robert van der Hulst on 21 Jul 2008 04:35 Hi Rob, On Sun, 20 Jul 2008, at 12:41:19 [GMT -0700 (PDT)] (which was 21:41 where I live) you wrote about: 'SQL record locking' > I'm reading a lot (in SQL books, on this forum) about record locking. > Frankly speaking I find it a rather complex part of my SQL (Vo2Ado) > 'study'. My idea is to follow this, simple, approach: > - when a user opens a record I use the UPDATE SQL command to directly > put the users' name > in a special field named as "INUSEBY" in same table; > - other users will now see that the record is in use. When another > user tries to open the > record it will only be possible if the "INUSEBY" field is empty. I > think I will just allow them to > view the record and I could simply disable an available "Save" > button" my EditWindow. > What is wrong with the above approach? Is there something I'am > overlooking? My only concern is that if a user edits a records and his > Pc (or OS) crashes the record will still be in use by that person. > But, no doubt that SQL Express (and in a later stage MS SQL 2005) will > have options to release all locks with a special command which, I > hopefully, could even run from within Vo. The problem with your approach is that if the user that starts to edit crashes his record, then the lock will not be reset. At least you should add a field with the date/time of the lock, so you will know whether a lock is still valid or not. Another alternative is a separate locks table with columns for table name, primary key value, user and date/time of the lock. You can then also schedule a task on the server to periodically clear old locks from the table. -- Robert van der Hulst AKA Mr. Data Vo2Jet & Vo2Ado Support VO & Vulcan.NET Development Team www.heliks.nl
From: Rob van Erk on 21 Jul 2008 15:05 Geoff, Robert, The basics are clear now. Thanks. For the time being I go for the "soft lock" and will specify username/date,time in separate columns. Only will have to check (in the books...) how to unlock are record (preferably with administrative command) in my application. Brgds, Rob
From: richard.townsendrose on 21 Jul 2008 23:02
Rob I have been thru this saga many times...... What i do is table locks. and i forget all about checking if the record has changed. mostly one is writing new records. locking a table is much faster .... and when there is a crash, there are no held open record locks - apart from the userlog table which doesn't matter - indeed one can use that to tell who is and isn't logged on as it has datetimesin and datetimesout fields, and mode out (normal, controlled crash, or bang out) richard |