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.

0
votes
0answers
7 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
46 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
49 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
24 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
0answers
26 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
1answer
72 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
643 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
176 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
60 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
343 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
164 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
48 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
61 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
41 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
345 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
63 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
136 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
136 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
109 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
155 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
63 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
618 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
37 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
2k 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
53 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 ...
4
votes
2answers
492 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
118 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
50 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
2k 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
93 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 ...
4
votes
2answers
178 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 ...
13
votes
2answers
4k 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
196 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
56 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
120 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
146 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 ...
2
votes
1answer
119 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
59 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
494 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
457 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
597 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
297 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
338 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
978 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
428 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
68 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 ...