Skip to main content
30 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
  • 3,259
25 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
  • 78.6k
19 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
  • 54.1k
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
  • 70.2k
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
  • 87.1k
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
  • 87.1k
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,158
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
  • 11k
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
  • 87.1k
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

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
12 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
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.9k
11 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
  • 87.1k
11 votes
Accepted

Is it possible to never run vacuum full in PostgreSQL

In order to understand the differences between VACUUM VACUUM ANALYZE VACUUM FULL, a basic understanding of PostgreSQL's architecture is required. From several9s1 we are told that: PostgreSQL does ...
Vérace's user avatar
  • 30.3k
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

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.4k
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
  • 87.1k
10 votes
Accepted

How does SQL Server update lock work?

SQL Server does not take an intent exclusive (IX) lock on the database in the situation you describe. IX locks are taken at the object (table) level, and on the page containing the target row before ...
Paul White's user avatar
  • 87.1k
9 votes
Accepted

Transaction with lock on a specific table

Use a key table to allocate receipt numbers in a way that is concurrency friendly, and still guarantees receipt numbers will never be re-used, and will (almost) never contain gaps. I've created a ...
Hannah Vernon's user avatar
  • 70.2k
9 votes
Accepted

Does changing the length limit (type-modifier) of varchar() result in a table or index rewrite?

Essentially, this is out of date information. It hasn't been relevant since 9.2. Now, the only drawback, that I can see, is that the index gets rewritten if the length constraint gets more restrictive ...
Evan Carroll's user avatar
  • 63.9k
9 votes
Accepted

Lock in SQL Server and Oracle

This is a default behavior of SQL server, to understand more you need to invoke isolation level. What you have described above falls in isolation level called "Read Committed". If you want ...
Learning_DBAdmin's user avatar
9 votes
Accepted

Sometimes the updated rows are not locked within instead of update trigger

When the update statement qualifies for a trivial plan, the optimizer rule that expands the instead-of trigger part of the statement (ExpandInsteadOfTriggerUpd) includes the part of the plan that ...
Paul White's user avatar
  • 87.1k
9 votes
Accepted

What type of locks are needed when creating a Postgres index concurrently?

Well, when documentation is not enough it is possible to looking directly in code. I will give links to already released PostgreSQL 13.1 (REL_13_1 tag) in order to avoid possible line number changes ...
Melkij's user avatar
  • 3,259
9 votes
Accepted

How to use application lock in SQL server from two databases

Consider database-qualifying the sp_getapplock proc name. This way, the lock will be acquired in the specified database instead of the current session database context. EXEC @lock_result = tempdb.....
Dan Guzman's user avatar
  • 28.3k

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