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
2 answers
66 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 ...
user avatar
0 votes
0 answers
31 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 ...
user avatar
  • 55
-1 votes
2 answers
36 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 ...
user avatar
  • 5
1 vote
0 answers
16 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 ...
user avatar
  • 1,344
0 votes
1 answer
62 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 ...
user avatar
  • 587
5 votes
1 answer
194 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) ...
user avatar
  • 153
-1 votes
1 answer
40 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) ...
user avatar
  • 59
1 vote
2 answers
64 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) ...
user avatar
  • 125
0 votes
0 answers
79 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 ...
user avatar
  • 1
1 vote
1 answer
87 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)...
user avatar
0 votes
1 answer
46 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 ...
user avatar
1 vote
1 answer
64 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 ...
user avatar
  • 113
3 votes
1 answer
269 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? ...
user avatar
  • 654
0 votes
1 answer
55 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 ...
user avatar
  • 133
1 vote
3 answers
121 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 ...
user avatar
  • 587
0 votes
1 answer
58 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 ...
user avatar
0 votes
0 answers
61 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....
user avatar
3 votes
2 answers
333 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 ...
user avatar
0 votes
1 answer
65 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 ...
user avatar
  • 429
1 vote
2 answers
56 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 ...
user avatar
1 vote
1 answer
51 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 ...
user avatar
  • 131
0 votes
0 answers
37 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, ...
user avatar
  • 155
0 votes
2 answers
27 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 ...
user avatar
  • 157
0 votes
1 answer
24 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 ...
user avatar
1 vote
1 answer
62 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 ...
user avatar
  • 1,450
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 ...
user avatar
0 votes
1 answer
36 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 ...
user avatar
  • 1,323
1 vote
1 answer
250 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 ...
user avatar
  • 151
0 votes
1 answer
78 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 ...
user avatar
  • 2,127
0 votes
2 answers
82 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 ...
user avatar
  • 1,258
0 votes
1 answer
511 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 ...
user avatar
2 votes
1 answer
133 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 ...
user avatar
0 votes
1 answer
33 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?
user avatar
2 votes
2 answers
562 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='...
user avatar
  • 2,879
1 vote
1 answer
282 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 ...
user avatar
0 votes
2 answers
147 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 ...
user avatar
  • 155
0 votes
2 answers
326 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 ...
user avatar
  • 101
1 vote
2 answers
5k 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 ...
user avatar
  • 111
0 votes
1 answer
65 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 ...
user avatar
  • 407
0 votes
0 answers
83 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 ...
user avatar
  • 24.1k
2 votes
1 answer
225 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="...
user avatar
  • 1,450
0 votes
0 answers
57 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 ...
user avatar
0 votes
0 answers
1k 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 ...
user avatar
  • 101
1 vote
2 answers
131 views

For AlwaysOn AGs, can a secondary replica cause contention on the primary when the resources of the secondary are maxed out?

Is it possible and in what ways can the secondary replica cause contention against the primary replica when the secondary replica's resources are maxed out (e.g. does the primary "lock" up while ...
user avatar
  • 24.1k
0 votes
2 answers
416 views

Can a SELECT statement block an ALTER TABLE?

I see in Postgres docs: Two transactions cannot hold locks of conflicting modes on the same table at the same time During maintenance (schema migration) we saw cases when we can't ALTER TABLE ...
user avatar
  • 429
0 votes
1 answer
84 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 ...
user avatar
  • 69
2 votes
0 answers
129 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 ...
user avatar
0 votes
1 answer
528 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 ...
user avatar
  • 1,285
2 votes
1 answer
341 views

SQL Server Sch-S locks on unrelated tables

I am a bit confused. I myself am MCSE and thought I would understand most parts of the SQL Server. Now I have a stored procedure here, doing inserts and updates on two tables dbo.tabA and dbo.tabB. ...
user avatar
  • 321
1 vote
1 answer
94 views

How to improve simple update statement causing blocking issues, inneficient index scan in query plan

Please bear with me - I am on the development side but I've been tasked with finding the cause of several blocking issues after upgrading from sql server 2008 to 2016. The blocking is intermittent, ...
user avatar