Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

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
if another program trys to execute the same SQL code at the same time. Two
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/ Inc.
Phone: 763-512-2740
Phone: 612-512-2740

>-----Original Message-----
>From: Seaman, Mark []
>Sent: Monday, October 02, 2000 1:29 PM
>To: SybPerl Discussion List
>Subject: RE: select... for update
>	I think the "holdlock" option of the SELECT statement 
>does what you
>want to do.
>Mark Seaman
>-----Original Message-----
>From: Matthew E Studley []
>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:
>  select col1 into var1 from table1 FOR UPDATE;
>  .. some stuff
>  update table1 set col2 = new_value where col1 = var1;
>  error handling commit / rolllback etc
>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...
>Matthew Studley
>Technical Architect 
>				126-128 New Kings Rd
>+44 (0) 7080 810 101		London  SW6 3LZ.