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
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 ...
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,
...
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 ...
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 ...
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 ...
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 ...
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 "...
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 ...
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 ...
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 ...
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 ...
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,
...
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 ...
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 ...
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 ...
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"...
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 ...
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 ...
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 ...
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 ...
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] = ...
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 ...
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 ...
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 ...
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,
[...
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 ...
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 ...
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....
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...