Many moons ago, I was attending a session at a SQL Saturday like I often do, and I remember the presenter showing a demo of locks on his local instance of SQL Server. While it was not specifically a session on locking, he was describing the locks at various levels, and remarked on the database locks, “I don’t know why you’d need to lock an entire database.”
I love “I don’t know.” It’s a great starting point for learning many, many things. For purposes of discussion, I’m going to keep things super-simple.
The idea of a “locking” is really best described as controlled access to a given resource. There are a couple of different kind of locks in SQL Server, which I will roughly classify as DML and DDL locks. Data Manipulation Language locks are those that are used by DML statements, such as SELECT, INSERT, UPDATE, and DELETE. You have probably heard of Isolation Levels in SQL Server, and this is what those isolation levels affect; the behavior of those DML locks. Data Definition Language locks control the behavior of DDL statements that alter objects within the database, or the database itself, such as CREATE, ALTER, or DROP statements.
Now, let’s talk about shared vs. exclusive locks. They do pretty much exactly what they say on the label. If a process has an exclusive lock on a resource, such as a data row or an object, then nothing else can touch that resource. If the lock is a shared lock, then other processes can access that resources at the same time, as long as they don’t need an exclusive lock. Depending on whether we’re talking about DDL or DML, this can change once you start getting into things like Snapshot Isolation, but for now, we’ll keep it simple, and just say that exclusive and shared locks are incompatible. You can’t get a shared lock on a resource, while another process already has an exclusive lock on it, and vice-versa.
Certain actions that would affect an entire database, such as changing collation, enabling snapshot isolation or even simpler: dropping the database, require exclusive access to the database in order to do so. I can’t drop a database while you’re still querying it. Not only would that be rude, it would violate the ACID principles of database transactions, which is how SQL Server and other relational database platforms work. So any action that requires access to a resource within a database, comes with a shared lock on that database. An exclusive lock on the database can’t be obtained until all shared locks are released.
The “exception” to this is when issuing an ALTER DATABASE command using WITH ROLLBACK. I use the quotation marks with the word “exception” since we’re still honoring the ACID properties, we’re just forcing all running transactions with shared locks on the database to roll back, freeing up the database for an exclusive lock. Obviously, this is something you want to do very carefully, and only when you’re sure that rolling back all running transactions isn’t going to cause a problem for your application users or developers, or anyone connected to that database.
Thanks for reading.