|
|
sybperl-l Archive
Up Prev Next
From: "McIlhargie, Rob" <rmcilhargie at exchange dot webmd dot net>
Subject: RE: select... for update
Date: Oct 2 2000 8:22PM
The holdlock keyword in Sybase will give you a read lock on the
variable. If you then
try to promote the lock to an exclusive lock for an update you'll get a
deadlock
if another program trys to execute the same SQL code at the same time. Two
applications
could get the readlock at the same time and then they would both try to
promote the lock
to an exclusive lock and they would come to an impass causing a deadlock.
Try using a bogus update to get an exclusive lock immediately at the
beginning of the transaction instead. This will eliminate any chance for
deadlocks and insure that the
value won't change while the transaction is running. I haven't found another
way to
do this in Sybase. I have tried setting the isolation level but that did the
same thing
as the holdlock. Does anyone know of another way to do this?
begin transaction
update set foo = foo /* Bogus update. No affect, just gets the
exclusive lock. */
from table
select foo from table
... do some other stuff here.
end transaction
--
Rob McIlhargie
Software Engineer
Healtheon/WebMD.com Inc.
http://www.healtheon.com/
http://www.webmd.com/
Email: rmcilhargie@webmd.net
Phone: 763-512-2740
Phone: 612-512-2740
>-----Original Message-----
>From: Seaman, Mark [mailto:Mark.Seaman@vtmednet.org]
>Sent: Monday, October 02, 2000 1:29 PM
>To: SybPerl Discussion List
>Subject: RE: select... for update
>
>
>Matt,
> I think the "holdlock" option of the SELECT statement
>does what you
>want to do.
>
>Mark Seaman
>-----Original Message-----
>From: Matthew E Studley [mailto:matthew@coms.com]
>Sent: Monday, October 02, 2000 2:04 PM
>To: SybPerl Discussion List
>Subject: select... for update
>
>
>ladies and gents
>
>quick transact sql q. if i may...
>
>in oracle I can do something like this:
>
>begin
> select col1 into var1 from table1 FOR UPDATE;
> .. some stuff
> update table1 set col2 = new_value where col1 = var1;
>
> error handling commit / rolllback etc
>
>end
>
>the FOR UPDATE bit gives me a lock on this data row.
>
>has anybody got any pointers on how I acquire a lock with a select
>statement in transact sql?
>
>thanks for any help...
>
>matt
>
>Matthew Studley
>_____________________________________________________
>Technical Architect coms.com
> 126-128 New Kings Rd
>+44 (0) 7080 810 101 London SW6 3LZ.
>
|
