Skip to main content
24 votes

Detect when a CREATE INDEX CONCURRENTLY is finished in PostgreSQL

Combining the comment from @Rory and info in @Zaytsev Dmitry's answer: The CREATE INDEX CONCURRENTLY will not return until the index has finished building. So you know the index is done when your ...
Anentropic's user avatar
24 votes
Accepted

Best situation to use READ UNCOMMITTED isolation level

I use READ_UNCOMMITTED (or NOLOCK) when querying production databases from SSMS but not routinely from application code. This practice (along with a MAXDOP 1 query hint) helps ensure casual queries ...
Dan Guzman's user avatar
  • 28.3k
18 votes
Accepted

How to perform conditional insert based on row count?

Typically, you have a team table (or similar) with a unique team_id column. Your FK constraint indicates as much: ... REFERENCES teams(id) - so I'll work with teams(id). Then, to avoid complications (...
Erwin Brandstetter's user avatar
17 votes
Accepted

What is "special" about PostgreSQL update vs delete+insert

Yes, there is a difference between UPDATE and DELETE + INSERT. Let's use the pageinspect extension to look at the tuples and the tuple headers. If you want to repeat my experiment, you have to drop ...
Laurenz Albe's user avatar
17 votes
Accepted

Safe to use WITH (NOLOCK) in this case?

I'm not a DBA, but a software dev with a few years of DB experience. I'm only "textbook-level" familiar with the inner workings of locking, pages, hints, Then you should use SNAPSHOT ...
David Browne - Microsoft's user avatar
17 votes

What are the problems with using NOLOCK given an approach to handle the short comings?

Whenever you are reading data that might be concurrently changed by other processes, you have to decide what level of transaction isolation you need. Every isolation level below serializable comes ...
Paul White's user avatar
  • 86.9k
16 votes
Accepted

Why am I getting a deadlock for a single UPDATE query?

Your statement modifies several rows. Each of these rows is locked when it is updated. It is well possible that a statement in a concurrent transaction has already locked one of these rows, blocking ...
Laurenz Albe's user avatar
16 votes

Why am I getting a deadlock for a single UPDATE query?

Laurenz explained the mechanism that can lead to deadlocks, and you already included a link yourself to a more detailed explanation by Kevin: Deadlocks in PostgreSQL when running UPDATE Here are ...
Erwin Brandstetter's user avatar
15 votes
Accepted

Finding the default isolation level in SQL Server 2017 and Azure SQL DB

It's referring to Azure SQL Database which uses RCSI by default. Isolation Level SQL Database default database wide setting is to enable read committed snapshot isolation (RCSI) by having both the ...
Erik Darling's user avatar
  • 42.1k
15 votes

Concurrent transactions result in race condition with unique constraint on insert

The root of the problem is that, with default READ COMMITTED isolation level, each concurrent UPSERT (or any query, for that matter) can only see rows that were visible at the start of the query. The ...
Erwin Brandstetter's user avatar
15 votes
Accepted

UPDATE statement behavior

I have a question regarding inner workings of UPDATE statements with regards to SQL Server. The statement describes a desired logical change to the database. What happens physically at runtime ...
Paul White's user avatar
  • 86.9k
14 votes

Detect when a CREATE INDEX CONCURRENTLY is finished in PostgreSQL

You can get list of invalid indexes. SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid; If you see your index in this query it means the index ...
Logovsky Dmitry's user avatar
14 votes
Accepted

Identity Column in Concurrency, Multithreading, Parallel Processing

Would the identity primary key column fail, with a primary key violation in any way, example: processors are trying to input the same identity number? This is covered in the SQL Server product ...
Paul White's user avatar
  • 86.9k
14 votes

Is the system column "ctid" legitimate for identifying rows to delete?

The ROW SHARE lock taken by FOR UPDATE prevents concurrent write access that would change the physical location of the row. The manual: This prevents them from being locked, modified or deleted by ...
Erwin Brandstetter's user avatar
13 votes
Accepted

SQL server temp table using # - is it only accessible by the containing query for multiple connections with the same login?

Local temporary objects are separated by Session. If you have two queries running concurrently, then they are clearly two completely separate sessions and you have nothing to worry about. The Login ...
Solomon Rutzky's user avatar
13 votes
Accepted

SQL Server Concurrent Delete/Insert Application

The only query in what you're showing above appears to be this one, repeated a few times: IF EXISTS ( select * from [dbo].[FinanceDetail] trd WITH (UPDLOCK, ...
Brent Ozar's user avatar
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
  • 86.9k
11 votes
Accepted

Concurrent transactions result in race condition with unique constraint on insert

Try the insert first, with on conflict ... do nothing and returning id. If the value already exists, you will get no result from this statement, so you have then to execute a select to get the ID. If ...
CL.'s user avatar
  • 5,243
11 votes
Accepted

Missing rows under read committed isolation

A scenario where rows committed before the start of the SELECT query can be missed is when an index key value is updated. Consider this query: SELECT * FROM dbo.YourTable; The execution plan will ...
Dan Guzman's user avatar
  • 28.3k
10 votes
Accepted

Why is NOLOCK implemented like that?

SQL Server by default operates under Read Committed isolation. If you want SQL Server to behave more like other implementations, what you're looking for is either Read Committed Snapshot Isolation, ...
Erik Darling's user avatar
  • 42.1k
10 votes
Accepted

Is a serialization anomaly only possible with SUM/COUNT?

There's an example attributed to Jim Gray. There are two rows in the database. One has the value "white" and the other "black". Transaction T1 updates all white to black, T2 all ...
Michael Green's user avatar
9 votes
Accepted

SQL Server Concurrent Inserts and Deletes

The UPDLOCK, SERIALIZABLE pattern is all about avoiding incorrect results (including false key violation errors) due to race conditions, when performing a particularly common operation known as an ...
Paul White's user avatar
  • 86.9k
9 votes
Accepted

Performance of large transactions and concurrency?

Are rows being updated during the transaction able to be read after the transaction starts and before it finishes? Yes, in Postgres reads do not block writes and writes do not block reads. The ...
taffer's user avatar
  • 269
9 votes

UPDATE statement behavior

When it comes to writing, there is no such thing as "same time." One session will "win" the race for the lock, and update the value first; the "loser" will update the ...
Aaron Bertrand's user avatar
9 votes
Accepted

Is my queue table implementation race condition safe?

The only thing you're asking the database engine to do in this scenario is to enforce the PRIMARY KEY. It will do that under all conditions and isolation levels, of course. The potential race ...
Paul White's user avatar
  • 86.9k
8 votes

Best situation to use READ UNCOMMITTED isolation level

I think it's fine in some circumstances, as long as you accept the consequences, and don't have other options. For other options, I'd push people towards using Read Committed Snapshot Isolation (RCSI)...
Erik Darling's user avatar
  • 42.1k
8 votes

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

Since we've established that no other transaction holds any of the locks the current one holds, it logically follows that no other transaction would be attempting to update or lock any of the ...
Randi Vertongen's user avatar
8 votes
Accepted

Will my SQL query use stale data? How can I prevent it?

If you're using the default isolation level in SQL Server (Read Committed), then you certainly can run into all sorts of issues around inconsistent reads. Paul White describes the problems here. If ...
AMtwo's user avatar
  • 16.2k
7 votes
Accepted

Strategy for concurrent group bookings?

Because you're not telling us much of what you need, I'll guess for everything, and we'll make it moderately complex to simplify some of the possible questions. First thing about MVCC is that in a ...
Evan Carroll's user avatar
  • 63.9k

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