Questions tagged [blocking]
A blocking SQL command prevents other reading or writing operations while it is being executed.
201 questions
2
votes
1
answer
104
views
Is there any information in Query Store that can be used to find block leaders?
I am on a server with major blocking issues. Not deadlocks, just plain blocks. We know that it is the server's primary bottleneck. However, they do not know what the block leaders are, so I do not ...
- 823
2
votes
2
answers
72
views
Why does a create trigger (that already exists) block all processes in SQL Server 2016 SP3?
I have a situation where the following DDL Statement is rising to the top of my list of processes which I have ordered by the number of processes that each process is blocking (directly or indirectly)....
- 151
1
vote
0
answers
57
views
PostgreSQL locking problem
TL;DR
We have a postgres stored procedure running as a job, which calls another stored procedure, which calls yet another stored procedure, and this internal stored procedure acquires ROW EXCLUSIVE ...
- 163
8
votes
2
answers
557
views
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 ...
- 707
4
votes
2
answers
468
views
Best Way to Determine if a Table has a Long Running Transaction
I need to be able to iterate all tables and issue a TRUNCATE command, however, I don't want the loop to be held up by blocking. Essentially, I am looking for the best way to determine if a SCH-M lock ...
- 629
0
votes
1
answer
122
views
TRUNCATE WITH PARTITION Constantly Fails on 1 Out of 620 Tables
There is a table that seems to have constant updates. There is also a stored procedure that runs periodically and iterates through tables and attempts to truncate a partition of data using the command ...
- 629
0
votes
1
answer
235
views
Postgres VIEW locking: CREATE OR REPLACE vs. DROP and CREATE
My question relates to a production incident in which it was necessary to replace a poorly performing Postgres VIEW under load. We tried numerous times to redefine the VIEW using CREATE OR REPLACE; ...
- 103
3
votes
0
answers
68
views
request_status is Grant and Convert on the same resource_type at the same time
I have a typical blocking scenario due to lock contention.
Lead blocker has these locks on an object.
<Object name="Table1" schema_name="dbo">
<Locks>
<Lock ...
- 13.6k
2
votes
1
answer
181
views
IndexOptimize Question
I have a quick question: Is it safe to run IndexOptimize for stats update under SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to avoid blocking as below?
USE [DBA]
GO
SET TRANSACTION ISOLATION ...
- 23
1
vote
1
answer
82
views
Job history cleanup query being blocked
I'm running the below query every hour to cleanup job history in the sysjobhistory table for all jobs that execute every 10 seconds. The query aims to delete 100k rows of job history that is older ...
- 79
0
votes
1
answer
66
views
What could create block in that case when 'blocking' procedure/spid not do any select / almost nothing?
I had situation like this today- sorry that this is screenshot but it is easier way to show what i need to understand
As on screen - many processes was blocked by SPID 91
This SPID 91 querry was as ...
- 103
1
vote
1
answer
233
views
Why does VALIDATE CONSTRAINT on PG 14.7 take a ShareLock?
I'm following this answer to add a NOT NULL constraint to an existing DB table with ~100M rows. However, when I try running the backend process takes out a ShareLock.
ALTER TABLE mytable VALIDATE ...
- 305
1
vote
1
answer
984
views
Capturing Blocked Process Report via Extended Events
I ran the following commands to setup extended event captures for blocked processes:
CREATE EVENT SESSION [blocked_process] ON SERVER
ADD EVENT sqlserver.blocked_process_report(
ACTION(sqlserver....
- 163
1
vote
1
answer
332
views
Why does a SELECT query (Sch-S) get blocked due to an existing Sch-S lock held by index reorganize command?
SELECT query requires a SCH-S lock. This can be shared with other read queries.
UPDATE query requires a SCH-M lock. This cannot be shared with other read/update queries.
Link: https://www.mssqltips....
- 3,499
0
votes
0
answers
470
views
Refreshing materialized view blocks an unrelated table
Last few days I was experiencing a problem that seems really strange to me and even though I have some experience with PostgreSQL, I really don't understand what may be the cause.
We have two services ...
1
vote
2
answers
2k
views
How can I insert into a Postgres table with a unique constraint without waiting on concurrent transactions?
I have a table like this in a Postgres database:
CREATE TABLE tabwithunique (
id SERIAL PRIMARY KEY,
key BIGINT NOT NULL UNIQUE,
value BIGINT NOT NULL
);
I want to design a statement that ...
- 121
1
vote
1
answer
415
views
Under what conditions does WITH(rowlock) not work on views?
We have a lock pileup problem on a customer site. By a combination of tools we managed to locate the root locker. It is definitely running this particular SQL statement at the time of the lock pileup:
...
- 426
-1
votes
1
answer
58
views
How to tell Postgresql what tables will be in a transaction?
RDS Postgresql 12.12
CREATE INDEX CONCURRENTLY idx_foo ON foo (f1, f2, f3); is blocked by a stored procedure which only touches tables bar and baz.
BEGIN and START TRANSACTION don't seem to have a ...
- 658
1
vote
1
answer
610
views
What could be causing the delay in synchronous replication between the master and slave nodes?
I have 2 PostgreSQL 13 database, one acting as a master and the other as a slave, using streaming replication with synchronous_commit=remote_apply. This means that all data written to the primary ...
- 31
4
votes
2
answers
3k
views
Does a query that is suspended due to an ongoing ASYNC_NETWORK_IO cause blocking?
I am looking at the dm_exec_sessions and requests DMVs on a production server. There are some queries with status 'suspended', a duration of 12 minutes, and a wait type of ASYNC_NETWORK_IO.
I ...
- 3,499
4
votes
1
answer
2k
views
Identifying wait_resource for wait_info Extended Events
Is there a way to identify what type/name of resource that is being waited on?
- 195
-1
votes
1
answer
370
views
Diagnosing Timeouts and Blocks on SQL Server [duplicate]
We found a scenario where a large delete in one table causes hanging across seemingly unrelated transactions and tables in the same the database (but not other databases on the same server). The good ...
- 9
1
vote
0
answers
99
views
MSSQL - Diagnosing Timeouts, Blocks
We found a scenario where a large delete in one table causes hanging across seemingly unrelated transactions and tables in the same the database (but not other databases on the same server). The good ...
- 9
0
votes
0
answers
99
views
When does InnoDB acquire gap locks on the post gap of the selected/updated records?
I am currently learning about Gap Lock in the InnoDB engine, In MySQL doc they say,
InnoDB will acquire a gap locks only on the preceding gap, but on this Percona article as I understood from their ...
7
votes
1
answer
2k
views
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 ...
- 1,219
7
votes
0
answers
232
views
Find a previous statement or held locks in a blocking situation
I'm using XE blocked_process_report to detect and analyse the blocking.
But since it's a point-in-time situation, I only see the blocking leader's statement currently being run and the incompatible ...
- 5,512
0
votes
2
answers
1k
views
Concurrently update counter with least amount of locking
Using Sql Server I need to have concurrent transactions update a counter, when they commit - but they must not block each other from updating the counter.
Let's say the first started transaction takes ...
- 225
2
votes
3
answers
79
views
How do I block all IP addresses from connecting remotely to Cassandra?
I want access to remote cassandra with one ip address. how can I change cassandra.yaml to block all ip address except ip that I want to connect?
Can I do by cassandra.yaml ?
- 41
0
votes
0
answers
189
views
Blocked process while deleting and inserting rows
I've a table which contains 282M records. Here's the structure:
CREATE TABLE [dbo].[EntityHistory](
[EntityHistoryId] [int] IDENTITY(1,1) NOT NULL,
[EntityType] [nvarchar](100) NOT NULL,
[...
- 61
1
vote
1
answer
1k
views
single insert ignore on a table with no auto-increment
For this table, in mysql 5.7,
CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL,
`val` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
The table doesn't have ...
- 113
10
votes
0
answers
388
views
Has SQL Server not Killed a Deadlock? [closed]
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 ...
- 2,083
1
vote
1
answer
5k
views
Dropping PostgreSQL partition is blocked as long as a client is reading data from any partition
I have a simple partitioned table in PostgreSQL 14, like this:
create table entity
(
dataset_id integer not null references dataset (id),
...
) partition by list (dataset_id);
create table ...
- 210
1
vote
2
answers
1k
views
SQL Server - Exclusive lock taken by a SELECT statement - Why?
I'm trying to make sense of why SQL Server (2014) is placing an eXclusive key lock during a deadlock scenario. I've pasted the entire deadlock graph below.
I'm confused because the deadlock is ...
- 431
-1
votes
2
answers
626
views
Dangers of running this stored procedure that kill blocking processes
use master
GO
CREATE PROC support_KillBlockingProcesses (@RecursiveCount int = NULL)
AS
DECLARE @count int, @spid int, @sql nvarchar(max)
SET @count = ISNULL(@RecursiveCount, 3)
while @count > 0
...
user253549
1
vote
0
answers
78
views
SQL Server 2008R2 - Cannot View DB Properties in SSMS or Query Database Files
I am trying to view the properties of a database but it fails in SSMS with below error in screenshot.
I searched online for solutions but none have worked. These include change db owner to sa. The ...
- 1,414
0
votes
1
answer
309
views
How does blocking increase sos_scheduler_yield wait?
What is the correlation between blocking and CPU? Because I am reaching the conclusion that the blocking is causing my sos_scheduler_yield wait type to increase, but I would like to understand the ...
- 817
5
votes
1
answer
1k
views
Query that returns no records hangs on lock
We noticed this weird situation, that a query that returns no records, will wait for a lock when WITH(UPDLOCK) is specified.
The query is as simple as
SELECT primaryKey FROM someTable WITH(UPDLOCK)
...
- 153
-1
votes
1
answer
540
views
Best method for isolating long running locks?
On a SaaS production database with 7 users (5 human, 2 backend) many are experiencing SQL cliënt timeouts at 30 seconds, our standard .Net CommandTimeout.
This SQL Server instance (out of our ten) ...
- 59
1
vote
2
answers
2k
views
Lock specific select query until all insert transactions are commited
I have an insert-only table in my database that contains time-series data.
When my system starts, I need to populate an in-memory state with the last-month data (each row contains data for one minute) ...
- 135
0
votes
0
answers
2k
views
What could block a SELECT (read) in postgres
I have a few long running transacrions that both select and update data. When the jobs are running, I suddently cannot run a basic SELECT + JOIN query on the table? It simply hangs forever... I ...
- 1
1
vote
1
answer
430
views
SQL Server and NHibernate - blocking occurs between two unrelated queries (distributed transactions)
there's a problem that I'm seeking your help with...
There are two simple queries:
DELETE a row from a table in a databaseA
SELECT a row from a table in a databaseB using hints with (updlock, rowlock)...
- 111
0
votes
1
answer
1k
views
Create table with foreign keys blocked by other Table-level Lock
I'm not pretty sure it is database question or development-process question, but I will kindly give it a try.
When creating a new table B with foreign keys to table A, the operation tries to acquires ...
- 135
1
vote
1
answer
220
views
How can I use a table as a GUID pool without suffering race conditions?
I am in a position where I will be issued with a list of GUIDs to be assigned by a SQL Server database at runtime.
My immediate idea is to create a table for the GUIDs. When a batch of GUIDs is ...
- 113
3
votes
1
answer
1k
views
MySQL/MariaDB: can a SELECT query block an INSERT query?
Let's suppose that I have a query in the form:
INSERT INTO tableA (...) select ... from tableB;
Now, is it possible that this query, while executing, blocks plain INSERT statements to tableB? ...
- 724
0
votes
1
answer
394
views
problem on understanding locking and covering index
As per my study, I found that a lot of people mention that the covering index would help to reduce the "key lookup" blocking. I was keen to test and understand it, but my testing did not ...
- 143
1
vote
3
answers
625
views
TABLOCK hint for SELECT statements when blocking writers is not an issue
Since SELECT statements take a shared lock in contrast X and U it seems to me that using WITH(TABLOCK) in datawarehouse style queries used for reporting is the correct thing to do in order to avoid ...
- 817
0
votes
1
answer
112
views
How do you handle maintenance window for DDL commands on Primary replicating to readable secondary AGs
We are currently running into this issue for one of our availability group design:-
On Primary :- Nightly Table switch/Purges are happening (DDL) for approx. 2-3 hours
On Secondary which is configured ...
- 794
0
votes
0
answers
201
views
Readers blocking reader on readable secondaries
We recently migrated to AG setup for one of our VLDB's around 20+TB.
Current setup is Multi subnet AG with primary accepting mix workload while heavy reporting offloaded to readable secondary replicas....
- 794
3
votes
2
answers
2k
views
Blocking on readable secondary replica
We recently migrated from LogShipping standby/read-only setup to Multi Subnet AG setup with readable secondaries.
Generally on old setup we have select queries running for longer duration as the ...
- 794
1
vote
1
answer
756
views
Does a lock mean a wait?
I really don't understand promises about waits in case of concurrent data access.
All manuals operate by term lock. No one explains that lock probably causes SQL clients waiting for lock acquisition ...
- 519