Questions tagged [blocking]

A blocking SQL command prevents other reading or writing operations while it is being executed.

36 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
7 votes
0 answers
197 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 ...
Zikato's user avatar
  • 4,467
3 votes
0 answers
1k views

SELECT query blocking writes in postgresql

We have a web application that sends emails with a queue worker. Before an email is sent we write to a table: INSERT INTO user_email (user_id, email_key, sent_at) VALUES ( 175413579283955991, ...
Michael Mattax's user avatar
3 votes
0 answers
2k views

SELECT blocking SELECT?

I have huge outputs from pg_stat_activity and the result from the second "combination of blocked and blocking activity" report from https://wiki.postgresql.org/wiki/Lock_Monitoring (2nd version), with ...
Ivan Voras's user avatar
2 votes
0 answers
304 views

Same temp table has created by different session and occurs blocking issue. how to avoid it?

It's a SQL Server 2012 EE. One session (SID=90) executed a JOIN query, and it's generated a temp table internally by SQL Server. But another sessions (SID=103, 109, 110,116) from totally different ...
jelongpark's user avatar
2 votes
0 answers
63 views

ERP System Blocking Transaction - Not Sure If There Is A Fix

I am experiencing a problem with our ERP system that is causing my company a huge headache and I am not sure if there is anything that I can do to try to resolve. Essentially, when someone at our firm ...
Daniel Paduck's user avatar
2 votes
0 answers
105 views

Need Clarification of SQL Server Blocking-Process lastbatchstarted

My understanding is that a SQL Server batch consists of 1 or more statements that will be executed sequentially. The last batch time should be the time when the batch was started. Also, multiple ...
Alf47's user avatar
  • 961
2 votes
1 answer
46 views

Refreshing a table using CREATE TABLE, DROP and Rename

A read-only database (database1) is updated via log shipping every minute, then a procedure queries multiple tables in it and refreshes a large table on database2. For this process I run an "...
Ricardo's user avatar
  • 21
2 votes
0 answers
37 views

Locks while stream reading and deleting/inserting involving the same tables?

I have a question regarding a potential block/lock situation: I am reading data using a select command, which is then modified externally and uploaded to a temporary table. A stored procedure will ...
Malmodir's user avatar
1 vote
1 answer
55 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 ...
Marcus's user avatar
  • 59
1 vote
0 answers
89 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 ...
breckit's user avatar
1 vote
0 answers
67 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 ...
K09's user avatar
  • 1,394
1 vote
0 answers
231 views

Concurrent UPDATE statements of single row in small table takes minutes

I have a very simple table with no indexes (although scan_uuid could have a UNIQUE index if that helps): create table public.host_scan_complete_log_counts ( scan_uuid uuid not null, ...
deed02392's user avatar
  • 165
1 vote
2 answers
146 views

Where is the documentation for SQL Server's blocked process report?

I'm trying to work with the blocked process reports captured using Extended Events and, while I can find numerous blog articles and Q&A about specific details (e.g.1 2 3), I can't find any ...
Rory's user avatar
  • 1,300
1 vote
0 answers
89 views

Long INSERT on large table blocks INSERT on other tables

Server is MySQL 5.7 and the tables are InnoDB. The large table consists of 20+ GB of data. The large table has several indexes on it and also full_text_indexes! Use-Case explained: There are ...
powtac's user avatar
  • 161
1 vote
0 answers
396 views

Insert statement on one table blocking delete on another unrelated table on sql server

I performed a load testing on our environment,where for eg. stored procedure say "sp" was called multiple times to process on different set of ids. During load testing this store procedure ...
ROHIT IHARE's user avatar
1 vote
0 answers
429 views

"Read by other session" wait event occurring in a particular Data Block of a Table

I'm using Oracle 10.2.0.4 with Oracle EBS R12 Application on it. Recently we found sudden slowness in running certain Standard Concurrent Programs. When we check from the Snap_id, we found "User I/O"...
ARGStackOvaFlo's user avatar
1 vote
0 answers
118 views

SQL Query that Blocks itself in Adventure Works

I know it can be common for a query to block itself, especially if the query is from a report or SSIS package. What I am trying to do is create such a query against the adventureworks database for ...
Andy E's user avatar
  • 11
1 vote
0 answers
54 views

MSSQL Deadlocking preventing DDL statement in different DB from running

I had a user doing a massive SQL data compare from server A to server B involving database X. On Server B, I was trying to run a CREATE SYNONYM statement in database Y, which was being entirely ...
ccrook's user avatar
  • 11
1 vote
0 answers
1k views

How to avoid Head Blocker query doing nothing?

We are experiencing periodic slowdowns of our SQL Server 2014. Today I identified what appears to be the cause, in the SQL Activity Monitor. Process 61 has: Task State - blank Command - blank Wait ...
Brendan Hill's user avatar
1 vote
0 answers
2k views

LCK_M_SCH_M Blocking

I have 2 stored procedures (one vendor, one third party) that are constantly (monthly) stepping on each other. They both (for some reason) acquire a schema lock when all they are doing is updating ...
Tom's user avatar
  • 11
1 vote
0 answers
209 views

How can I obtain concurrent deletes for this table?

We have a program that is currently doing something to the effect of: SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRANSACTION UPDATE [Source].[dbo].[SourceTable] SET [ProcessedOn] = ...
Mike Bailey's user avatar
0 votes
0 answers
39 views

Whenever a failover occurs, either manually or automatically Immediately blocking problem occurs

I have a table log_table, and I have two stored procedures: insert_sp for inserting log entries into the table, and update_sp for performing update operations. I have a SQL Server failover cluster ...
HAKAN MAZI's user avatar
0 votes
0 answers
270 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 ...
Paweł Sopel's user avatar
0 votes
0 answers
77 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 ...
Malek Alhourani's user avatar
0 votes
0 answers
164 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, [...
Thomas Ayoub's user avatar
0 votes
0 answers
1k 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 ...
pgnob's user avatar
  • 1
0 votes
1 answer
105 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 ...
Newbie-DBA's user avatar
0 votes
0 answers
176 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....
Newbie-DBA's user avatar
0 votes
1 answer
164 views

SQL SERVER: Header Blocker Across Multiple Databases with wait type CXCONSUMER

We have an instance of SQL Server which has multiple databases. A process in one database seems to be blocking a process in another database. When I look in activity monitor I can see a header blocker ...
SQLMIKE's user avatar
  • 437
0 votes
0 answers
196 views

Why would sys.dm_exec_requests show a query in Database A blocking a query in Database B when neither query references the same tables?

I have a query I use in-house to monitor slow running processes in the data layer. It essentially looks at a few fields from the following system tables and views joined in the following way: SELECT ...
J.D.'s user avatar
  • 37.3k
0 votes
0 answers
165 views

Multiple Writes to Same table in Redshift

I have a table in Redshift. My Application has Multiple Writes/Reads on this Table concurrently. How I do Make sure Reads won't wait for Writes to Commit/RollBack. I wanted to know which Lock Mode I ...
user3865748's user avatar
0 votes
0 answers
3k views

SQL server RUNNABLE process

I've a process that is open since 2 days ago and is executing a select as last command, but this session got stuck in runnable status. I checked inputbuffer several times and everytime it shows the ...
masoud's user avatar
  • 101
0 votes
1 answer
148 views

Blocking process inputbuf is empty

I was trying to capture blocking query using blocked process report past 1 hour as this link https://www.mssqltips.com/sqlservertip/4480/find-blocking-processes-using-sql-server-profiler/ I can get ...
jack's user avatar
  • 69
0 votes
1 answer
752 views

Lock Escalation happening while inserting few million records into a table in production

I want to insert few million records into a table in my production database. I am not updating any existing rows.I am just doing INSERT INTO TAB1 SELECT COL1,COL2,COL3 FROM TAB2 This table has an ...
user9516827's user avatar
  • 1,315
0 votes
0 answers
2k views

Can a SELECT statement block another SELECT on SQL Server?

So the basic question is simple, can a SELECT statement alone block another SELECT statement? My understanding was you needed at least an UPDATE or INSERT for a SELECT to start blocking something ...
Rocket04's user avatar
  • 111
0 votes
1 answer
186 views

How to fix below blocking problem on SQL server 2014

I am having a strange blocking problem which i am not able to understand. During a slighter more volume due to some app pooling issues we see sleeping spids with open transactions, so those SP's tend ...
BeginnerDBA's user avatar
  • 2,169