Why would GRANT SELECT issue Sch-M lock?
-
Hello!
Why would SQL Server 2000 issue a Sch-M (Schema Modification) lock when you try to give a user SELECT permission on an object?
Thanx, Hans.
-
from the books online
Schema Locks
Schema modification (Sch-M) locks are used when a table data definition language (DDL) operation (such as adding a column or dropping a table) is being performed.
Schema stability (Sch-S) locks are used when compiling queries. Schema stability (Sch-S) locks do not block any transactional locks, including exclusive (X) locks. Therefore, other transactions can continue to run while a query is being compiled, including transactions with exclusive (X) locks on a table. However, DDL operations cannot be performed on the table.
Does this only happens during a particular select?
In what way are you granting the select?
-
Thanx, I had read that
Well, say that user myDom\Joe does a SELECT col1,col2,SUM(col3) FROM BigTable GROUP BY col1,col2 in a OLAP environment (query takes 1.5 hours to run).
This places a S lock on the table.
I do a GRANT SELECT ON dbo.BigTable TO [myDom\Frank] which tries to place a Sch-M lock on the table. The process goes into 'Wait' state and is blocked by the SPID of 'myDom\Joe' (the one with the select).
I could understand a Sch-S lock but a Sch-M?
I was wondering Why SQL Server (2000) would need to do this...
And No, this happens during all SELECT / GRANT combinations described above.
And I am GRANTing the SELECT permission as so: Running 'GRANT SELECT ON dbo.BigTable TO [myDom\Frank]' from inside SQL Query Analyzer.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply