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
12 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 ...
5 votes
1 answer
460 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 ...
5 votes
0 answers
82 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 ...
  • 2,315
0 votes
2 answers
66 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 ...
2 votes
3 answers
33 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 ?
0 votes
0 answers
37 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, [...
1 vote
1 answer
133 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
246 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 ...
  • 1,600
0 votes
1 answer
617 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 ...
  • 177
0 votes
2 answers
270 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 ...
0 votes
0 answers
111 views

Change Tracking autocleanup blocked

I am encountering these errors every 30 minutes. Is there a permanent fix? I referred the link but wanted to make sure we have a trace flag 8293? The sql server version we are using is 2017 and its in ...
  • 55
-1 votes
2 answers
108 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 ...
  • 5
1 vote
0 answers
25 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,354
0 votes
1 answer
84 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 ...
  • 655
5 votes
1 answer
309 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
71 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
354 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) ...
  • 125
0 votes
0 answers
304 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
160 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)...
0 votes
1 answer
202 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 ...
1 vote
1 answer
70 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
437 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? ...
  • 684
0 votes
1 answer
65 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
173 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 ...
  • 655
0 votes
1 answer
61 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
90 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....
3 votes
2 answers
477 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 ...
0 votes
1 answer
112 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 ...
  • 429
1 vote
2 answers
70 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 ...
1 vote
1 answer
137 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 ...
  • 131
0 votes
0 answers
53 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, ...
  • 155
0 votes
2 answers
28 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 ...
  • 157
0 votes
1 answer
25 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 ...
1 vote
1 answer
78 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 ...
  • 1,600
1 vote
0 answers
39 views

select from Dynamic Management View causes blocked processes [duplicate]

we have troubles regarding querying from a dynamic view in SQL Server. We basically check for an existing object lock on a table (CHANGELOG) from sys.dm_tran_locks with the @@spid in a stored ...
0 votes
1 answer
44 views

Narrowing down the blocking session

I am using different queries to find the blocking query. For example, select ec.session_id, er.blocking_session_id, datediff(s, es.last_request_start_time, GETDATE()) as ...
  • 1,343
1 vote
1 answer
290 views

Will Blocking cause timeout issue

When I check in sys.sysprocesses in SQL 2008R2, a number of blocking sessions are there. Most of them are insert, update, and delete statements, with lock-related wait types. Will this cause time out ...
  • 151
0 votes
1 answer
93 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 ...
  • 2,137
0 votes
2 answers
85 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,270
0 votes
1 answer
694 views

Postgres CREATE OR REPLACE VIEW... can it block?

I'm looking for a way for a table to be swapped while it's being used. Say we have a view CREATE OR REPLACE VIEW my_data AS SELECT * FROM my_data_v1; that is being used, e.g. lots of SELECT * FROM ...
2 votes
1 answer
172 views

Why does MYSQL use blocking locks instead of optimistic attempts that fail immediately?

Why isn't optimistic concurrency control more commonly supported at the database layer? It is a valid alternative and can completely do away with any blocking locks. For certain usage patterns, this ...
0 votes
1 answer
37 views

SQL Sever Transactional Replication on DB with deadlocks and blocking issues

Is it feasible to try and start Transactional Replication on a SQL Server DB with deadlocks and blocking issues. Are there any other options for a close to hot backup?
2 votes
2 answers
657 views

Can we reduce row lock contention on an index

We have few queries which check their status into a table every 5 seconds and update their status.. Below is how the query looks like(all table columns are in update query) update table1 set Name='...
  • 2,899
1 vote
1 answer
359 views

Query cycles between suspended and runnable without ever completing

I have an insert statement, extracting data through multiple inner joins from around 8 tables. It is getting into suspended state then runnable and vice versa forever; never coming back to running ...
0 votes
2 answers
220 views

How to connect to specific session id & serial# ? Blocked sessions

I have blocked sessions and see which session is blocking them, having session_id and serial#. Is it possible to connect to such session? How? Background: using V$SESSION_BLOCKERS I see sessions are ...
  • 155
0 votes
2 answers
646 views

What is the difference between lock_timeout and deadlock_timeout?

What is the play between lock_timeout and deadlock_timeout ? What is the reason for a lock_timeout to occur not leading to a deadlock timeout. I am not able to think of scenarios from database ...
  • 101
1 vote
2 answers
6k views

Identifying blocking queries on MySQL 5.5

For an application using MySQL 5.5.60 under Debian, lately we experience transactions with database writes, which do not succeed within the 50s time limit of the innodb_lock_wait_timeout setting. I ...
  • 111
0 votes
1 answer
92 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 ...
  • 417
0 votes
0 answers
103 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 ...
  • 26.7k
2 votes
1 answer
246 views

Blocked Process Report - which process does lockmode apply to?

I am analyzing a blocked process report and my blocking-process XML is as follows (some sections redacted for simplicity) <blocking-process> <process status="running" spid="...
  • 1,600