Table Locking

hortoristic · February 9th, 2004, 08:02 PM

Anyone know the equiv M$ command that does table locking such as below? This code snippet is from Sybase and I need to convert it to M$ SQL2k - SP3.

/* Lock the tables that will be transferred TO DW.
* The locks will be removed when this stored procedure
* is exited. "mode wait" means wait patiently
* until the table can be locked. */

lock table CPLACT in exclusive mode wait
lock table CPLSUP in exclusive mode wait
lock table CNSACT in exclusive mode wait
lock table CNSSUP in exclusive mode wait

_________________________
Joe Horton
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928 fax (360) 902-6200

Jeff Mason · February 9th, 2004, 09:17 PM

Explicit locking of rows in a table, or of the entire table, is handled via "locking hints" in SQL Server. These hints may be placed in SELECT, INSERT, UPDATE, and DELETE statements and are added to the table(s) in the FROM clause, as, e.g:

Code:

SELECT * FROM MyTable WITH (TABLOCKX, HOLDLOCK)
WHERE ...

will acquire an exclusive lock (read and update lock) on the table and hold it until the transaction in which it resides is completed (committed or rolled back). See "table hints" in the BOL index for the list of the various hint values.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com