Yes, especially when they’re part of a larger transaction.
In this example, I’m going to use a database in Read Committed Snapshot Isolation (RCSI), my favorite isolation level for building new applications from the ground up. Optimistic concurrency (aka MVCC) helps avoid a lot of blocking issues – but not all of them.
In a database with RCSI enabled, start by creating two tables:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE dbo.Table1 (ID INT IDENTITY(1,1), Things VARCHAR(50)); GO CREATE TABLE dbo.Table2 (ID INT IDENTITY(1,1), Things VARCHAR(50)); GO /* Only Table1 will have data: */ INSERT INTO dbo.Table1 (Things) SELECT 'Meaningless Stuff' FROM sys.all_columns GO |
Then start a transaction that does a lot of work, but don’t commit:
|
1 2 3 4 5 |
/* WINDOW #1: Start a transaction that does a lot of work */ BEGIN TRAN UPDATE dbo.Table1 SET Things = 'Different Stuff'; GO |
Now start a new tab or window in SSMS – we’re going to start a separate transaction in the same database, but working on Table #2 instead. We’ll call this Window #2 (because it’s 5AM when I’m writing this, and the caffeine hasn’t kicked in yet.)
|
1 2 3 4 5 |
/* WINDOW #2: Start a transaction that does just a little work on an empty table: */ BEGIN TRAN ALTER TABLE dbo.Table2 ADD NewCol VARCHAR(10); GO |
That query finishes instantly because it’s a metadata-only change in modern versions (even if you set a default, as Remus explains.) Both transactions are getting along just fine right now, not blocking each other, because they’re working on different tables.
Now, let’s cause a deadlock.
In Window #2, where you did just a little bit of work, try to do much more work – but monkey with the table that’s already locked by Window #1:
|
1 2 3 |
/* WINDOW #2: */ UPDATE dbo.Table1 SET Things = 'Even more different stuff'; GO |
Aaaaaand nothing happens – he’s blocked because Window #1 already has a lock on those rows. He won’t make progress, and he doesn’t time out. He just sits there waiting patiently.
Now switch over to Window #1 and run a select:
|
1 2 3 |
/* WINDOW #1 */ SELECT * FROM dbo.Table2; GO |
At first, nothing happens – because he’s blocked by Window #2. When Window #2 added a column to Table2, that schema change is a big deal, and it even blocks SELECTs. After all, the SELECT can’t have some of his rows come back without the new column, and some of them with it.
Within 5 seconds, the SELECT wins.
SQL Server’s deadlock monitor wakes up, sees that we have a Mexican standoff. Neither window can make progress until the other window gives up. SQL Server looks at which session would be the easiest to roll back, and kills that session’s work.
Window #2’s UPDATE sees:
|
1 2 3 4 |
Msg 1205, Level 13, State 56, Line 9 Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. |
Window #1’s SELECT won because in his transaction, he’d already done a lot of work. Window #2 hadn’t done that much – the alter table was a tiny metadata-only change. Sure, he was trying to run an update that would do a lot of work, but he hadn’t done it yet, so he was trivially easy to roll back.
In the past, I’ve often said, “In RCSI, readers don’t block writers, and writers don’t block readers” – but that’s not exactly true, as this demo indicates. However, I’m…still gonna say that, because it’s mostly true. The problem is that the OTHER writing we’d done previously in the transaction is what killed us here.
Wanna learn more?
I do – I find this stuff really interesting because it happens so fast. Locking and blocking can just appear and disappear in your servers, and it’s really hard to track down if you can’t reproduce it easily. That’s why I like learning the internals on this stuff so I can recognize the symptoms when they strike.
If you’re like me, check out Kalen Delaney’s upcoming 4-hour class, Locking and Blocking: SQL Server Concurrency Control. It’s really hard to find deep-dive internals lessons on this stuff, and Kalen knows internals. I’ll be there myself!
