Thread: Lock record
Hi people. I'm writing a client application in Visual Basic, and I need to lock certain records (a read lock) for a long period of time (well, from the start just to the stop of my application) so that no one can modify them. I've seen a lock command, but it seem only capable to lock an entire table. I'm using ADO, and it seem possible to lock a record by opening a recordset on it (with a proper query) and keeping that recordset open (I think that it's the cursor that keeps the lock on the db). Anyone knows a different/better method? Thanks Andrea
Andrea Aime wrote: > > Hi people. I'm writing a client application in Visual Basic, > and I need to lock certain records (a read lock) for > a long period of time (well, from the start just to > the stop of my application) so that no one can modify > them. I've seen a lock command, but it seem only capable > to lock an entire table. I'm using ADO, and it seem possible > to lock a record by opening a recordset on it (with a > proper query) and keeping that recordset open (I think > that it's the cursor that keeps the lock on the db). > Anyone knows a different/better method? First, locking is evil. All I achieves is make any other client trying to access that record jam up. If you want to handle multiple people modifying the same record, maybe you should look into transactions... More info maybe be needed here... HTH, -- Martijn van Oosterhout <kleptog@cupid.suninternet.com> http://cupid.suninternet.com/~kleptog/
Andrea Aime wrote:
> Hi people. I'm writing a client application in Visual Basic,
> and I need to lock certain records (a read lock) for
> a long period of time (well, from the start just to
> the stop of my application) so that no one can modify
> them. I've seen a lock command, but it seem only capable
> to lock an entire table. I'm using ADO, and it seem possible
> to lock a record by opening a recordset on it (with a
> proper query) and keeping that recordset open (I think
> that it's the cursor that keeps the lock on the db).
> Anyone knows a different/better method?
Skip and forget about anything below if your app isn't an
interactive one, waiting sometimes for user input.
Back in the late 80's, I remember that a customer payed
millions to Siemens just that they add a "hold DB lock over
interaction step" feature to their BS2000 UTM (system like
CICS on IBM). All that money was wasted because they never
really used that feature - after it was implemented they
discovered that all Siemens warnings about "that is extremely
dangerous" where true.
Believe it or not, but holding pure DB locks over
"interaction" in an interactive application isn't what you
really want! The user might go for coffee, and such long time
locks are not what the locking mechanism of databases is
intended for - so it's not optimized for this kind of abuse!
I've used a generic "lock-object" table in the past, and used
a LISTEN/NOTIFY mechanism along with lookup in pg_listener to
identify dead object locks with success. Need to dig out my
old 4.2 works - tell me if you need some details and I'll
strart to dig.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
On Thu, Jun 15, 2000 at 12:45:52AM +0200, Jan Wieck wrote:
> Believe it or not, but holding pure DB locks over
> "interaction" in an interactive application isn't what you
> really want! The user might go for coffee, and such long time
> locks are not what the locking mechanism of databases is
> intended for - so it's not optimized for this kind of abuse!
Allow me to echo the above sentiment. Our library automation system is
built on a PICK back end (UniVerse), and the implementation locks any record
that is in current use. A good thing, that, in so far as you don't want,
say, two people writing to the same patron record at the same time. Problem
is, patrons are frequently checking books out while someone else is checking
in the items the patron had out before. Kablooey.
What's supposed to happen, of course, is that the ckeck-in or check-out
clerk gets a message, "patron file is locked -- wait or quit?" In practice,
there are too many cases where lock contention is not handled properly, and
both terminals get locked up. This is often a pain to resolve; I spend
quite a bit of time just trying to figure out where the lock is coming from.
And this doesn't even begin to touch the times where a staff member was
helping a patron look at his/her holds (or whatever), and then just left the
session logged in to that person's record (which is a problem of bad user
behaviour, yes, but knowing that doesn't help when you're trying to break
someone's 20 year bad habit).
Use transactions. Much better than locking.
--
Andrew Sullivan Computer Services
<sullivana@bpl.on.ca> Burlington Public Library
+1 905 639 3611 x158 2331 New Street
Burlington, Ontario, Canada L7R 1J4
Andrew Sullivan wrote: > On Thu, Jun 15, 2000 at 12:45:52AM +0200, Jan Wieck wrote: > > > Believe it or not, but holding pure DB locks over > > "interaction" in an interactive application isn't what you > > really want! The user might go for coffee, and such long time > > locks are not what the locking mechanism of databases is > > intended for - so it's not optimized for this kind of abuse! > > Allow me to echo the above sentiment. Our library automation system is > built on a PICK back end (UniVerse), and the implementation locks any record > that is in current use. A good thing, that, in so far as you don't want, > say, two people writing to the same patron record at the same time. Problem > is, patrons are frequently checking books out while someone else is checking > in the items the patron had out before. Kablooey. > > What's supposed to happen, of course, is that the ckeck-in or check-out > clerk gets a message, "patron file is locked -- wait or quit?" In practice, > there are too many cases where lock contention is not handled properly, and > both terminals get locked up. This is often a pain to resolve; I spend > quite a bit of time just trying to figure out where the lock is coming from. > And this doesn't even begin to touch the times where a staff member was > helping a patron look at his/her holds (or whatever), and then just left the > session logged in to that person's record (which is a problem of bad user > behaviour, yes, but knowing that doesn't help when you're trying to break > someone's 20 year bad habit). > > Use transactions. Much better than locking. > > -- > Andrew Sullivan Computer Services > <sullivana@bpl.on.ca> Burlington Public Library > +1 905 639 3611 x158 2331 New Street > Burlington, Ontario, Canada L7R 1J4 Why is a transaction better than a lock ? I have worked with locks without transactions and locks with transactions, and transactions alone. When you have two transactions on the same record, say User A in program P Begin transaction A little time later User B in program P Begin transaction Then user A inhibits user B from going further until transaction A has been completed. If this person goes for a coffee, then transaction B will also be held up indefinitely. With the systems I have worked with, the only thing you can do with transactions is begin them and commit or rollback, while the locks would be used to get hold of the needed data. Further functionality of locks included always the possibility of specifying a time-out and getting the name of the user who currently holds the lock. Jurgen Defurne defurnj@glo.be
Jurgen Defurne wrote: > > Andrew Sullivan wrote: > > > On Thu, Jun 15, 2000 at 12:45:52AM +0200, Jan Wieck wrote: > > > > > Believe it or not, but holding pure DB locks over > > > "interaction" in an interactive application isn't what you > > > really want! The user might go for coffee, and such long time > > > locks are not what the locking mechanism of databases is > > > intended for - so it's not optimized for this kind of abuse! > > > > Allow me to echo the above sentiment. Our library automation system is > > built on a PICK back end (UniVerse), and the implementation locks any > > Why is a transaction better than a lock ? I have worked with locks without > transactions > and locks with transactions, and transactions alone. When you have two > transactions > on the same record, say > > User A in program P > Begin transaction > > A little time later > User B in program P > Begin transaction > > Then user A inhibits user B from going further until transaction A has been > completed. Not with multi-versioning. Please see the link below for details: http://www.postgresql.org/docs/postgres/mvcc.htm Hope that helps, Mike Mascari
On Thu, Jun 15, 2000 at 08:08:50PM +0200, Jurgen Defurne wrote:
> Then user A inhibits user B from going further until transaction A has been
> completed. If this person goes for a coffee, then transaction B will also be
> held
> up indefinitely.
[. . .]
>
> of the needed data. Further functionality of locks included always the
> possibility
> of specifying a time-out and getting the name of the user who currently holds
> the lock.
In my pg_options file, I have the ability to time out deadlocks like you
describe. The file should be located at $PGDATA/pg_options. See below:
# deadlock timeout; set this to a non-zero integer, which is the number
# of seconds that the backend should wait before deciding that it is in
# a deadlock and timing out. The system default is 1 second.
deadlock_timeout = [insert your value here]
--
Andrew Sullivan Computer Services
<sullivana@bpl.on.ca> Burlington Public Library
+1 905 639 3611 x158 2331 New Street
Burlington, Ontario, Canada L7R 1J4