Transactions
Prepare a record
Section titled “Prepare a record”CREATE TABLE docs_balances (id BIGINT PRIMARY KEY, balance BIGINT);INSERT INTO docs_balances VALUES (1, 100);Roll back a change
Section titled “Roll back a change”BEGIN;UPDATE docs_balances SET balance = 50 WHERE id = 1;ROLLBACK;SELECT balance FROM docs_balances WHERE id = 1;The balance remains 100.
Commit a change
Section titled “Commit a change”BEGIN;UPDATE docs_balances SET balance = 75 WHERE id = 1;COMMIT;SELECT balance FROM docs_balances WHERE id = 1;The balance is 75.
Isolation and retries
Section titled “Isolation and retries”The default session isolation in 1.1.19-rc.17 is READ COMMITTED. A default setting is not a guarantee that concurrent transactions cannot violate a business rule; select a stronger level for a transaction when your application’s invariants require it:
BEGIN ISOLATION LEVEL SERIALIZABLE;SELECT balance FROM docs_balances WHERE id = 1;COMMIT;| Level | Use it when |
|---|---|
| READ COMMITTED | Each statement can read the latest committed data |
| REPEATABLE READ | Related reads need a consistent transaction snapshot |
| SERIALIZABLE | Concurrent transactions must preserve a serial execution order |
Server concurrency control is separate from the transaction isolation level. --concurrency-control (default optimistic) selects optimistic, strict-optimistic or none. SERIALIZABLE is validated identically under optimistic and strict-optimistic, but real-time ordering holds by construction only under strict-optimistic; under optimistic reads may fall back to an older row copy. none checks no conflicts and provides no isolation guarantee; REPEATABLE READ and SERIALIZABLE are refused there with SQLSTATE 0A000. Concurrency, isolation and durability.
Test concurrent invariants using independent connections and handle retryable conflicts even when individual statements succeed.
After an error, roll back before reusing the connection. A retryable conflict such as SQLSTATE 40001 requires retrying the complete transaction, using a bounded policy.
Boundaries
Section titled “Boundaries”Keep statements on one connection. Separate HTTP requests or Redis client commands are not automatically part of a SQL transaction. External actions need their own idempotency and reconciliation strategy: do not retry an external action solely because a database transaction failed. See workflow retries.
Not every schema command has the same transaction behavior. Check schema changes before wrapping a migration in a transaction. A commit’s persistence guarantee depends on the configured durability mode.
Related
Section titled “Related”This site uses Google Analytics cookies to count visits. Privacy · License