Questions tagged [blocking]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
57 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 ...
3
votes
1answer
85 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? ...
0
votes
1answer
40 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 ...
1
vote
3answers
73 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 ...
0
votes
1answer
52 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
0answers
50 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
2answers
175 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
1answer
38 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 ...
1
vote
2answers
48 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
1answer
35 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 ...
0
votes
0answers
35 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, ...
0
votes
2answers
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 ...
0
votes
1answer
23 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
1answer
31 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
vote
0answers
38 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
1answer
23 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
vote
1answer
218 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 ...
0
votes
1answer
54 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 ...
0
votes
2answers
68 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 ...
0
votes
1answer
296 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
1answer
101 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
1answer
26 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
2answers
429 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='...
1
vote
1answer
196 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
2answers
95 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 ...
0
votes
2answers
228 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 ...
1
vote
2answers
4k 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 ...
0
votes
1answer
48 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
0answers
77 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 ...
2
votes
1answer
183 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="...
0
votes
0answers
41 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
0answers
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 ...
1
vote
2answers
114 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 ...
0
votes
2answers
252 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 ...
0
votes
1answer
67 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 ...
2
votes
0answers
101 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 ...
0
votes
1answer
437 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 ...
2
votes
1answer
299 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. ...
1
vote
1answer
88 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, ...
0
votes
2answers
2k views

Are race conditions possible in SQL? [closed]

I would like to understand what the SQL standard says about parallel queries and race conditions. If there is no standard, then I would be interested in the approach adopted by popular databases. I ...
0
votes
1answer
165 views

Blocked Process Report

I have tons of BPR in .xel file (XE) and I want to be able to view them in a graph form and dump them all in SQL Server table, is there any way to do so? Instead of the 5 sec threshold is there any ...
0
votes
0answers
952 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 ...
2
votes
1answer
858 views

What types of locks are held on global temporary tables?

I am wondering if: the isolation level is Read Committed data is inserted or deleted or read are the same locks (shared for reading and exclusive for insert/delete) used for global temporary table ...
0
votes
1answer
29 views

Disk block per table?

At T1, insert comes for table_1, goes to disk block_1. At T2, insert comes for table_2, can it go to same disk block_1(provided block_1 has space) or new block will be created per table ? At T3, ...
0
votes
1answer
662 views

how to remove/deal with locks in a MariaDB Galera cluster

I have a cluster of 3 nodes running MariaDB Galera cluster, cluster works as expected, I can reboot a single node at a time and in the best case, IST will be used or SST(using rsync) to sync back the ...
0
votes
3answers
2k views

Database Blocking, kill the session which is responsible after 5 minutes

I have been struggling with database blocking at my database server from last so many months. Have tried to use many ways to capture them and also aware that who are leaders of blocking. This ...
3
votes
1answer
1k views

Aggressive Under-Indexing and no data for missing index

I know that there are lot of blocking on my database and have tried my best to get this sorted by vendor as this application is supported by them and hasn't produced any successful result yet. Every ...
6
votes
1answer
646 views

Strange blocking on readable secondary after reboot

We are running an SQL Server 2014 Availability Group with 3 replicas, one synchronous (SQL2 for this matter) and one asynchronous secondary replica. We also configured read-only routing to the ...
3
votes
1answer
348 views

Causes for pg_class locks except DROP TABLE

I do experience the following situation on a PostgreSQL 11: A transaction with unknown statements creates an ACCESS SHARE lock on pg_class. Another connection wants to execute DROP TABLE which causes ...
0
votes
1answer
2k views

Oracle Database troubleshooting enq: TX - row lock contention

AWR Reports of a 12.1.0.2 EE Oracle Database shows enq: TX - row lock contention with up to 80% of DB Time. The application is huge with more than 500 tables and 100.000 registered users, on average ...