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

learn more… | top users | synonyms

0
votes
0answers
27 views

Update on a table on MySQL Innodb seems to hang / lock forerever

When performing an update on a MySQL database for an InnoDB table, the update sentence never finishes. This is the update sentence from the MySQL query log: UPDATE `bb_plan` SET `creation_date` = '...
0
votes
0answers
19 views

Schema Locks blocking during Alter Table Add Constraint?

I am dealing with a customer using MS SQL Server with Entity Framework to generate their database upgrades. EF generates a "Migration Script", which in this case is a fairly straightforward collection ...
3
votes
1answer
67 views

Diagnosing a block in a process that EXECs several layers of stored procedures

Disclaimer: I am a developer. Please be nice to me. I am not the developer that is responsible for what follows. I try to be one of the good ones. I have inherited a support ticket that involves a ...
2
votes
2answers
127 views

Blocking continues after enabling snapshot isolation

On database hosted on a SQL Server 2012 instance, I have enabled ALLOW_SNAPSHOT_ISOLATION and verified the state as ON using SELECT snapshot_isolation_state_desc,name from sys.databases However, in ...
3
votes
1answer
51 views

Unknown System SPID blocking user SPIDs

We are performing a migration of 3rd party data and it is hanging. When we investigate we see SPID 38 (a system/background SPID) is the blocking user SPIDs trying to do the migration. Unfortunately, ...
2
votes
1answer
71 views

Hold exclusive lock on a whole DB

As I explained in Set a filegroup to readonly, I wanna set some tables readonly, to have better performance. These tables are only written by an ETL that runs daily, then they are only read. ...
2
votes
2answers
86 views

Database “frozen” on ALTER TABLE

Our production environment just froze* this morning for a while when altering a table, adding a column actually. Offending SQL:ALTER TABLE cliente ADD COLUMN topicos character varying(20)[]; * ...
1
vote
2answers
63 views

SOS_SCHEDULER_YIELD - Prevent a Proc from giving up the Processor Time

I've got a process which about once a month at times unrelated (or as far as we can tell) goes into SOS_SCHEDULER_YIELD State. This process blocks our main data entry pipeline and brings the entire ...
1
vote
1answer
11 views

What happens during RCSI while tempdb is being populated?

Our database/application utilizes Read Committed Snapshot Isolation so that readers are not blocked by writers, and writers are not blocked by readers. Of course that means when a row/page/table lock ...
0
votes
1answer
33 views

Multiple Replication Type Publications on Single DB

I have 2x critical DB's which until recently; I was reporting on as well as using in production. DB1 is a Vendor DB and DB2 an in-house DB used in conjunction with DB1 for custom tables and views (as ...
3
votes
1answer
76 views

Unpredicatable single insert performance on SQL Server table

I have inherited a system that sometimes exhibits extremely poor single record insert performance. The table is something like 8 million rows, so is relatively large but not exceptionally so. The ...
4
votes
1answer
77 views

Blocking Extended events

I have very basic table CREATE TABLE [dbo].[Table_1]( [Test] [int] NULL) INSERT INTO [dbo].[Table_1]([Test]) Select 1 sp_configure'block process threshold', 1 --> 1 sec go To create ...
6
votes
1answer
125 views

How to solve blocking on tempdb

I am having tempdb contention (I guess, at least) on one of the servers (SQL Server 2008 R2). Both the blocker and the waiters (10 of them) has PAGELATCH_EX wait type on database tempdb and all ...
1
vote
0answers
62 views

How to avoid Head Blocker query doing nothing?

We are experiencing periodic slowdowns of our SQL Server 2014. Today I identified what appears to be the cause, in the SQL Activity Monitor. Process 61 has: Task State - blank Command - blank Wait ...
0
votes
1answer
197 views

Query hangs in preparing state and blocks others in waiting for table flush

In a webapp I'm working on, there's an UPDATE that's slowing / blocking out several other queries: UPDATE products SET visible = 1 WHERE merchant_id = in_merchant_id AND source_code IN ( ...
1
vote
1answer
36 views

Lock priority for scripts run in maintenance windows

We run maintenance windows weekly and monthly. Some of our maintenance windows are online and the last thing we want is our scripts to block live queries from our applications. Is there a way to set ...
2
votes
1answer
25 views

Refreshing a table using CREATE TABLE, DROP and Rename

A read-only database (database1) is updated via log shipping every minute, then a procedure queries multiple tables in it and refreshes a large table on database2. For this process I run an "...
3
votes
1answer
102 views

PAGELATCH_EX occures in update query with UPDLOCK, READPAST

We have a queue organized with a table on MS SQL Server 2014: CREATE TABLE [queue].[Messages]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [Status] [tinyint] NOT NULL, [Data] [nvarchar](max) ...
7
votes
1answer
276 views

How can I view how long a query has spent waiting for locks, without a profiler?

I'm trying to diagnose a query that is intermittently taking a long time. I suspect it may be blocked trying to acquire a lock. I don't have permissions to use a profiler in the environment ...
2
votes
0answers
145 views

SELECT blocking SELECT?

I have huge outputs from pg_stat_activity and the result from the second "combination of blocked and blocking activity" report from https://wiki.postgresql.org/wiki/Lock_Monitoring (2nd version), with ...
2
votes
2answers
64 views

What could be preventing updates and inserts to a table without anything visibly blocking the process?

The following has happened on more than one occasion. Updates or inserts to a certain table (I'm not 100% sure but there seem to be a couple of tables it always happens to) fail due to a timeout. If ...
7
votes
1answer
539 views

How do I prevent a massive SELECT from blocking other statements?

Our SQL Azure database contains a massive SELECT statement that runs something like once per day. The heavy SELECT statement contains no locking hints. Recently we observed some stalls in production ...
0
votes
0answers
38 views

Blocking SPID has Zero Disk IO and Zero CPU Time

Running sp_who2 and following the BlkBy trail up to the root cause of blocking returns a SPID which has CPUTime and DiskIO values of 0; yet it's blocking 4 other SPIDs. I'm confused by how this is ...
1
vote
1answer
230 views

SSIS Blocking itself with IsolationLevel read Uncommited and TransactionOption supported

My Project components have IsolationLevel = ReadUncommitted and TransactionOption = supported. My query is a select on table Y verifying if not exists in table X and the result is written to table X. ...
5
votes
1answer
1k views

Why does UPDLOCK cause SELECTs to hang (lock)?

I've got a select in SQL SERVER that locks the whole table. Here's the setup script (make sure you don't overwrite anything) USE [master] GO IF EXISTS(SELECT 1 FROM sys.databases d WHERE d.name = '...
2
votes
0answers
189 views

SQL Reporting Services (SSRS) - Blocked process on ReportServer, SP CheckSessionLock [duplicate]

We frequently see blocking on our SSRS box which houses both front-end Report Manager ReportingService and the SQL database engine with ReportServer & ReportServerTempDB Catalog databases ...
1
vote
1answer
264 views

How is an idle SQL Server connection causing blocking?

I have a busy, transactional SQL Server (2012) which is having intermittent problems with blocking (every couple of days or so). I've been using extended events to capture blocked-process reports, but ...
3
votes
1answer
217 views

how to get the information about sleeping processes that are causing blocking?

I have a procedure that allows me to see what is currently running in my sql server. This procedure does not use sys.sysprocess because it has been deprecated. However, I am struggling to find ...
2
votes
2answers
285 views

how to find the T-SQL of a sleeping session that is blocking some other process?

I have a stored procedure called sp_radhe that I put on my servers and it has been helping me to "see" what is happening internally. here is the code of this stored procedure: USE [master] GO SET ...
1
vote
2answers
89 views

Spotlight for SQL Server LockType explanation required

I have just started using Spotlight as I'm a software developer, but I am forced to identify database issues causing blocking (Spotlight shows me AWAITING_COMMAND at the points and all other processes ...
5
votes
2answers
192 views

Blocked Process Report -two updates causing a long block

I have a .NET application which is getting SQL update timeouts and I believe that some sort of blocking/locking is taking place which is causing this. I have ran a blocking process report on the ...
2
votes
2answers
276 views

Blocked Process Report SQL Server 31 hour duration

I have an application (.NET EF 6 ) which is getting timeouts when submitting/reading data from the database. Whilst trying to diagnose the problem I used Blocked Process Reports from SQL server ...
2
votes
0answers
29 views

Locks while stream reading and deleting/inserting involving the same tables?

I have a question regarding a potential block/lock situation: I am reading data using a select command, which is then modified externally and uploaded to a temporary table. A stored procedure will ...
2
votes
2answers
195 views

'sp_releaseschemalock' causing me a blocking issue?

why 'sp_releaseschemalock' causing me a blocking issue? what proccess that must execute sp_releaseschemalock or what function of sp_releaseschemalock? I have searched various forums or blogs about ...
18
votes
5answers
2k views

In Sql Server, is there a way to check if a selected group of rows are locked or not?

We are attempting to update/delete a large number of records in a multi-billion row table. Since this is a popular table, there is a lot of activity in different sections of this table. Any large ...
0
votes
1answer
84 views

SQL Blocking causing timeouts

We have a long running transaction (with nested trans), it runs for about 2min. In this time it does a heap of insert, update and selects. At the same time users still need to be able to use the ...
4
votes
2answers
2k views

SSRS Blocking Processes

We have a busy SSRS Reporting server (SQL Server 2008 R2) on which we see frequent blocking between the below processes: [dbo].[CheckSessionLock] [dbo].[WriteLockSession] We are aware that some ...
0
votes
0answers
283 views

Postgresql: Update or insert stuck

I'm running update-or-insert query on a table of approximatelly 3mln rows, 4 columns. Query is part of every day process, and for same days, the query stucks forever. It's just blocked. It's ...
1
vote
1answer
700 views

SQL Server 2012 merge replication blocks other insert/updates during sync

I have a merge replication setup between two SQL Server 2012 databases via Pull method. There are several merge replications affecting different table sets (3 sets 2-5 tables each, actually). There ...
1
vote
0answers
84 views

Trying to add a NULLable column to a table with over 90 million rows takes a very long time

I have been trying to add a NULLable column to a table with over 90 million rows. ALTER TABLE <TableName> ADD <FieldName> INT NULL; It is taking a very long time (been running for over ...
2
votes
3answers
130 views

Why is sql server waiting for acknowledgment?

I have an application that often request a big set of rows [about 20K] from a sql view, in the mean time other applications can't update the table from where the data comes from, the update query's ...
1
vote
0answers
29 views

CLR UDT SCH-M blocking

I’m hoping someone can provide some guidance on a problem I’m having with schema locking on C# CLR UDT’s. I’ve been on the Internet for an extended period of time researching my problem without ...
0
votes
0answers
114 views

Sleeping queries blocking rebuild index and update statistics job in Always on

At one of your client sides we have configured Always on with synchronous mode.Also we have schedule rebuild index and update statistics job which runs in night every alternate day. the issue is ...
2
votes
1answer
428 views

What happens if two mySQL queries both try to lock the same row at the same time?

So this isn't exactly my strong suit, but here's my basic understanding of the situation: If two threads of the same application both try to execute the same query at the same time, mySQL will ...
7
votes
1answer
210 views

When does SQL Server acquire locks?

The list of isolation levels in SQL Server found here states that write locks acquired within a transaction are retained until the end of the transaction. However it doesn't mention anything about ...
1
vote
1answer
132 views

My connection to a Mysql DB has been blocked after spamming queries, for how long?

I am creating a web application that depends on querying a legacy DB without an API. It's MySql. Off course the lack of an API eventually got me in trouble. A feedback loop while testing the app ...
1
vote
0answers
419 views

Renaming a column with sp_rename hangs SQL Server 2012

I was attempting to remove a column from a table within SQL Server 2012 for one of our developers. As a precaution, before dropping the column, we rename them to make sure that everything continues ...
0
votes
1answer
215 views

How to determine which statement is blocking an update in SQL Server

We have an UPDATE statement running in a large J2EE application that is getting blocked by some other statement. The update is using the primary key, so it should affect only one row. The isolation ...
2
votes
1answer
126 views

Resource cost of a blocked query

I'm currently reviewing a case where an SSRS server is experiencing frequent blocking and deadlocking. The reports are not business-critical, and they are frequently killed to alleviate the blocking. ...
0
votes
0answers
725 views

LCK_M_SCH_M Blocking

I have 2 stored procedures (one vendor, one third party) that are constantly (monthly) stepping on each other. They both (for some reason) acquire a schema lock when all they are doing is updating ...