1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
5 >Transaction Isolation</TITLE
8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
12 TITLE="PostgreSQL 7.4.1 Documentation"
13 HREF="index.html"><LINK
15 TITLE="Concurrency Control"
16 HREF="mvcc.html"><LINK
18 TITLE="Concurrency Control"
19 HREF="mvcc.html"><LINK
21 TITLE="Explicit Locking"
22 HREF="explicit-locking.html"><LINK
25 HREF="stylesheet.css"><META
27 CONTENT="2003-12-22T03:48:47"></HEAD
33 SUMMARY="Header navigation table"
43 >PostgreSQL 7.4.1 Documentation</TH
67 >Chapter 12. Concurrency Control</TD
81 HREF="explicit-locking.html"
95 NAME="TRANSACTION-ISO"
96 >12.2. Transaction Isolation</A
105 > standard defines four levels of
106 transaction isolation in terms of three phenomena that must be
107 prevented between concurrent transactions. These undesirable
123 > A transaction reads data written by a concurrent uncommitted transaction.
134 > A transaction re-reads data it has previously read and finds that data
135 has been modified by another transaction (that committed since the
147 > A transaction re-executes a query returning a set of rows that satisfy a
148 search condition and finds that the set of rows satisfying the condition
149 has changed due to another recently-committed transaction.
161 The four transaction isolation levels and the corresponding
162 behaviors are described in <A
163 HREF="transaction-iso.html#MVCC-ISOLEVEL-TABLE"
170 NAME="MVCC-ISOLEVEL-TABLE"
174 >Table 12-1. <ACRONYM
177 > Transaction Isolation Levels</B
182 ><COL><COL><COL><COL><THEAD
263 offers the Read Committed and Serializable isolation levels.
270 NAME="XACT-READ-COMMITTED"
271 >12.2.1. Read Committed Isolation Level</A
281 is the default isolation level in <SPAN
285 When a transaction runs on this isolation level,
289 > query sees only data committed before the
290 query began; it never sees either uncommitted data or changes committed
291 during query execution by concurrent transactions. (However, the
295 > does see the effects of previous updates
296 executed within its own transaction, even though they are not yet
297 committed.) In effect, a <TT
301 sees a snapshot of the database as of the instant that that query
302 begins to run. Notice that two successive <TT
306 see different data, even though they are within a single transaction, if
308 commit changes during execution of the first <TT
324 > commands behave the same as <TT
328 in terms of searching for target rows: they will only find target rows
329 that were committed as of the command start time. However, such a target
330 row may have already been updated (or deleted or marked for update) by
331 another concurrent transaction by the time it is found. In this case, the
332 would-be updater will wait for the first updating transaction to commit or
333 roll back (if it is still in progress). If the first updater rolls back,
334 then its effects are negated and the second updater can proceed with
335 updating the originally found row. If the first updater commits, the
336 second updater will ignore the row if the first updater deleted it,
337 otherwise it will attempt to apply its operation to the updated version of
338 the row. The search condition of the command (the <TT
342 re-evaluated to see if the updated version of the row still matches the
343 search condition. If so, the second updater proceeds with its operation,
344 starting from the updated version of the row.
347 > Because of the above rule, it is possible for an updating command to see an
348 inconsistent snapshot: it can see the effects of concurrent updating
349 commands that affected the same rows it is trying to update, but it
350 does not see effects of those commands on other rows in the database.
351 This behavior makes Read Committed mode unsuitable for commands that
352 involve complex search conditions. However, it is just right for simpler
353 cases. For example, consider updating bank balances with transactions
359 UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
360 UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
364 If two such transactions concurrently try to change the balance of account
365 12345, we clearly want the second transaction to start from the updated
366 version of the account's row. Because each command is affecting only a
367 predetermined row, letting it see the updated version of the row does
368 not create any troublesome inconsistency.
371 > Since in Read Committed mode each new command starts with a new snapshot
372 that includes all transactions committed up to that instant, subsequent
373 commands in the same transaction will see the effects of the committed
374 concurrent transaction in any case. The point at issue here is whether
375 or not within a <SPAN
381 > command we see an absolutely consistent
382 view of the database.
385 > The partial transaction isolation provided by Read Committed mode is
386 adequate for many applications, and this mode is fast and simple to use.
387 However, for applications that do complex queries and updates, it may
388 be necessary to guarantee a more rigorously consistent view of the
389 database than the Read Committed mode provides.
397 NAME="XACT-SERIALIZABLE"
398 >12.2.2. Serializable Isolation Level</A
407 > provides the strictest transaction
408 isolation. This level emulates serial transaction execution,
409 as if transactions had been executed one after another, serially,
410 rather than concurrently. However, applications using this level must
411 be prepared to retry transactions due to serialization failures.
414 > When a transaction is on the serializable level,
418 > query sees only data committed before the
419 transaction began; it never sees either uncommitted data or changes
421 during transaction execution by concurrent transactions. (However, the
425 > does see the effects of previous updates
426 executed within its own transaction, even though they are not yet
427 committed.) This is different from Read Committed in that the
432 sees a snapshot as of the start of the transaction, not as of the start
433 of the current query within the transaction. Thus, successive
437 > commands within a single transaction always see the same
451 > commands behave the same as <TT
455 in terms of searching for target rows: they will only find target rows
456 that were committed as of the transaction start time. However, such a
458 row may have already been updated (or deleted or marked for update) by
459 another concurrent transaction by the time it is found. In this case, the
460 serializable transaction will wait for the first updating transaction to commit or
461 roll back (if it is still in progress). If the first updater rolls back,
462 then its effects are negated and the serializable transaction can proceed
463 with updating the originally found row. But if the first updater commits
464 (and actually updated or deleted the row, not just selected it for update)
465 then the serializable transaction will be rolled back with the message
469 >ERROR: could not serialize access due to concurrent update</PRE
472 because a serializable transaction cannot modify rows changed by
473 other transactions after the serializable transaction began.
476 > When the application receives this error message, it should abort
477 the current transaction and then retry the whole transaction from
478 the beginning. The second time through, the transaction sees the
479 previously-committed change as part of its initial view of the database,
480 so there is no logical conflict in using the new version of the row
481 as the starting point for the new transaction's update.
484 > Note that only updating transactions may need to be retried; read-only
485 transactions will never have serialization conflicts.
488 > The Serializable mode provides a rigorous guarantee that each
489 transaction sees a wholly consistent view of the database. However,
490 the application has to be prepared to retry transactions when concurrent
491 updates make it impossible to sustain the illusion of serial execution.
492 Since the cost of redoing complex transactions may be significant,
493 this mode is recommended only when updating transactions contain logic
494 sufficiently complex that they may give wrong answers in Read
495 Committed mode. Most commonly, Serializable mode is necessary when
496 a transaction executes several successive commands that must see
497 identical views of the database.
506 SUMMARY="Footer navigation table"
535 HREF="explicit-locking.html"
545 >Concurrency Control</TD
559 >Explicit Locking</TD
OSDN Git Service
