Questions tagged [lock-escalation]
25 questions
63
votes
4 answers
What is lock escalation?
I was asked this question at an interview and had no answer. Can anyone here explain?
Kilhoffer
8
votes
2 answers
Blocking between two MERGE queries inserting into the same table
Scenario
I have a large table partitioned on an INT column. When I run two different MERGE statements on two different partitions of this table, they seem to be blocking each other.
Sample code to recreate the scenario:
1. Preparation. Create tables…
ToC
- 727
- 1
- 8
- 20
7
votes
1 answer
Why isn't partition-level lock escalation the default?
In SQL Server, locks are normally escalated from row or page -> table. Starting with SQL Server 2008, a new level of lock escalation was added - partition level.
However, this isn't automatically enabled for partitioned tables - by default, the…
SqlRyan
- 1,206
- 2
- 17
- 24
7
votes
1 answer
How to avoid Table Lock Escalation?
I've got a task to update 5 million rows in a production table, without locking down the whole table for extended time
So, I used approach that helped me before many times - updating top (N) rows at a time with 1-N second interval between…
Aleksey Vitsko
- 6,148
- 5
- 39
- 70
6
votes
4 answers
Does the new locking optimization make batching unnecessary?
Optimized Locking (2025-05-19) makes it sound like batching would be unnecessary if I were to, for example, try to delete 20 thousand rows from a million row table in one statement (DELETE FROM foo WHERE Status = 1). Is that true?
Mark Freeman
- 2,293
- 5
- 32
- 54
5
votes
2 answers
can I resolve a deadlock with the rowlock hint?
I have a large delete stored proc and I've reproduced a deadlock in a situation where the deletes would not have deleted anything.
It looks like the part of the stored proc that hit a deadlock was like this (changed table names):
DELETE d
FROM…
Adam Butler
- 1,531
- 4
- 16
- 16
5
votes
1 answer
What is Postgres lock behaviour when UPDATE WHERE clause does a table scan?
Say you have a large table with tens of millions of rows.
You want to UPDATE large_table SET col=value WHERE col=other_value... but col is not indexed and an EXPLAIN shows that this query will perform a seq scan over the whole table.
What is the…
Anentropic
- 548
- 1
- 5
- 11
5
votes
1 answer
Lock Escalation and Count Discrepancy in lock_acquired Extended Event
I'm trying to understand why there is a discrepancy in lock count in sys.dm_tran_locks and sqlserver.lock_acquired extended event in certain cases. Here is my repro script, I'm using the StackOverflow2013 database on SQL Server 2019 RTM, compat…
Arun Gopinath
- 512
- 1
- 3
- 10
4
votes
1 answer
Indexes with High Lock waits - how to fix
I've been running dbo.sp_BlitzIndex and have 4 somewhat similar indexes on the main table in my database. Each one has a high number of waits and escalation attempts. I'm not sure where these are coming from. I don't have missing indexes. I don't…
MikeC
- 75
- 1
- 5
3
votes
1 answer
Risk of disabling page locking
In another question a user asks how to force row level locking. The answer that he receives tell them how to force row level locking by altering the table and the indexes. The user that answered the question does also mentions that there can be a…
Matthew Morris
- 33
- 1
- 1
- 3
2
votes
1 answer
Does PostgreSQL have lock promotion or escalation?
We having this strange behaviour with ExclusiveLock in one of our database running PostgreSQL 13. I am not able to dig what leads to this lock as lock info is from a monitoring tool.
From what I see from docs ExclusiveLock only acquired by…
goodfella
- 589
- 4
- 14
2
votes
1 answer
When Page Lock happen under Read Committed Isolation level in SQL Server
I have a clustered index table in Azure SQL Database. After checking the dm_db_index_operational_stats, the column page_lock_wait_count greater than 0 means there are some Page Lock happended for this before, but no row_lock_wait_count recorded like…
Jerry.Chen
- 23
- 3
2
votes
2 answers
SQL Server 2008 - get current LOCK_ESCALATION on particular table
Is there a way to find the current setting for LOCK_ESCALATION on particular table? Can it be found through system views?
bojan
- 438
- 6
- 10
2
votes
2 answers
What type of locks does BEGIN TRANSACTION cause against a specific table that's being read from?
In the following example, what kinds of locks are generated from the explicit transaction block that wraps the SELECT INTO query?
BEGIN TRANSACTION T1
SELECT Field1, Field2, Field3
INTO TableB
FROM TableA
WHERE Field3 > Value1
COMMIT TRANSACTION…
J.D.
- 40,776
- 12
- 62
- 141
2
votes
1 answer
Queries from different tables locking each other (LCK_M_X)
Background: Was hired to work with JMeter for performance testing but I'm also getting more and more into SQL performance (because of time, and because I like it). I still suck, and will for a long time. I will try to explain the best I can.
I…
xicotaslb
- 31
- 3