Questions tagged [blocking]

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

Filter by
Sorted by
Tagged with
0 votes
0 answers
50 views

reorg index causing locks on Background session

After reading many many posts, I gave up and I need to ask the question. We have a index maintenance (yes, I know, people don't like it) every weekend. For some reason I could notice that it started ...
Racer SQL's user avatar
  • 7,394
2 votes
1 answer
80 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 ...
Nauman Aziz's user avatar
1 vote
1 answer
54 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
0 votes
1 answer
56 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 ...
Dorian's user avatar
  • 103
1 vote
1 answer
106 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 ...
Felipe's user avatar
  • 293
1 vote
1 answer
306 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....
Aheho's user avatar
  • 141
0 votes
0 answers
37 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
1 answer
157 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....
variable's user avatar
  • 3,533
0 votes
0 answers
234 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
1 vote
2 answers
879 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 ...
Curtis Fenner's user avatar
1 vote
1 answer
223 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: ...
Joshua's user avatar
  • 426
-1 votes
1 answer
43 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 ...
RonJohn's user avatar
  • 615
1 vote
1 answer
331 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 ...
Rio Fajar's user avatar
4 votes
2 answers
2k 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 ...
variable's user avatar
  • 3,533
4 votes
1 answer
971 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?
JieLong's user avatar
  • 167
-1 votes
1 answer
276 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 ...
breckit's user avatar
1 vote
0 answers
86 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
0 votes
0 answers
72 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
7 votes
1 answer
1k 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 ...
Fajela Tajkiya's user avatar
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,403
0 votes
2 answers
879 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 ...
asgerhallas's user avatar
2 votes
3 answers
65 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 ?
RezaRastgoo's user avatar
0 votes
0 answers
156 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
1 vote
1 answer
814 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 ...
aconrad's user avatar
  • 113
10 votes
0 answers
362 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 ...
SE1986's user avatar
  • 1,950
0 votes
1 answer
4k 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 ...
EM0's user avatar
  • 190
1 vote
2 answers
915 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 ...
FrugalShaun's user avatar
-1 votes
2 answers
502 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 ...
Sayaman's user avatar
  • 201
1 vote
0 answers
65 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
0 votes
1 answer
213 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 ...
xhr489's user avatar
  • 805
5 votes
1 answer
976 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) ...
CSharpie's user avatar
  • 153
-1 votes
1 answer
408 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) ...
Nick's user avatar
  • 59
1 vote
2 answers
1k 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) ...
Sassa's user avatar
  • 135
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
1 vote
1 answer
376 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)...
Timbalero's user avatar
  • 101
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 ...
Cowabunga's user avatar
  • 135
1 vote
1 answer
178 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 ...
Q'''s user avatar
  • 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? ...
Vilx-'s user avatar
  • 724
0 votes
1 answer
203 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 ...
YHTAN's user avatar
  • 143
1 vote
3 answers
476 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 ...
xhr489's user avatar
  • 805
0 votes
1 answer
104 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
171 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
3 votes
2 answers
1k 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 ...
Newbie-DBA's user avatar
1 vote
1 answer
560 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 ...
gavenkoa's user avatar
  • 499
1 vote
2 answers
264 views

Unable to understand correlation between MaxWorkers created and CPU utilization

I am having a strange issue which i am unable to understand: On my server with 64 Logical processors having maxworkers 1472, there are peak periods in day where workers created for an hour lets say ...
Newbie-DBA's user avatar
1 vote
1 answer
484 views

Why is PostgreSQL blocking creation of a new table on update on foreign table?

I understand why an UPDATE query on tableA can block an ALTER TABLE DDL statement on the same tableA. ex: UPDATE tableA SET column1 = 'test' WHERE id = 1;` But I am trying to create a new table, say ...
Surya's user avatar
  • 131
1 vote
0 answers
226 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
0 votes
2 answers
30 views

Open Windows Causing Locks After Sproc has Completed [closed]

I had a query that was being blocked. Ran sp_whoisactive @ get_locks=1 and identified the blocking query and it's owner. The result indicated the table had been locked for over four hours. The ...
Don's user avatar
  • 161
0 votes
1 answer
32 views

Getting All SQL Corresponding to Locks

I was recently involved in debugging a tricky SQL Server locking issue. We eventually figured out what was going on by studying the application code, but it would have been much easier if SQL Server ...
sam.bishop's user avatar
1 vote
1 answer
171 views

Why use an intent lock at a higher level in the hierarchy, why not just a regular lock?

This article suggests intent locks are used at a higher level of the lock hierarchy to indicate that a lock has been taken on a lower level. The idea of this mechanism is that SQL Server doesn't need ...
SE1986's user avatar
  • 1,950