Understanding Sch-M locks...

[cmorel]

Hi Guys,
I have written quite a big stored procedure which creates a temporary table (multi-session) and updates it. All the statements are encapsulated in a single transaction which is explicitly declared in the code. What happens is that a lock is being put by the server on that table (of type Sch-M) in order thus preventing any type of operations on it (including simple select)

Now, I want to be able read that table from within another transaction. Why is that I cannot use a table hint NOLOCK in the select statement?

Here is some code which reproduces my problem.

Query A:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

BEGIN TRAN TR_DEMO;

CREATE TABLE ##TBL1(
	Oidx int not null primary key identity(1,1),
	Name nvarchar(30) not null,
	Type char(1) not null
);


INSERT ##TBL1 (Name,Type) VALUES ('Car','M');

WAITFOR DELAY '00:00:10';

INSERT ##TBL1 (Name,Type) VALUES ('Plane','M');

WAITFOR DELAY '00:00:10';

INSERT ##TBL1 (Name,Type) VALUES('Submarine','M');

WAITFOR DELAY '00:00:10';

DELETE FROM ##TBL1;

DROP TABLE ##TBL1;

COMMIT TRAN TR_DEMO;

Query B:

SELECT TOP 1 * FROM ##TBL1 (NOLOCK) ORDER BY oidx DESC;

Launch query A and then execute query B.

Thanks a lot for your help.

[rmiao]

It's schema modification lock, will release once the transaction finishes in your case. Nolock hint in select statement tells sql don't put lock on the table when do selection so will not block other processes, but it can't force sql to access table that has non-share lock on it already.

[cmorel]

Hi rmiao,
Then there is no way to go but moving the CREATE TABLE statement outside the scope of the transaction, as I cannot provide a NOLOCK hint on a DDL statement.
Thanks anyway,