Transferencias

Transaction Processing in PostgreSQL

Transaction Processing in PostgreSQL

Tom Lane Great Bridge, LLC tgl@sss.pgh.pa.us
30 Oct 2000 Tom Lane

1

Transaction Processing in PostgreSQL

Outline
Introduction • What is a transaction? User’s view • Multi-version concurrency control Implementation • Tuple visibility • Storage management • Locks

2
30 Oct 2000 Tom Lane

TransactionProcessing in PostgreSQL

PostgreSQL system overview

Client Processes Client Application
DB Requests and Results via Library API Initial Connection Request and Authentication

Server Processes Postmaster Daemon Process
Spawn Server Process

Client Interface Library

SQL Queries and Results

Postgres Server (Backend)

3
30 Oct 2000 Tom Lane

Transaction Processing in PostgreSQLPostgreSQL system overview
Server Processes Postmaster Daemon Process
Spawn Server Process
Create Shared Disk Buffers Kernel Disk Buffers

Shared Memory

Unix System

Postgres Server (Backend)

Read/ Write

Shared Tables

Disk Storage

• Database files are accessed through shared buffer pool • Hence, two backends can never see inconsistent views of a file • Unix kernel usuallyprovides additional buffering 4
30 Oct 2000 Tom Lane

Transaction Processing in PostgreSQL

What is a transaction, anyway?

Definition: a transaction is a group of SQLcommands whose results will be made visible to the rest of the system as aunit when the transaction commits --- or not at all, if the transaction aborts.

Transactions are expected to be atomic, consistent,isolated, anddurable.

• Postgres does not support distributed transactions, so all commandsof a transaction are executed by one backend. • We don’t currently handle nested transactions, either.

5
30 Oct 2000 Tom Lane

Transaction Processing in PostgreSQL

The ACID test: atomic, consistent, isolated, durable
Atomic: results of a transaction are seen entirely or not at all within other transactions. (Atransaction need not appear atomic to itself.)

Consistent: system-defined consistency constraints areenforced on the results of transactions. (Not going to discuss constraintchecking today.)

Isolated: transactions are not affected by the behavior ofconcurrently-running transactions. Stronger variant: serializable. If the final resultsof a set of concurrent transactions are the same as ifwe’d run thetransactions serially in some order (not necessarily any predetermined order), then we say the behavior is serializable.

Durable: once a transaction commits, its results will not belost regardless of subsequent failures. 6
30 Oct 2000 Tom Lane

Transaction Processing in PostgreSQL

But how can thousands of changes be made "atomically"?
• The actual tupleinsertions/deletions/updates are all markedas done by transaction N as they are being made. Concurrently running backends ignore the changes because they know transaction N is not committed yet. When the transactioncommits, all those changes become logically visible at once. • The control file pg_log contains 2 status bits pertransaction ID, with possible states in progress, committed,aborted. Setting those two bits tothevalue committed is the atomic action that marks a transaction committed. • An aborting transaction will normally set its pg_log statusto aborted. But even if the process crashes without havingdone so, everything is safe. The next time some backend checks the state ofthat transaction, it will observe that the transaction is marked inprogress but is not running on any backend, deduce that itcrashed,and update the pg_log entry to aborted on its behalf.No changes are needed in any table data file during abort. 7
30 Oct 2000 Tom Lane

Transaction Processing in PostgreSQL

But is it really atomic and durable, even if the system crashes?
Well ... that depends on how much you trust your kernel and hard disk.

• Postgres transactions are only guaranteed atomic if a disk page write...