Re: [sqr-users] Table locking

["George Jansen" <GJANSEN@aflcio.org>]

Is the table declared to enforce the one-row cardinality? That is, do
you have the equivalent of 

CREATE TABLE mytab
(
  DUMMY NUMBER CHECK (DUMMY = 1),
  REALVAL VARCHAR2(32),
  CONSTRAINT PK$MYTAB PRIMARY KEY (DUMMY));

If so, and if you do not require intermediate commits within the
processing, you can simply lock the row by updating it, then unlock by
committing after your real update:

Begin-SQL
  UPDATE mytab
  SET realval = realval;
End-SQL

Do Real-Processing

Begin-SQL
   UPDATE mytab
   SET realval = $new-realval
End-SQL

COMMIT

Failing that, I guess my question would be whether the table is the
main point here. If what you're trying to do is manage concurrency with
a semaphore, then perhaps there are ways to do that within Transact SQL
(that is what it's called),  as in Oracle, you'd use the PL/SQL package
DBMS_LOCK.
>>> rwright@therousecompany.com 10/29/03 02:01PM >>>
I'm running with SQL Server 2000. In my SQR, I need to read a
one-record
table at the beginning of the SQR, and keep the record/table locked
exclusively until the SQR nears completion, at which point I'll update
the table and release the lock. Does any one know how I can accomplish
this?

 

Rod Wright

Senior Applications Specialist

The Rouse Company

410-992-6307
 
The information contained in this transmission is for the sole use of
the intended recipient(s) and 
may contain confidential and privileged information.  Any unauthorized
review, use, disclosure
or distribution of this communication is strictly prohibited.  If you
have received this e-mail in 
error, please contact the sender by reply e-mail and destroy all paper
and electronic copies of
the original message. 

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users