A blocking SQL command prevents other reading or writing operations while it is being executed.
Questions tagged [blocking]
205 questions
45
votes
1 answer
Why does CREATE INDEX ... WITH ONLINE=ON block access to the table over a period of minutes?
I have an existing table:
CREATE TABLE dbo.ProofDetails
(
ProofDetailsID int NOT NULL
CONSTRAINT PK_ProofDetails
PRIMARY KEY CLUSTERED IDENTITY(1,1)
, ProofID int NULL
, IDShownToUser int NULL
, UserViewedDetails…
Hannah Vernon
- 70,928
- 22
- 177
- 323
33
votes
2 answers
Database "frozen" on ALTER TABLE
Our production environment just froze* this morning for a while when altering a table, adding a column actually.
Offending SQL:ALTER TABLE cliente ADD COLUMN topicos character varying(20)[];
* Login into our system requires a select from that very…
Gonzalo Vasquez
- 1,059
- 2
- 18
- 33
29
votes
2 answers
Empty blocking process in blocked process report
I'm collecting blocked process reports using Extended Events, and for some reason in some reports the blocking-process node is empty. This is the full xml:
Tom V
- 15,752
- 7
- 66
- 87
21
votes
6 answers
In Sql Server, is there a way to check if a selected group of rows are locked or not?
We are attempting to update/delete a large number of records in a multi-billion row table. Since this is a popular table, there is a lot of activity in different sections of this table. Any large update/delete activity is being blocked for extended…
ToC
- 727
- 1
- 8
- 20
18
votes
2 answers
How to find the query that is still holding a lock?
Querying the sys.dm_tran_locks DMV shows us which sessions (SPIDs) are holding locks on resources like table, page and row.
For each lock acquired, is there any way to determine which SQL statement (delete, insert, update or select) caused that…
tanitelle
- 447
- 1
- 3
- 9
16
votes
1 answer
Why does UPDLOCK cause SELECTs to hang (lock)?
I've got a select in SQL SERVER that locks the whole table.
Here's the setup script (make sure you don't overwrite anything)
USE [master]
GO
IF EXISTS(SELECT 1 FROM sys.databases d WHERE d.name = 'LockingTestDB')
DROP DATABASE…
marius-O
- 263
- 1
- 2
- 4
15
votes
1 answer
Excessive compile blocking on sp_procedure_params_90_rowset
A resurgence of this questionon MSDN: Blocked-process-report: what is this waitresource "OBJECT: 32767:124607697:0 [COMPILE]"
I have caught these statements in Profiler. They all have durations over 3 seconds. Some over 10+. The blocking activity is…
dan holmes
- 301
- 1
- 5
10
votes
3 answers
SSRS Blocking Processes
We have a busy SSRS Reporting server (SQL Server 2008 R2) on which we see frequent blocking between the below processes:
[dbo].[CheckSessionLock]
[dbo].[WriteLockSession]
We are aware that some reports are running unacceptably slow. What I need…
Yasin
- 193
- 2
- 2
- 9
10
votes
1 answer
When does SQL Server acquire locks?
The list of isolation levels in SQL Server found here states that write locks acquired within a transaction are retained until the end of the transaction. However it doesn't mention anything about when these locks are acquired.
Are locks by default…
Levi Botelho
- 375
- 2
- 11
10
votes
0 answers
Has SQL Server not Killed a Deadlock?
I have encountered a situation where a user has run something in one of our applications which runs a stored procedure. The stored procedure does a few bits of logging (INSERTing to a log table) and then performs a simple delete in the format
DELETE…
SE1986
- 2,142
- 4
- 30
- 61
9
votes
2 answers
Blocking caused by Query Store. Unable to clear or disable
I recently updated our 2016 SQL Server to SP2 and the most recent CU (KB4458621) released in August 2018. Just in the last day or so, I noticed that I have some blocking going on. I can't kill the SPID b/c it isn't a user process. According to…
Nate
- 91
- 4
9
votes
1 answer
How can I view how long a query has spent waiting for locks, without a profiler?
I'm trying to diagnose a query that is intermittently taking a long time. I suspect it may be blocked trying to acquire a lock. I don't have permissions to use a profiler in the environment experiencing the issue.
Is there any way for me to obtain…
Collin Dauphinee
- 365
- 2
- 10
8
votes
2 answers
Blocking between two MERGE queries inserting into the same table
Scenario
I have a large table partitioned on an INT column. When I run two different MERGE statements on two different partitions of this table, they seem to be blocking each other.
Sample code to recreate the scenario:
1. Preparation. Create tables…
ToC
- 727
- 1
- 8
- 20
7
votes
1 answer
Why does updating a non-key column in the Parent Table take a lock on the Child Table
Given the following database with two tables having foreign key relationship between them:
CREATE DATABASE FKLocksTest
GO
ALTER DATABASE FKLocksTest SET READ_COMMITTED_SNAPSHOT OFF
USE FkLocksTest
GO
CREATE TABLE dbo.Department
(
DeptID INT…
SE1986
- 2,142
- 4
- 30
- 61
7
votes
1 answer
How does SQL Server update lock work?
I'm learning SQL Server and trying to understand how SQL Server updates a row.
As I understand, first SQL Server puts an intent exclusive lock on the database, then an intent exclusive lock on the table, and then an update lock on the record to be…
Fajela Tajkiya
- 1,239
- 9
- 23