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 anyWhy 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, sayUser A in program P
Begin transactionA little time later
User B in program P
Begin transactionThen 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