32 votes
Accepted

Locking in Postgres for UPDATE / INSERT combination

I have heard of concurrency problems like that in MySQL before. Not so in Postgres. Built-in row-level locks in the default READ COMMITTED transaction isolation level are enough. I suggest a single ...
31 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 ...
  • 73.8k
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 ...
  • 24.7k
22 votes
Accepted

Read a partially updated row?

Yes, SQL Server can, under some circumstances read one column's value from the "old" version of the row, and another column's value from the "new" version of the row. Setup: CREATE TABLE Person ( ...
  • 78.6k
17 votes
Accepted

Why is CTE open to lost updates?

I think I probably meant to add that comment on the prior answer, about two separate statements. It was over a year ago, so I'm not totally sure anymore. The wCTE based query doesn't really solve the ...
  • 52.8k
17 votes
Accepted

PostgreSQL - If I run multiple queries concurrently, under what circumstances would I see a speedup? Under what circumstances would I see a slowdown?

If I run them serially, one right after the other, I'm expecting it will require 7 minutes to complete on average. Is this reasonable? If they use unrelated data sets, then yes. If they share a data ...
  • 52.8k
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 ...
  • 73.8k
16 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 ...
15 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 (...
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 ...
  • 34.4k
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 ...
  • 73.8k
15 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 ...
  • 35.1k
14 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 ...
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 ...
13 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 ...
  • 73.8k
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, ...
  • 41.5k
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 ...
  • 73.8k
12 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 ...
12 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 ...
  • 35.1k
12 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 ...
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, ...
  • 73.8k
11 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 ...
11 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 ...
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 ...
  • 24.7k
10 votes
Accepted

How to avoid a "duplicate key" error?

If you really need to run multiple threads at the same time you can enable the ignore_dup_key option on the primary key. This will just give a warning instead of an error when an insert would ...
  • 1,741
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, ...
  • 34.4k
10 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 ...
  • 4,875
10 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 ...

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