Blocking, Locking, and Deadlocking

Thanks for watching my session on Tuning Indexes to Avoid Blocking.

Here are the demo scripts. If you want to play along with the demos, you’ll need the free Stack Overflow database.

Blocking Demo – Left Window

This session uses two windows – updates on the left, party in the back. Wait, that’s not right – updates on the left, selects on the right:

/*

Blocking, Locking, and Deadlocking - SELECT Window

Brent Ozar, 2018/02/22

Download this script, the database, and watch the session video:
https://www.brentozar.com/go/locking

MIT License

Copyright (c) 2018 Brent Ozar Unlimited

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.
*/




/* 
   ****************************************************************************
   *** THE SETUP
   ****************************************************************************
*/


/*

My SQL Server VM is:
 * 4 cores, 8GB RAM
 * SQL Server 2017 CU3
 * Has the StackOverflow.com database: BrentOzar.com/go/querystack
 * Focusing on the dbo.Users table - about 1GB in size


Let's make sure we're on the latest-and-greatest SQL 2017 compat level. I
distribute the Stack Overflow database in a SQL Server 2008 backup just so that
the most people possible can play around with it, but today, I'm using 2017.
Nothing in this demo requires 2017 - but I just want you to understand that the
behaviors I'm going to show you haven't been fixed or changed in 2017.
*/
ALTER DATABASE [StackOverflow] SET COMPATIBILITY_LEVEL = 140;
GO

/* Make sure we don't have any nonclustered indexes on the Users table.
Source: https://www.brentozar.com/archive/2017/08/drop-indexes-fast/
*/

DropIndexes;
GO

SELECT COUNT(*)
FROM dbo.Users
WHERE LastAccessDate >= '2013/11/10'
AND LastAccessDate <= '2013/11/11'
GO


BEGIN TRAN
UPDATE dbo.Users
SET Reputation = Reputation + 100
WHERE LastAccessDate >= '2013/11/10'
AND LastAccessDate <= '2013/11/11'
GO




ROLLBACK
GO
CREATE INDEX 
IX_LastAccessDate_Id
ON dbo.Users(LastAccessDate, Id)
GO


BEGIN TRAN
UPDATE dbo.Users
SET Reputation = Reputation + 100
WHERE LastAccessDate >= '2013/11/10'
AND LastAccessDate <= '2013/11/11'
GO



/* Come back after showing selects */
UPDATE dbo.Users
SET Reputation = Reputation + 100
WHERE LastAccessDate >= '2014/11/10'
AND LastAccessDate <= '2014/11/11'
GO

/* Go query Brent */


UPDATE dbo.Users
SET Reputation = Reputation + 100
WHERE LastAccessDate >= '2015/11/10'
AND LastAccessDate <= '2015/11/11'
GO

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

Blocking, Locking, and Deadlocking - SELECT Window

Brent Ozar, 2018/02/22

Download this script, the database, and watch the session video:

https://www.brentozar.com/go/locking

MIT License

Permission is hereby granted, free of charge, to any person obtaining a copy

of this software and associated documentation files (the "Software"), to deal

in the Software without restriction, including without limitation the rights

to use, copy, modify, merge, publish, distribute, sublicense, and/or sell

copies of the Software, and to permit persons to whom the Software is

furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all

copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR

IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,

FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE

AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER

LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,

OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE

SOFTWARE.

****************************************************************************

*** THE SETUP

****************************************************************************

My SQL Server VM is:

* 4 cores, 8GB RAM

* SQL Server 2017 CU3

* Has the StackOverflow.com database: BrentOzar.com/go/querystack

* Focusing on the dbo.Users table - about 1GB in size

Let's make sure we're on the latest-and-greatest SQL 2017 compat level. I

distribute the Stack Overflow database in a SQL Server 2008 backup just so that

the most people possible can play around with it, but today, I'm using 2017.

Nothing in this demo requires 2017 - but I just want you to understand that the

behaviors I'm going to show you haven't been fixed or changed in 2017.

ALTER DATABASE [StackOverflow] SET COMPATIBILITY_LEVEL = 140;

/* Make sure we don't have any nonclustered indexes on the Users table.

Source: https://www.brentozar.com/archive/2017/08/drop-indexes-fast/

DropIndexes;

SELECT COUNT(*)

FROM dbo.Users

WHERE LastAccessDate >= '2013/11/10'

AND LastAccessDate <= '2013/11/11'

BEGIN TRAN

UPDATE dbo.Users

SET Reputation = Reputation + 100

WHERE LastAccessDate >= '2013/11/10'

AND LastAccessDate <= '2013/11/11'

ROLLBACK

CREATE INDEX

IX_LastAccessDate_Id

ON dbo.Users(LastAccessDate, Id)

BEGIN TRAN

UPDATE dbo.Users

SET Reputation = Reputation + 100

WHERE LastAccessDate >= '2013/11/10'

AND LastAccessDate <= '2013/11/11'

/* Come back after showing selects */

UPDATE dbo.Users

SET Reputation = Reputation + 100

WHERE LastAccessDate >= '2014/11/10'

AND LastAccessDate <= '2014/11/11'

/* Go query Brent */

UPDATE dbo.Users

SET Reputation = Reputation + 100

WHERE LastAccessDate >= '2015/11/10'

AND LastAccessDate <= '2015/11/11'

Blocking Demo – Right Window

Here we go:

/*

Blocking, Locking, and Deadlocking - SELECT Window

Brent Ozar, 2018/02/17

Download this script, the database, and watch the session video:
https://www.brentozar.com/go/locking

MIT License

Copyright (c) 2018 Brent Ozar Unlimited

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.
*/


/* SELECT WINDOW */


SELECT *
FROM dbo.Users
WHERE Id = 26837
GO





SELECT Id
FROM dbo.Users
WHERE LastAccessDate >= '1800/01/01'
AND LastAccessDate <= '1800/01/02'
GO


/* Now go add the index */

SELECT Id
FROM dbo.Users
WHERE LastAccessDate >= '1800/01/01'
AND LastAccessDate <= '1800/01/02'
GO



/* Let's also do a key lookup for Reputation */
SELECT Id, Reputation
FROM dbo.Users
WHERE LastAccessDate >= '1800/01/01'
AND LastAccessDate <= '1800/01/02'
GO


/* Let's try to hit the same dates we're updating */
SELECT Id, Reputation
FROM dbo.Users
WHERE LastAccessDate >= '2013/11/10'
AND LastAccessDate <= '2013/11/11'
GO


/* Maybe that's because we're changing Reputation.
   What if we select just Location instead, which isn't changing? */
SELECT Id, Location
FROM dbo.Users
WHERE LastAccessDate >= '2013/11/10'
AND LastAccessDate <= '2013/11/11'
GO


/* How could we fix that? */


/* Go update more rows, then get Brent again */
SELECT *
FROM dbo.Users
WHERE Id = 26837
GO




/* Go update more rows, then get Brent again */
SELECT *
FROM dbo.Users
WHERE Id = 26837
GO

100

101

102

103

104

Blocking, Locking, and Deadlocking - SELECT Window

Brent Ozar, 2018/02/17

Download this script, the database, and watch the session video:

https://www.brentozar.com/go/locking

MIT License

Permission is hereby granted, free of charge, to any person obtaining a copy

of this software and associated documentation files (the "Software"), to deal

in the Software without restriction, including without limitation the rights

to use, copy, modify, merge, publish, distribute, sublicense, and/or sell

copies of the Software, and to permit persons to whom the Software is

furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all

copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR

IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,

FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE

AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER

LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,

OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE

SOFTWARE.

/* SELECT WINDOW */

SELECT *

FROM dbo.Users

WHERE Id = 26837

SELECT Id

FROM dbo.Users

WHERE LastAccessDate >= '1800/01/01'

AND LastAccessDate <= '1800/01/02'

/* Now go add the index */

SELECT Id

FROM dbo.Users

WHERE LastAccessDate >= '1800/01/01'

AND LastAccessDate <= '1800/01/02'

/* Let's also do a key lookup for Reputation */

SELECT Id, Reputation

FROM dbo.Users

WHERE LastAccessDate >= '1800/01/01'

AND LastAccessDate <= '1800/01/02'

/* Let's try to hit the same dates we're updating */

SELECT Id, Reputation

FROM dbo.Users

WHERE LastAccessDate >= '2013/11/10'

AND LastAccessDate <= '2013/11/11'

/* Maybe that's because we're changing Reputation.

What if we select just Location instead, which isn't changing? */

SELECT Id, Location

FROM dbo.Users

WHERE LastAccessDate >= '2013/11/10'

AND LastAccessDate <= '2013/11/11'

/* How could we fix that? */

/* Go update more rows, then get Brent again */

SELECT *

FROM dbo.Users

WHERE Id = 26837

/* Go update more rows, then get Brent again */

SELECT *

FROM dbo.Users

WHERE Id = 26837

Deadlocking Demo – Left Window

/* DEADLOCKING: Left Window */

/* Set up the demo data */
CREATE TABLE dbo.Ingredients 
  (IngredientName VARCHAR(50) PRIMARY KEY CLUSTERED,
   StockQuantity INT);
INSERT INTO dbo.Ingredients
  VALUES ('Peanut Butter', 1),
         ('Jelly', 1);
GO

/* Start making a sandwich */
BEGIN TRAN
UPDATE dbo.Ingredients
  SET StockQuantity = 0
  WHERE IngredientName = 'Peanut Butter';
GO


/* Go over to the right window,
   and make another sandwich over there. */


/* Now try to get the jelly to finish your sandwich. */
UPDATE dbo.Ingredients
  SET StockQuantity = 0
  WHERE IngredientName = 'Jelly';
GO

/* DEADLOCKING: Left Window */

/* Set up the demo data */

CREATE TABLE dbo.Ingredients

(IngredientName VARCHAR(50) PRIMARY KEY CLUSTERED,

StockQuantity INT);

INSERT INTO dbo.Ingredients

VALUES ('Peanut Butter', 1),

('Jelly', 1);

/* Start making a sandwich */

BEGIN TRAN

UPDATE dbo.Ingredients

SET StockQuantity = 0

WHERE IngredientName = 'Peanut Butter';

/* Go over to the right window,

and make another sandwich over there. */

/* Now try to get the jelly to finish your sandwich. */

UPDATE dbo.Ingredients

SET StockQuantity = 0

WHERE IngredientName = 'Jelly';

Deadlocking Demo – Right Window

/* DEADLOCKING: Right Window */

/* Start making a sandwich,
   but start by grabbing the jelly first. */
BEGIN TRAN
UPDATE dbo.Ingredients
  SET StockQuantity = 0
  WHERE IngredientName = 'Jelly';
GO





/* Now try to grab the other ingredient. */
UPDATE dbo.Ingredients
  SET StockQuantity = 0
  WHERE IngredientName = 'Peanut Butter';
GO



/* In another window, go see why we're not making progress. */

/* DEADLOCKING: Right Window */

/* Start making a sandwich,

but start by grabbing the jelly first. */

BEGIN TRAN

UPDATE dbo.Ingredients

SET StockQuantity = 0

WHERE IngredientName = 'Jelly';

/* Now try to grab the other ingredient. */

UPDATE dbo.Ingredients

SET StockQuantity = 0

WHERE IngredientName = 'Peanut Butter';

/* In another window, go see why we're not making progress. */

Wanna learn more? We’ve got a whole bunch of locking, blocking, and deadlocking resources.