Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

Concurrent Write Examples

The following pseudo-code examples demonstrate how transactions either proceed or abort and roll back when they are run concurrently.

Concurrent COPY Operations into the Same Table

Transaction 1 copies rows into the LISTING table:

Copy
begin; copy listing from ...; end;

Transaction 2 starts concurrently in a separate session and attempts to copy more rows into the LISTING table. Transaction 2 must wait until transaction 1 releases the write lock on the LISTING table, then it can proceed.

Copy
begin; [waits] copy listing from ; end;
The same behavior would occur if one or both transactions contained an INSERT command instead of a COPY command.

Concurrent DELETE Operations from the Same Table

Transaction 1 deletes rows from a table:

Copy
begin; delete from listing where ...; end;

Transaction 2 starts concurrently and attempts to delete rows from the same table. It will succeed because it waits for transaction 1 to complete before attempting to delete rows.

Copy
begin [waits] delete from listing where ; end;

The same behavior would occur if one or both transactions contained an UPDATE command to the same table instead of a DELETE command.

Concurrent Transactions with a Mixture of Read and Write Operations

In this example, transaction 1 deletes rows from the USERS table, reloads the table, runs a COUNT(*) query, and then ANALYZE, before committing:

Copy
begin; delete one row from USERS table; copy ; select count(*) from users; analyze ; end;

Meanwhile, transaction 2 starts. This transaction attempts to copy additional rows into the USERS table, analyze the table, and then run the same COUNT(*) query as the first transaction:

Copy
begin; [waits] copy users from ...; select count(*) from users; analyze; end;

The second transaction will succeed because it must wait for the first to complete. Its COUNT query will return the count based on the load it has completed.