CODEWITHSUNDEEP
postgresqlacid
Sam Lee
Sam Lee

Reputation: 10453

How do transactions work in the context of reads to the database?

I am using transactions to make changes to a SQL database. As I understand it, this means that changes to the database will happen in an all-or-nothing fashion. What I want to know is, does this have any guarantees for reads? For example, suppose I have some (pseudo)-code like this:

1) start TRANSACTION
2) INSERT INTO users ... // insert some data
3) count = SELECT COUNT(*) FROM ... // count something in the database
4) if count > 10: // do something based on the read
5)     INSERT INTO other_table ... // write based on the read
6) COMMMIT TRANSACTION

In this code, I'm doing an INSERT, followed by a SELECT, and then conditionally doing another INSERT based on the outcome of the SELECT.

So my question is, if another process modifies the database between steps (3) and (5), what happens to the count variable, and to my transaction?

If it makes a difference, I am using PostgreSQL.

Upvotes: 1

Views: 248

Answers (2)

Nick Barnes
Nick Barnes

Reputation: 21336

As Xin pointed out, it depends on the isolation level.

At the default READ COMMITTED level, records from other sessions will become visible as they are committed; you would see the same records if you didn't start a transaction at all (though of course, other processes would see your inserts appear at different times).

With REPEATABLE READ, your queries will not see any records committed by other sessions after your transaction starts. But while you don't have to worry about the result of SELECT COUNT(*) changing during your transaction, you can't assume that this result will still be accurate by the time you commit.

Using SERIALIZABLE provides the strongest guarantee: if your script does the right thing when given exclusive access to the database, then it will do the right thing in the presence of other serialisable transactions (or it will fail outright). However, this means that all transactions which might interfere with yours must be using the same isolation level (which comes at a cost), and all must be prepared to retry their transaction in the event of a serialisation failure.

When serialisable transactions are not an option, you generally guard against race conditions by explicitly locking things against concurrent writes. It's often enough to lock a selection of records, but you can't exactly lock the result of a COUNT(*); in your case, you'd probably need to lock the whole table.

Upvotes: 1

Xin
Xin

Reputation: 36520

I am not working on postgreSQL, but I think I can answer your question. Think of every query is parallel. I am saying so, because there are 2 transactions: when you insert into a; others can insert into b; then when you check b; whether you can see the new data depends on your isolation setting (read committed or just dirty read).

Also please note that, in database, there is a technology called lock: you can lock a table so that prevent altering it from others before committing your transaction.

Hope

Upvotes: 0

Related Questions