Database concurent UPDATE and SELECT with Read Uncommitted

Question

I have this TABLE_A:

id	columnA	columnB
1	oldValueA	oldValueB

When two queries are performed at the exact same time and concurrency issues arise:

Query #1:

UPDATE TABLE_A 
SET columnA = 'newValueA', columnB = 'newValueB' 
WHERE id = 1;

Query #2:

SELECT columnA, columnB 
FROM TABLE_A 
WHERE id = 1 WITH UR;

Is there a chance that the SELECT query will return mixed old/new values this one particular row like:

newValueA, oldValueB

Is the UPDATE query always atomic and there is no chance that SELECT with "Read Uncommitted" isolation level can fetch inconsistent column values in a scope of a particular row or the update can happen per column and the SELECT query can return when the other column is not yet updated? Does it depend on the database?

My question is related mostly to Db2 database.

in the scope of that specific update command, with both columns, you would never see such scenario newValueA, oldValueB BUT if within your application you have independent updates on each of those columns in separate transactions THAT could generate such scenario. — Jorge Campos
– Jorge Campos, Commented Jun 4 at 18:16
In my scenario there are two separate applications with schedulers that run these queries - app1 runs UPDATE at some time and app2 runs SELECT at some time. I wonder if there can be a collision when app2 will execute query during database processing of the UPDATE query from app1. I understand that SELECT with UR doesn't care about committing the UPDATE changes. — michalte97
– michalte97, Commented Jun 4 at 18:21
again, if the updates are the same (both columns at once), no. — Jorge Campos
– Jorge Campos, Commented Jun 4 at 18:25

ShiftHappens · Accepted Answer · 2025-06-05 18:40:59Z

0

For an SQL-compliant database, the answer is no.

A single UPDATE statement is atomic, in your scenario there is no way your SELECT statement can return a mix of old and new values.

source: SQL specifications 4.30-4-Statement Atomicity

answered Jun 5 at 18:40

ShiftHappens

331 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Database concurent UPDATE and SELECT with Read Uncommitted

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related