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
1answer
57 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 ...
0
votes
2answers
59 views

How to find the blocking duration in sql server?

We know that there is a "blocked_process_report" event, which will be fired once the blocked session is blocked longer than a threshold defined by sp_configure 'blocked process threshold' However, ...
2
votes
1answer
82 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
81 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
36 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
30 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
104 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
76 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
59 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
173 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
722 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
242 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
123 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
653 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
323 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
66 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
67 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
51 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
46 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
500 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
96 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
222 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
240 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
213 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
228 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
64 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
1k 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
41 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
64 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
622 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
130 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
70 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
27 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
117 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
245 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 ...
15
votes
2answers
7k 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
218 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
188 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
227 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
133 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
67 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
584 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
521 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
682 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
351 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, ...