Blocking when missing Permissions
The strange case of the blocked index rebuild
Recently, a client drew my attention to a blocking problem he could not make any sense of.
After a brief analysis, the scenario can be described quite simply:
Session 1) (SPID 69 in my example)
A user carries out an update statement on a table within an explicit transaction, but which fails due to the lack of required permissions. The transaction, however, remains open. (The is of course the actual problem, as one will conclude at the end. A basic error processing is essential.)
Session 2) (SPID 67)
In the meantime, an index rebuild is running via a maintenance job. And this one is stuck due to a block which was caused by the previously described session.
Part 1: blocked index rebuild
What kind of a blocking can be maintained by a non-executable update statement? – That is a reasonable question.
You can see the set locks through session 1, identified via the DMV sys.dm_tran_locks, here:
First, one will probably notice that the table has received an IX. – Even though further below in the hierarchy no further lock was set, nor could be set! There are no further locks at page or key level as would be the case otherwise in a successful update.
Parallel to this, metadata locks stand out: these are at the schema of the table (5) and the database principal dbo. We will get to this in the 2nd part further below.
Which of these is causing the blocking?
Those who have dealt with index rebuilds know that these set an Sch-M Lock (schema modification lock). And this particular lock type isn’t compatible with any other lock mode! (https://technet.microsoft.com/en-us/library/ms172925(v=sql.110).aspx )
Accordingly, we see the following locks and wait states:
(Session 69 was tried by the update, session 67 tries the index rebuild)
For those indulging in a professional monitoring tool, it is a little bit easier to interpret via SQLSentry Blocking monitor:
As you will notice, the Sch-M Lock actually waits for the release and is blocked until the end of the lock of the transaction with the failed update keeping the IX-lock at table level.
By the way, this is also the decisive difference to Index Reorganize. (hint)
Part 2: The key question: Could one simply grant permissions via a GRANT UPDATE directly for the table, then have the 1st transaction repeat the update command, and expect that in so doing their locks disappear and continue the index rebuild as a result?
The command for this would be:
GRANT UPDATE
ON SQL_Demos.Transactions
TO SQLUserX
According to the documentation, permissions will immediately become “active” and do not require a login again.
The answer is nonetheless: No, in this particular case it does not work like this.
Why?
The problem is the Sch-M-Lock in the table („OBJECT“).
A GRANT UPDATE at object level requires a Sch-S-Lock in the table.
The following blocking chain is created (the crucial lines are highlighted in yellow):
(#1 is created by the update attempt, #2 by the index rebuild that now has to wait, and #3 by the GRANT waiting for it)
Here, it is worth relaunching the SQLSentry Blockade monitor again:
But those who got used to the Best Practice of granting permissions only at scheme level (also see: Schema-design for SQL Server: recommendations for Schema design with security in mind) also avoid these locks at object level!:
GRANT UPDATE
ON SCHEMA::SQL_Demos
TO SQLUserX
The locks for this hence are at scheme and database-principal-level, and thus not in the way.
The principal for testing has the principal-ID 5, and also, my test scheme happens to have the ID 5.
For insiders: Object-ID 29 belongs to the internal system table sysprivs that contains the database permissions.
It would be even better to work exclusively with roles and schemas.
Adding the user to a role retroactively looks like this at lock-level:
In other words: Also no problem for continuing to work in the table.
As one can recognize by the high number, the Principal-ID 16391 belongs to the built-in database roles, in this case the db_datawriter role.
I hope this analysis of a rather rare scenario was interesting to some of you.
Andreas
(Still-owner of Sarpedon Quality Lab® (http://andreas-wolter.com/de-1809_news_senior_program_manager_microsoft_sql_server_security_team/ ))
Wow. Totally makes sense after you walked us through it, but i never would have expected that. Fantastisch!!
Happy to hear that I could get some by surprise. I also never thought of that before.