Questions tagged [blocking]
A blocking SQL command prevents other reading or writing operations while it is being executed.
198
questions
-2
votes
0
answers
27
views
SQL Server: Two MERGE statements are blocking each other on a large partitioned table
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.
Example:
-- Large table ...
- 561
4
votes
2
answers
426
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 ...
- 567
0
votes
1
answer
103
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 ...
- 567
0
votes
1
answer
57
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
57
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.5k
2
votes
1
answer
170
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
65
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 ...
- 69
0
votes
1
answer
63
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
179
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
607
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
0
votes
1
answer
238
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,476
0
votes
0
answers
355
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
1k
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
336
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
50
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 ...
- 647