Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [blocking]

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

5
votes
1answer
158 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
36 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
164 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 ...
3
votes
1answer
92 views

Identify Query Waiting on resources

I have some query with CXCONSUMER wait time when i ran sp_whoisactive as shown in the screenshot below. When i checked the status it is suspended.That means it is waiting for some process to release ...
0
votes
0answers
37 views

Read Deadlock XML - Intra-query Parallelism

I encountered several deadlocks in my application over the last 2 months and i checked the Extended Events to find the details. I tried to understand the dealock XML and i came across this great ...
2
votes
1answer
60 views

Could a busy database affect others on the same server? [closed]

I have a very busy database with lots of reads and writes. I want to create another database on the same server, but I am wondering if that busy database will slow down the performance of the second ...
1
vote
2answers
37 views

InnoDB engine seems lock the table automatically in Mysql5.7

I have a table named Students like: mysql> select * from students; +----+-------+---------+ | id | name | classId | +----+-------+---------+ | 1 | Kris | 7 | | 2 | Tom | 7 | | 3 ...
0
votes
1answer
42 views

Reporting on a SQL DB Server with many write operations and few read operations [closed]

I know this is a generic question, but I have to clarify some basic questions. I know that when a write is happening to a SQL Server database table, a read to that table will have to wait. My question ...
8
votes
1answer
264 views

Blocking caused by Query Store. Unable to clear or disable

I recently updated our 2016 SQL Server to SP2 and the most recent CU (KB4458621) released in August 2018. Just in the last day or so, I noticed that I have some blocking going on. I can't kill the ...
1
vote
1answer
51 views

How to handle process blocking in SQL Server Profiler?

I have run a SQL Profiler Trace which detected a process blocking. Now what should i do, if it always brings the same blocking event, each time duration is getting bigger. I have only one transaction ...
0
votes
1answer
104 views

What blocks a query in Postgres

I have a query running in Postgres 9.3 for more than 24h now (namely an EXPLAIN ANALYZE VERBOSE, linked to another question). I check the "Server status" in pgAdmin which shows the query "Active". I ...
0
votes
2answers
82 views

MySQL insert operation hang while another update operation is running, but can't figure out why

Transaction 1: begin; update t_info set gender=1 where c_score > 85; Transaction 2: begin; insert into t_info(c_id,c_class_no,c_score,c_name,gender) values (4,99,50,'gap',1); Transaction 2 ...
2
votes
1answer
83 views

The effect of a Primary Key on a blocking situation

I'm studying about "Transaction and concurrency","Deadlock" and related stuff for some time. I have a question regarding this matter. The situation is that two records (AccountNumber AW00012597 and ...
-2
votes
1answer
118 views

RID and Key lock on same object by a Select and Delete query

To be specific how did the select query (spid 128, on the left) is able to acquire a S (shared) lock on Index but not on the row? Also, how did Delete query (SPID 121, on the right) is able to get X ...
0
votes
1answer
61 views

Data locking question

I'm learning locking basics and I have some doubts about how it works. I'm using following query to examine transaction locks : SELECT request_session_id AS spid , DB_NAME(resource_database_id) AS ...
2
votes
3answers
470 views

Can a TRUNCATE or DROP table ever be very time consuming?

A couple of months ago, I discovered in a (not so well maintained) database a totally useless table containing 430 million unindexed rows, growing by about 80 000 rows per day. It was a top candidate ...
1
vote
0answers
33 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 ...
3
votes
2answers
1k views

SQL Server 2012 - How to view the locks' log? [closed]

I'm using SQL Server 2012 Express Edition, may I know how can I view all the lock/block processes which were recorded in my database? The reason I want to view the previous locked/blocked processes is ...
0
votes
0answers
48 views

Automating Alerting on Blocking with Queues and Event Notifications [duplicate]

I need to automate email notifications on blocking on some SQL 2016 servers. I have had a look at running a job through the SQL agent but seems hack n slash. I found an alternative method online that ...
3
votes
2answers
405 views

Contention on tempdb due to high concurrency temp table creation from multiple databases?

I have an environment with ~100 databases, all with the same schema. There is a stored procedure on each database that creates a #temp table and drops it, and is running very often (every 30 seconds ...
0
votes
1answer
29 views

Controlled Transaction Blocking - Resume Behavior

When several procedures are blocked on a shared resource, how does sql server pick which one to resume? Example 1 proc sp_a begin tran select 1 from dbo.lock_tbl with ( tbllockx ) delay wait ...
0
votes
1answer
109 views

Database Blocking [closed]

We have been using SQL Server 2014 Enterprise Edition for one of our applications and have been experiencing severe blocking the past few days. This application is supported by a vendor and hence we ...
1
vote
1answer
47 views

Block insertions on a mysql table with specific value

I've got a simple question: I was wondering if it is possible to (temporary or permanent if possible) block the insertion of a record into a table with a specific value for a specific column. I'm ...
-3
votes
1answer
26 views

Will one transaction with million of queries blocking? [closed]

I am having a data migrating task that need to run couple of million UPDATE queries in one single database transaction. Will this causes anything bad for other clients connecting/querying from the ...
4
votes
1answer
1k views

DBA Blocking Query Email Alert

Does anyone have a good Sql Alert which sends email notifications, when query Blocking has occurred over many minutes? I know how to write my own, however seems to be a good code library on internet. ...
1
vote
0answers
80 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 ...
0
votes
0answers
41 views

How can we keep control of what is locked by who?

The script below creates and populates a table for developing and testing purposes: use tempdb go if OBJECT_ID ('tempdb.dbo.person') IS NOT NULL DROP TABLE dbo.person create table dbo.person( id ...
4
votes
2answers
147 views

Framework to effectively identify blocking queries

I need a routine to effectively identify which queries caused blocking. This is related to my previous question How to find the query that is still holding a lock?. I know there is a bunch of ...
12
votes
2answers
3k views

How to find the query that is still holding a lock?

Querying the sys.dm_tran_locks DMV shows us which sessions (SPIDs) are holding locks on resources like table, page and row. For each lock acquired, is there any way to determine which SQL statement (...
2
votes
2answers
181 views

Lots of short term blockings on SQL Server 2008-R2 database

Seeking experts advise on short term blocking which we are seeing for almost a week now. DB is 15 TB and this Table B where we have lots of blocking - almost 300 in no is 2 TB. While using ...
2
votes
0answers
48 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 ...
0
votes
2answers
98 views

Monitoring blocking queries in SQL Server 2012 [closed]

have to monitor blocking queries in SQL Server 2012. How do we figure out which queries are the locked ones? Do I use the Activity Monitor? I want actual SQL statements.
0
votes
1answer
108 views

MySQL table not working after a few days of use

I have the following tables on MySQL server, 5.5.46: CREATE TABLE `locking` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `filename` varchar(255) NOT NULL, `timestamp` timestamp NULL DEFAULT ...
0
votes
0answers
61 views

SELECT blocks INSERT for an unrelated table

We are facing some database (Postgres) issues at the moment and got to a point where the information we have doesn't lead to new insights. Hopefully, somebody can help us out here. Some context The ...
2
votes
1answer
115 views

dm_db_index_operational_stats showing more blocking than extended event report

I’ve implemented the XE to capture blocking on my instance (as per this great article https://www.brentozar.com/archive/2014/03/extended-events-doesnt-hard/#comment-2470072. ) I did this because I ...
2
votes
0answers
58 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 ...
-1
votes
2answers
430 views

Is there any method of looking for Head Blocking history in SQL 2012?

Is there any method of looking for Head Blocking history via DMVs. I have been able to find the subsequent wait details via Extended Events but there doesn't seem to be much information about what ...
2
votes
1answer
422 views

Extended Events to find Blocking and Deadlocking

Came across another great article from Jeremiah Peschka called Finding Blocked Processes and Deadlocks using SQL Server Extended Events and set this up on my local MS SQL 2014 Developer edition and ...
0
votes
1answer
542 views

TSQL - merge partitions online

the short version of my question is: "During the merge of two partitions is the whole partitioned table being blocked. How can I access, for read&write, the part that is not affected by the merge?"...
3
votes
0answers
252 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, ...
5
votes
1answer
300 views

sp_tables blocking my other statements. Who runs it?

In our application we sporadically experience a locking issue caused by the execution of an sp_tables statement. We use SQL Server 2012. In our code and queries in our stored procedures we don't make ...
3
votes
2answers
838 views

SQL Server cannot obtain a LOCK resource at this time

I'm somewhat familiar with blocking processes and locks in SQL Server, however this one has me pretty confused. I understand this error can come from two issues: 1) if SQL Server cannot allocate more ...
28
votes
2answers
2k views

Empty blocking process in blocked process report

I'm collecting blocked process reports using Extended Events, and for some reason in some reports the blocking-process node is empty. This is the full xml: <blocked-process-report monitorLoop="...
2
votes
2answers
386 views

SQL Server blocking report with waitresource Database

We recently had a number of unexpected blocks occur on one of our servers. We captured the blocked process report and they are showing the waitresource as waitresource="DATABASE: 12:0". Below is an ...
1
vote
1answer
66 views

Why do I have RID locks if I have a clustered index?

I have configured extended_events to capture blocks on my database. Now I can see that I had blocks on one table that have been causing blocks problem. I can see that one UPDATE query can not go ...
1
vote
1answer
334 views

I can see a X lock on a RID although the query uses a non-clustered index

My initial issue was two update queries, ran in the same transaction, trying to acquire a X lock on a RID on the same table. The second query wants to update a different record in the table, but it ...
1
vote
0answers
246 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
1answer
26 views

Set user variables from tables being queried in MySQL without locking

I have a data warehouse in AWS Aurora, with a pretty traditional Date Dimension (from a DateSK, gives your RelativeDays, Weeks, Months, etc). First, I have a simple function to convert any date to ...
0
votes
0answers
819 views

LCK_M_S causing blocking with session id < 50

We are experiencing high LCK_M_S wait times conflicting with idle blocking sessions whose ids are less than 50. The waiting session is always a Select statement within a stored procedure. The ...
1
vote
1answer
32 views

Can a killed application process cause blocking of the server?

We use an application made by HPE which blocks completely from time to time. The application comes with Java 6, uses ojdbc6 and connects to Oracle 11. HPE claims that when one of our 6 application ...