Thread: Lock rekord
Hello I was listening Your discusion. I have practical problem for which I write my own locking system: Personal rekords: Name zip adress Users A,B reads data from record: John Smith 124312 Xstreet 27 Now A change zip and write all B change adress and write so zip is unchanged. In pgsql I can solve it by SELECT FOR UPDATE but if user A goes for caffe during his update user B hangs for several minutes ... Ther is no solution to check if record is in transaction ? Am I right ? Regards Pawel Dubin
"Pawe³ Dubin" wrote: > Hello > > I was listening Your discusion. I have practical problem for which I write my > own locking system: > > Now A change zip and write all > B change adress and write > > so zip is unchanged. > > In pgsql I can solve it by SELECT FOR UPDATE but if user A goes for caffe > during his update > user B hangs for several minutes ... > > Ther is no solution to check if record is in transaction ? I usually prefer the following trick for preventing long locking times. On every table I define a timestamp field which is updated every time the record is written to the database. If a user edits a record (without locking) and commit his changes the timestamp is returned from the client program unchanged. The program reads the record again for update and compares the timestamp from the database and the timestamp from the user program. If the timestamp has changed in the meantime the record was updated from someone else and the transaction will be rejected. Regards Herbie -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Herbert Liechti E-Mail: Herbert.Liechti@thinx.ch ThinX networked business services Stahlrain 10, CH-5200 Brugg ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> I usually prefer the following trick for preventing long locking > times. On every > table I define a timestamp field which is updated every time the record is > written to the database. If a user edits a record (without > locking) and commit his changes > the timestamp is returned from the client program unchanged. The program > reads the record again for update and compares the timestamp from > the database > and the timestamp from the user program. If the timestamp has changed > in the meantime the record was updated from someone else and the > transaction will be rejected. What happens if someone else updates the record *just* after the record is reread for update and timestamp compared? - Andrew
List z dnia: Sat, 17 Jun 2000, : > > I usually prefer the following trick for preventing long locking > > times. On every > > table I define a timestamp field which is updated every time the record is > > written to the database. If a user edits a record (without > > locking) and commit his changes > > the timestamp is returned from the client program unchanged. The program > > reads the record again for update and compares the timestamp from > > the database > > and the timestamp from the user program. If the timestamp has changed > > in the meantime the record was updated from someone else and the > > transaction will be rejected. and all the work done by the user (filling long form) gone out > > What happens if someone else updates the record *just* after the record is > reread for update and timestamp compared? It's ok. I do the same. 1. Update lock field for marked to transaction (everyone else need to know It is locked) 2. begin transaction 3. fill form 4. write to database 5. end transaction 6. delete lock field contents So applicaction should check if record is in transaction via select on lock field But it would be very nice to check it via system. for example if SELECT FOR UPDATE on record being in transaction returns error. Pawel
"Pawe³ Dubin" wrote: > List z dnia: Sat, 17 Jun 2000, : > > > I usually prefer the following trick for preventing long locking > > > times. On every > > > table I define a timestamp field which is updated every time the record is > > > written to the database. If a user edits a record (without > > > locking) and commit his changes > > > the timestamp is returned from the client program unchanged. The program > > > reads the record again for update and compares the timestamp from > > > the database > > > and the timestamp from the user program. If the timestamp has changed > > > in the meantime the record was updated from someone else and the > > > transaction will be rejected. > > and all the work done by the user (filling long form) gone out Yes, but how often this really occurs? You may handle this case in your application i.e. by showing the differences between the record in the database and the changes submitted by the user. I made very good experience in the past 10 years with this system even in big applications where a large number of users were using the database concurrently. > > > > > > What happens if someone else updates the record *just* after the record is > > reread for update and timestamp compared? If you reread the record for updating nobody else should be able to update the record. The record is locked. The method I suggested is a way to keep the locking times as short as possible. > > > It's ok. I do the same. > 1. Update lock field for marked to transaction (everyone else need to know It > is locked) And what happens if a user is closing his browser i.e. in a web application? What are you doing with the record marked as locked? Regards Herbie -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Herbert Liechti E-Mail: Herbert.Liechti@thinx.ch ThinX networked business services Stahlrain 10, CH-5200 Brugg ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > > > I usually prefer the following trick for preventing long locking > > > > times. On every > > > > table I define a timestamp field which is updated every time the record is > > > > written to the database. If a user edits a record (without > > > > locking) and commit his changes > > > > the timestamp is returned from the client program unchanged. The program > > > > reads the record again for update and compares the timestamp from > > > > the database > > > > and the timestamp from the user program. If the timestamp has changed > > > > in the meantime the record was updated from someone else and the > > > > transaction will be rejected. > > > > and all the work done by the user (filling long form) gone out > > Yes, but how often this really occurs? You may handle this case in your > application i.e. by showing the differences between the record in the > database and the changes submitted by the user. I made very good > experience in the past 10 years with this system even in big applications > where a large number of users were using the database > concurrently. > > If you reread the record for updating nobody else should be able to > update the record. The record is locked. The method I suggested is > a way to keep the locking times as short as possible. > > > > > > > It's ok. I do the same. > > 1. Update lock field for marked to transaction (everyone else need to know It > > is locked) > > And what happens if a user is closing his browser i.e. in a web application? > What are you doing with the record marked as locked? > I timed out locks in database and get phones from users but it is little problem becouse my application is in java and browser invokes finalyze method with unlock procedure. I have only problems with browser crasches. In my case locking problem is greater than in web apps (I thing) because users frequently try to modify the same record and I thing they will be not happy trying to fill form for the second, .... once taking them about 2 minutes. Pawel