Explanation of SQL: BEGIN, COMMIT flow?

Question

I try to understand, how that SQL command works:

BEGIN;
UPDATE post SET hits = hits + 1;
-- run from another session:  DELETE FROM post WHERE hits = 10;
COMMIT;

Let's say, we hahe a rows with hits = 9 and 10. Then we run that query, and then what? What (and why) will our rows look like?

Why don't you simply test? I mean - it takes 10 seconds to start 2 terminals, and test. And you'll get valuable information in a way that will teach you more. — user80168
– user80168, Commented Jun 19, 2013 at 11:54
But how to test it in that very small ammount of time, when transaction is being active (between BEGIN and COMMIT)? — tunarob
– tunarob, Commented Jun 19, 2013 at 11:57
Start transaction, issue update. then, in other psql, run the delete. — user80168
– user80168, Commented Jun 19, 2013 at 11:57

VitaliyG · Accepted Answer · 2013-06-19 11:58:39Z

1

This depends on transaction isolation level in each session but by default
all changes made by UPDATE will be visible to other session only after executing COMMIT.
So your DELETE session will behave like there was no UPDATE at all.

answered Jun 19, 2013 at 11:58

VitaliyG

1,8571 gold badge11 silver badges11 bronze badges

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

6 Comments

user330315 Over a year ago

There is no isolation level in Postgres that will ever make non-committed data visible to other transactions.

tunarob Over a year ago

So if I understand well - BEGIN - COMMIT puts a lock on affected rows, and until COMMIT that rows some kind of invisible for other queries?

user330315 Over a year ago

@VitaliyG: you should read the manual carefully: "When you select the level Read Uncommitted you really get Read Committed" (emphasis mine) - there is no way to run un-committed day in Postgres

VitaliyG Over a year ago

Yes it is not implemented for now

user330315 Over a year ago

It will never be implemented. read uncommitted is simply a hack by other DBMS to allow for non-blocking concurrent reads and writes

|

Collectives™ on Stack Overflow

Explanation of SQL: BEGIN, COMMIT flow?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related