Performance of large transactions and concurrency?

If I have a multi-million row table and I run a transaction that updates 50k rows, what are the performance implications of this?

Assuming it's indexed correctly, it shouldn't take long, but what rows are locked and how is the usage of that table affected?

• Are rows being updated during the transaction able to be read

after the transaction starts and before it finishes?

• Are rows not being updated during

the transaction able to be read after the transaction starts and before it finishes?

• If another transaction starts trying to change rows that are being changed by

a previously unfinished transaction, will that transaction fail at
start or after it tries to commit (assuming conflict)?

My question is for Postgres 9.3; I assume there are variations.

Are rows being updated during the transaction able to be read after the transaction starts and before it finishes?

Yes, in Postgres reads do not block writes and writes do not block reads. The Postgres documentation states that:


Internally, data consistency is maintained
by using a multiversion model (Multiversion Concurrency Control,
MVCC). This means that while querying a database each transaction sees
a snapshot of data (a database version) as it was some time ago,
regardless of the current state of the underlying data. […] The main
advantage of using the MVCC model of concurrency control rather than
locking is that in MVCC locks acquired for querying (reading) data do not
conflict with locks acquired for writing data, and so reading never
blocks writing and writing never blocks reading.

Are rows not being updated during the transaction able to be read after the transaction starts and before it finishes?

Yes.

If another transaction starts trying to change rows that are being changed by a previously unfinished transaction, will that transaction fail at start or after it tries to commit (assuming conflict)?

This depends on the Transaction Isolation Level and if you are issuing a write that depends on a read or a blind write. Using the default level Read Committed the second transaction has to wait until the first transaction is done writing. In higher transaction levels one of the transactions might get aborted with a serialization error.

You can actually try this yourself by running two psql sessions:

Session 1:

-- first set up a table
CREATE TABLE tools (id SERIAL PRIMARY KEY, description STRING);
INSERT INTO tools(description) VALUES('scredriver');
INSERT INTO tools(description) VALUES('hammer');

-- now type the following into two psql sessions
          SESSION 1             |          SESSION 2
                                |
BEGIN TRANSACTION;              |
UPDATE tools                    |
   SET description = 'anvil'    |
 WHERE id = 1;                  |
                                | BEGIN TRANSACTION;
                                |UPDATE tools
                                |   SET description = 'wrench'
                                | WHERE id = 1;
                                |-- this transaction is blocked
                                |-- until the other transaction
COMMIT TRANSACTION;             |-- commits

As you will see, session 2 will get blocked by session 1. Only if session 1 commits, session 2 will be able to continue.

The postgres documentation also contains performance suggestions on how to avoid blocking and serialization failures when using higher transaction levels.

StackExchange Database Administrators Q#222262, answer score: 9