Problem in Applying Exclusive Lock on table in SQL Server

[Avadhoot]

Hi all,
With ur input about locks on table for I/U/D using TabLock worked for my last query.

Now I've a problem about applying lock to prevent reading data.
I mean once I apply lock the table, it should not allow to read the data even for any other process.

I tried to apply Exclusive Lock using
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT * FROM A (TABLOCKX)
COMMIT TRANSACTION

& then checked using sp_Lock, it shows me that locktype is X ; i.e. Exclusive.
However when I give select statement again, it returns me the data as it is where I don't want to see the data..

Kindly let me know whether I am correct? Or what I need to do for the achieving the same.

situation is some thing like that I want to apply Exclusive lock on say Table A, once lock is there..I am starting some other process which uses to read data from A for further work.
In that I want to apply lock exclusively so that process should get halted as Table 'A' is not at all available for any operations, even Reading the data from it for any kinda purpose !!!!

Request you to reply my query urgently if you have any solution ...

Thanks in advance..

Avadhoot

[skhanal]

Are you issuing the second select from the same connection?.

If yes then, it is not blocked because that connection holds the lock, however all other connections will be blocked.

[Avadhoot]

Hi skhanal,
thanks for ur comments. I was doing the same thing as u said. Now what I've done is , locked table from one login privilage and tried to access table from ther login, it shows table is locked.
thanks again..

Avadhoot