Hot answers tagged

33 votes
Accepted

Can I rely on reading SQL Server Identity values in order?

When inserting a row, is there a window of opportunity between the generation of a new Identity value and the locking of the corresponding row key in the clustered index, where an external observer ...
Paul White's user avatar
  • 83.7k
26 votes
Accepted

How can I set a column to NOT NULL without locking the table during a table scan?

Another answer from another postgresql contributor. PostgreSQL will not even try to use any indexes during execution of "alter table set not null". It is just not implemented. Proper implementation ...
Melkij's user avatar
  • 2,984
24 votes
Accepted

MySQL: will a transaction lock the row?

A single statement like that works the same with MyISAM or InnoDB, with a transaction or with autocommit=ON. It blocks enough to do the query, thereby blocking the other connection. When finished, ...
Rick James's user avatar
  • 77.6k
18 votes
Accepted

How to find the query that is still holding a lock?

SQL Server doesn't keep a history of the commands that have been executed1,2. You can determine what objects have locks, but you cannot necessarily see what statement caused those locks. For example, ...
Hannah Vernon's user avatar
  • 70k
18 votes
Accepted

Unclear update conflict

Why do I get update conflict in this situation instead of just blocking It is a product defect, which is fixed in SQL Server 2019. A snapshot write conflict occurs when a snapshot transaction ...
Paul White's user avatar
  • 83.7k
17 votes

What is lock escalation?

SQL Server does Lock Escalation in order to reduce memory overhead, by converting several fine-grained low level locks to coarse-grained high level locks. Row Locks are always escalated to Table ...
Manoj Pandey's user avatar
17 votes

Query hanging in ClientRead and blocking all others

You have a problem with transaction management. A session that waits for ClientRead is done processing the last query and waits for the client to send the next request. The only way that such a ...
Laurenz Albe's user avatar
  • 50.3k
15 votes
Accepted

Good reasons to use SELECT... WITH XLOCK?

As far I understand this, I am looking at a KEYLOCK deadlock basically caused by an uncovered index query that uses a nonclustered and a clustered index in order to collect the required values, right? ...
Paul White's user avatar
  • 83.7k
15 votes
Accepted

Checking @@ROWCOUNT failing

I'm not sure why you're using a variable, but you need to protect multiple statements with a transaction. What's happening is two users are calling the procedure at the same time, both are getting ...
Aaron Bertrand's user avatar
15 votes
Accepted

Why does this RX-X lock not appear in Extended Events?

The single row insert acquires an X (exclusive) lock on the new row. The SELECT attempts to acquire a range-shared, key shared (RangeS-S) lock. This request is reported by the lock_acquired Extended ...
Paul White's user avatar
  • 83.7k
14 votes
Accepted

What happens when we add an index to an existing table with a large amount of data?

With plain CREATE INDEX, the table will be locked for writes but not reads. Use CREATE INDEX CONCURRENTLY to avoid write locks as well. From the PostgreSQL docs on CREATE INDEX: When this option ...
Fabrizio Mazzoni's user avatar
14 votes
Accepted

SELECT FOR UPDATE locking other tables in PostgreSQL

We also don't ever update primary keys. In that case, I think you can use FOR NO KEY UPDATE instead of FOR UPDATE. It's a weaker lock, as explained in Postgres docs about Explicit Locking: FOR NO ...
ypercubeᵀᴹ's user avatar
14 votes
Accepted

MySQL: Safe to kill transaction that is stuck in "Waiting for table metadata lock"

Yes, it's safe. Your alter table query hasn't even started. However, the waiting for table metadata lock means, that there's another transaction operating on that table for a long time and you should ...
tombom's user avatar
  • 3,148
13 votes
Accepted

Could not obtain exclusive lock on database 'model' - who is using it?

Your model database is locked now so what you should do is to find out the session that has a lock on it, this can be done using sys.dm_tran_locks: select request_session_id from sys.dm_tran_locks ...
sepupic's user avatar
  • 10.7k
13 votes
Accepted

In the following inventory system, is it possible to force row locks to avoid deadlock and ensure it works as intended?

My first question is, will these update statements work as intended? Very likely, but not certain. SQL Server guarantees it will honour the semantics of the query, and the level of ACID compliance ...
Paul White's user avatar
  • 83.7k
13 votes

How to know which application or user put the SQL Server Database in single user mode

The spid is recorded in the SQL Server error log, but not the user / application / host. And, as Grant mentioned, nothing is recorded to the built-in system_health session (or the default trace, if ...
Aaron Bertrand's user avatar
12 votes
Accepted

Massive INSERTs blocking SELECTs

First, though slightly unrelated to the main question, your MERGE statement is potentially at risk of errors due to a race condition. The problem, in a nutshell, is that it is possible for multiple ...
Paul White's user avatar
  • 83.7k
12 votes
Accepted

Database level lock

You can use a query with UPDLOCK first, to protect you from everything throughout the transaction (except dirty reads). It can't ever be a single lock because a single lock can't span objects. But I ...
Aaron Bertrand's user avatar
12 votes
Accepted

How can I drop a column from a large (700M rows) hot (multiple transactions per second) table with minimal downtime?

How will SQL Server queue this request (eg First In First Out)? Can I be sure the DROP COLUMN will truly only take a few moments? The way the locking works for ALTER TABLE is that you need a Schema ...
David Browne - Microsoft's user avatar
11 votes
Accepted

Does UPDLOCK guarantee concurrency

Short Answer The update lock is sufficient, but you can achieve what you want more simply with: UPDATE dbo.tblOrderNumber WITH (SERIALIZABLE) SET @NextOrderNumber = NextOrderNumber, ...
Paul White's user avatar
  • 83.7k
11 votes
Accepted

How to get SQL insert and/or update to not lock entire table on MS SQL Server

Chances are it isn't locking the "whole table". It is locking a row in the table but your SELECT * FROM LAYOUTSv2 tries to read the whole table so necessarily is blocked by that lock. For the insert ...
Martin Smith's user avatar
  • 84.4k
11 votes
Accepted

During rollback, is lock escalation reversed

SQL Server does not de-escalate the lock. I investigated using a "Numbers" table with 100,000 rows. Empirically, updating 5,000 rows produced a corresponding number of RID locks in sys.dm_tran_locks. ...
Michael Green's user avatar
11 votes

Does Microsoft SQL Server offer an Advisory Locks feature like Postgres?

Application locks. They are arbitrary locks held by the engine on behalf of the app. Acquired with sp_getapplock and released with sp_releaselock (if needed). Shared, Update, Exclusive and intent ...
Remus Rusanu's user avatar
  • 51.8k
10 votes

How to get SQL insert and/or update to not lock entire table on MS SQL Server

The insert and update statements are supposed to create row-level locks. However, when the number of locks in any transaction is 5,000 or more then a lock escalation occurs and it creates a table ...
Suraj's user avatar
  • 201
10 votes
Accepted

Why does INSERT SELECT into an unrelated table block when SELECT doesn't? Mysql 5.6

Actually, INSERT INTO .. SELECT does lock the SELECT table I wrote about this before Apr 25, 2016 : MySQL INSERT ... SELECT Performance Aug 08, 2014 : MySQL consistent nonlocking reads vs. INSERT ......
RolandoMySQLDBA's user avatar
10 votes
Accepted

SQL Server - Is there a way to prevent shared range locks to 'infinity' when using an indexed view?

There's nothing you can do about this. SQL Server automatically takes steps to ensure the indexed view always stays synchronized with the base tables. When reading the indexed view to see if the data ...
Paul White's user avatar
  • 83.7k
10 votes

Unclear update conflict

And the second theoretical question: How does SQL Server handle include columns update? I mean how does SQL Server update all nonclustered index which have an include columns when we update this ...
Josh Darnell's user avatar
  • 29.2k
10 votes
Accepted

Request Sch-M lock on a table

There are any number of ways to do literally what you ask, but I would generally advise against using any of them. In most cases, all you need do is prevent any changes to the table while your process ...
Paul White's user avatar
  • 83.7k
10 votes
Accepted

Do I need an explicit FOR UPDATE lock in a CTE in UPDATE?

Postgres uses a multiversion model (Multiversion Concurrency Control, MVCC). In default READ COMMITTED isolation level, each separate query effectively sees a snapshot of the database as of the ...
Erwin Brandstetter's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible