Lock table with NOWAIT in MSSQL

[Erland Sommarskog]

[posted and mailed, please reply in news]

Does exist command in the MSSQL that is the same like ORACLE command
LOCK TABLE ee IN EXCLUSIVE MODE NOWAIT; ?
In the concrete, during transaction (where is this command used) other
users
-can read from table EE and result is status before transaction begining
-cannot modify table EE (neither INSERT nor DELETE nor UPDATE)
-cannot start the same transaction and get immediatelly information about
it

It is possible that

BEGIN TRANSACTION

SELECT @dummy = COUNT(*) FROM tbl WITH (INDEX = 0, TABLOCK, UPDLOCK)

will do the trick for you.

However, this is probably not a good solution to your actual problem.
MS SQL Server and Oracle are two very different beasts, and trying to
program SQL Server as if it was Oracle is no road to success.

If you provide details about your actual business problem, you may get
better suggestions.

[Sinisa Catic]

With command LOCK TABLE … IN EXCLUSIVE MODE NOWAIT you will acquire
exclusive lock on table, which is different from the row level locking
default behavior, but this will not prevent other Oracle data readers
from seeing data before your transaction started. You can acquire
exclusive table lock in SQL Server (with TABLOCKX query hint) but this
will prevent other data readers from seeing data. This is because of
the different locking and transaction isolation mechanisms between
Oracle and SQL Server. However, here is missing info why you are
requesting whole table lock instead of leaving to RDBMS to manage
locking automatically.

Sinisa Catic

Does exist command in the MSSQL that is the same like ORACLE command
LOCK TABLE ee IN EXCLUSIVE MODE NOWAIT; ?
In the concrete, during transaction (where is this command used) other users
-can read from table EE and result is status before transaction begining
-cannot modify table EE (neither INSERT nor DELETE nor UPDATE)
-cannot start the same transaction and get immediatelly information about it
Thank for help
Nentvichova

[Nentvichova]

Hi, thanks for your answers.

unfortunately, command
SELECT @dummy = COUNT(*) FROM tbl WITH (INDEX = 0, TABLOCK, UPDLOCK)
doesn't solve my problem, because others users cannot select from TBL
old data during transaction .

In some detail:

I need procedure, that will realised long lasting changes on one table
(deleting and reloading from other tables).
- This procedure should be stopped when it is already running from
other
session. And it should send message about it immediately
- Old data should be accessible for select from all sessions during
procedure running
- Nobody else can change data during this reloading.

These is completely solved in ORACLE by one command:
LOCK TABLE tbl IN EXCLUSIVE MODE NOWAIT;

and I am looking for something like it in MSSQL

I don't know MSSQL SERVER (neither ORACLE) well, but I don't believe
that doesn't exist simplier solution than this one, that I am able to
do but I don't like it:



(transaction isolation level is set at READ COMMITED)

CREATE PROCEDURE prc
AS
BEGIN TRANSACTION

SET LOCK_TIMEOUT 100
SELECT * FROM dummy WITH (TABLOCK, UPDLOCK) -- table DUMMY empty
unusable
SET LOCK_TIMEOUT -1

/* or something as

INSERT INTO ordertbl VALUES (@@SPID) -- ordertbl (order INT IDENTITY,
id INT)
SELECT @first=id FROM ordertbl
WHERE order= (SELECT MIN(order) FROM ordertbl)

IF @@SPID!=@first .......
...
*/



IF @@ERROR=1222
BEGIN
ROLLBACK TRANSACTION
... -- here will be err traitment
END
ELSE
BEGIN
CREATE TABLE #tmptbl .... -- #TMPTBL with same structure like TBL
.... -- here will be long lasting changes
on #TMPTBL
DELETE tbl
-- in this moment select from TBL
is impossible
INSERT INTO tbl SELECT * FROM #tmptbl

IF @@ERROR=0
BEGIN
COMMIT TRANSACTION -- only here the select from TBL
... is possible again
END
ELSE
BEGIN
ROLLBACK TRANSACTION -- only here the select from TBL
... is possible again
END
END






I am thankful for any better solution
Dasa Nentvichova

[Greg D. Moore (Strider)]

An even more wild approach would be to drop the table, and rename the
work table. This will only work if there are no referencing foreign
constraints (unless you move them around). But this is something that
I would only use as a last resort. There are quite a few precautions
to take in this case.

Actually we do something a bit different, but requires logic in the
application.

We have TableA and TableB. From 9:00 AM to Midnight we work against TableA.
From Midnight to 9:00 AM we work against table B.

When the table is not being used (in this case recording hits to a page) we
DTS data to a roll-up server and then truncate the table.

Similar idea might owkr here.

--
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp