Stack Exchange Network

Stack Exchange network consists of 174 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

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

0
votes
1answer
33 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 ...
5
votes
0answers
67 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
34 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
44 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
0answers
24 views

Why would a Session be holding multiple Object locks on one table

There was an issue on one of our servers yesterday that caused long-term blocking. One of the other DBAs identified the blocking session and killed it, and I was not directly involved in the issue. ...
0
votes
2answers
40 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
52 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 ...
0
votes
0answers
52 views

Locking with foreign key references

I have a table table A that is being created as part of a transaction. One of the columns in this table A has a foreign key reference to a column(columns) in table B. Now another transaction is ...
-2
votes
1answer
44 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
58 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
133 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
22 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 ...
1
vote
2answers
241 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
37 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
155 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
28 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
89 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
32 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
275 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
48 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
39 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 ...
0
votes
0answers
44 views

SQL Server Blocking issues

We are facing an issue in our top-up system which is SQL Server blocking resources. The scenario is every API user (third party system) has a single account in our database, when many requests came ...
4
votes
2answers
84 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 ...
10
votes
2answers
617 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
143 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
46 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
72 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
67 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
55 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 ...
0
votes
0answers
31 views

Risk of turning on RSCI on transactional replication subscriber DB

how would turning on RSCI affect(effort to reduce deadlock) the database if it’s the subscriber of a lot of transactional replications from a few publication servers? Any changes or issue would occur ...
0
votes
0answers
120 views

Oracle utl_http.request: Calls to same server blocking other sessions?

We had a job today that hanged on a utl_http.request() call... the server it called seems to have hung while generating the request, but no timeout exception occurred (the current receive timeout is ...
0
votes
0answers
353 views

TempDB Locking issue

Once per week i run the sp_blitz scripts from Brent Ozar to get a quick health check. At the last execution of the script i ran into something very strange. The query was blocked 45 minutes because of ...
2
votes
1answer
92 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
47 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
292 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
282 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
383 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
153 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, ...
1
vote
0answers
65 views

Table/row access blocked/slow until alter trigger [closed]

I have a very weird behavior in my databases. I have around 50 of them, one for each customer. In few cases, I've seen something odd: Some tables are starting to get blocked, locks, slow, no idea ...
5
votes
1answer
233 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
376 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 ...
27
votes
2answers
1k 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
277 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
64 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
248 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
181 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
25 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
604 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
31 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 ...