11<!--
2- $Header: /cvsroot/pgsql/doc/src/sgml/mvcc.sgml,v 2.30 2002/11/15 03:11:17 momjian Exp $
2+ $Header: /cvsroot/pgsql/doc/src/sgml/mvcc.sgml,v 2.31 2002/12/18 20:40:24 momjian Exp $
33-->
44
55 <chapter id="mvcc">
@@ -57,11 +57,10 @@ $Header: /cvsroot/pgsql/doc/src/sgml/mvcc.sgml,v 2.30 2002/11/15 03:11:17 momjia
5757 <title>Transaction Isolation</title>
5858
5959 <para>
60- The <acronym>SQL</acronym>
61- standard defines four levels of transaction
62- isolation in terms of three phenomena that must be prevented
63- between concurrent transactions.
64- These undesirable phenomena are:
60+ The <acronym>SQL</acronym> standard defines four levels of
61+ transaction isolation in terms of three phenomena that must be
62+ prevented between concurrent transactions. These undesirable
63+ phenomena are:
6564
6665 <variablelist>
6766 <varlistentry>
@@ -200,7 +199,7 @@ $Header: /cvsroot/pgsql/doc/src/sgml/mvcc.sgml,v 2.30 2002/11/15 03:11:17 momjia
200199
201200 <para>
202201 <productname>PostgreSQL</productname>
203- offers theread committed andserializable isolation levels.
202+ offers theRead Committed andSerializable isolation levels.
204203 </para>
205204
206205 <sect2 id="xact-read-committed">
@@ -635,7 +634,7 @@ ERROR: Can't serialize access due to concurrent update
635634 In addition to table and row locks, page-level share/exclusive locks are
636635 used to control read/write access to table pages in the shared buffer
637636 pool. These locks are released immediately after a tuple is fetched or
638- updated. Applicationwriters normally need not be concerned with
637+ updated. Applicationdevelopers normally need not be concerned with
639638 page-level locks, but we mention them for completeness.
640639 </para>
641640
@@ -645,25 +644,70 @@ ERROR: Can't serialize access due to concurrent update
645644 <title>Deadlocks</title>
646645
647646 <para>
648- Use of explicit locking can cause <firstterm>deadlocks</>, wherein
649- two (or more) transactions each hold locks that the other wants.
650- For example, if transaction 1 acquires an exclusive lock on table A
651- and then tries to acquire an exclusive lock on table B, while transaction
652- 2 has already exclusive-locked table B and now wants an exclusive lock
653- on table A, then neither one can proceed.
654- <productname>PostgreSQL</productname> automatically detects deadlock
655- situations and resolves them by aborting one of the transactions
656- involved, allowing the other(s) to complete. (Exactly which transaction
657- will be aborted is difficult to predict and should not be relied on.)
647+ The use of explicit locking can increase the likelyhood of
648+ <firstterm>deadlocks</>, wherein two (or more) transactions each
649+ hold locks that the other wants. For example, if transaction 1
650+ acquires an exclusive lock on table A and then tries to acquire
651+ an exclusive lock on table B, while transaction 2 has already
652+ exclusive-locked table B and now wants an exclusive lock on table
653+ A, then neither one can proceed.
654+ <productname>PostgreSQL</productname> automatically detects
655+ deadlock situations and resolves them by aborting one of the
656+ transactions involved, allowing the other(s) to complete.
657+ (Exactly which transaction will be aborted is difficult to
658+ predict and should not be relied on.)
658659 </para>
659660
660661 <para>
661- The best defense against deadlocks is generally to avoid them by being
662- certain that all applications using a database acquire locks on multiple
663- objects in a consistent order. One should also ensure that the first
664- lock acquired on an object in a transaction is the highest mode that
665- will be needed for that object. If it is not feasible to verify this
666- in advance, then deadlocks may be handled on-the-fly by retrying
662+ Note that deadlocks can also occur as the result of row-level
663+ locks (and thus, they can occur even if explicit locking is not
664+ used). Consider the case in which there are two concurrent
665+ transactions modifying a table. The first transaction executes:
666+
667+ <screen>
668+ UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111;
669+ </screen>
670+
671+ This acquires a row-level lock on the row with the specified
672+ account number. Then, the second transaction executes:
673+
674+ <screen>
675+ UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
676+ UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;
677+ </screen>
678+
679+ The first <command>UPDATE</command> statement successfully
680+ acquires a row-level lock on the specified row, so it succeeds in
681+ updating that row. However, the second <command>UPDATE</command>
682+ statement finds that the row it is attempting to update has
683+ already been locked, so it waits for the transaction that
684+ acquired the lock to complete. Transaction two is now waiting on
685+ transaction one to complete before it continues execution. Now,
686+ transaction one executes:
687+
688+ <screen>
689+ UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;
690+ </screen>
691+
692+ Transaction one attempts to acquire a row-level lock on the
693+ specified row, but it cannot: transaction two already holds such
694+ a lock. So it waits for transaction two to complete. Thus,
695+ transaction one is blocked on transaction two, and transaction
696+ two is blocked on transaction one: a deadlock
697+ condition. <productname>PostgreSQL</productname> will detect this
698+ situation and abort one of the transactions.
699+ </para>
700+
701+ <para>
702+ The best defense against deadlocks is generally to avoid them by
703+ being certain that all applications using a database acquire
704+ locks on multiple objects in a consistent order. That was the
705+ reason for the previous deadlock example: if both transactions
706+ had updated the rows in the same order, no deadlock would have
707+ occurred. One should also ensure that the first lock acquired on
708+ an object in a transaction is the highest mode that will be
709+ needed for that object. If it is not feasible to verify this in
710+ advance, then deadlocks may be handled on-the-fly by retrying
667711 transactions that are aborted due to deadlock.
668712 </para>
669713
@@ -822,9 +866,14 @@ ERROR: Can't serialize access due to concurrent update
822866 </para>
823867
824868 <para>
825- In short, B-tree indexes are the recommended index type for concurrent
826- applications.
827- </para>
869+ In short, B-tree indexes offer the best performance for concurrent
870+ applications; since they also have more features than hash
871+ indexes, they are the recommended index type for concurrent
872+ applications that need to index scalar data. When dealing with
873+ non-scalar data, B-trees obviously cannot be used; in that
874+ situation, application developers should be aware of the
875+ relatively poor concurrent performance of GiST and R-tree
876+ indexes.
828877 </sect1>
829878 </chapter>
830879