CODEWITHSUNDEEP
sqlpostgresql
xxx_coder_noscope
xxx_coder_noscope

Reputation: 85

How does table state in postgresql transaction works?

If i call some procedure that do a batch job, that takes 10 mins, for example. Which is in turn accessing some table. And what happens if that table which accessed within running procedure (transaction) is updated within these 10 minutes? Transaction will ignore these changes and keep state of the table at moment of a procedure call? Example: users table

|user_id|some_value|
|-------|----------|
|      1|       213|
|      3|       444|

-- and so on

If some_value for user_id = 3 is changed to 1 but transaction is still running, processing users table row by row, and user with id = 1 is not processed at the moment of some_value update, what value is going to be in procedure (transaction)? 1 or 444?

Upvotes: 1

Views: 500

Answers (1)

pifor
pifor

Reputation: 7882

In general a transaction always sees its own writings: if you run update mytable set some_value=1 where user_id=3 as long as this transaction is running, select some_value from mytable where user_id=3 returns 1 in this transaction. But other concurrent transactions cannot see this "pending" update.

If this transaction is ended by ROLLBACK no one will be able to see this value: not a new transaction in the same session or another transaction in another session.

If this transaction is ended by COMMIT, all other transactions will see this new value whether they run in another session or in the same session.

This assumes that the default transaction isolation level is READ COMMITTED.

Upvotes: 3

Related Questions