1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
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="Advanced Features"
16 HREF="tutorial-advanced.html"><LINK
19 HREF="tutorial-fk.html"><LINK
22 HREF="tutorial-inheritance.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
51 HREF="tutorial-fk.html"
60 HREF="tutorial-advanced.html"
67 >Chapter 3. Advanced Features</TD
73 HREF="tutorial-advanced.html"
81 HREF="tutorial-inheritance.html"
95 NAME="TUTORIAL-TRANSACTIONS"
105 > are a fundamental concept of all database
106 systems. The essential point of a transaction is that it bundles
107 multiple steps into a single, all-or-nothing operation. The intermediate
108 states between the steps are not visible to other concurrent transactions,
109 and if some failure occurs that prevents the transaction from completing,
110 then none of the steps affect the database at all.
113 > For example, consider a bank database that contains balances for various
114 customer accounts, as well as total deposit balances for branches.
115 Suppose that we want to record a payment of $100.00 from Alice's account
116 to Bob's account. Simplifying outrageously, the SQL commands for this
120 CLASS="PROGRAMLISTING"
121 >UPDATE accounts SET balance = balance - 100.00
122 WHERE name = 'Alice';
123 UPDATE branches SET balance = balance - 100.00
124 WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
125 UPDATE accounts SET balance = balance + 100.00
127 UPDATE branches SET balance = balance + 100.00
128 WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');</PRE
132 > The details of these commands are not important here; the important
133 point is that there are several separate updates involved to accomplish
134 this rather simple operation. Our bank's officers will want to be
135 assured that either all these updates happen, or none of them happen.
136 It would certainly not do for a system failure to result in Bob
137 receiving $100.00 that was not debited from Alice. Nor would Alice long
138 remain a happy customer if she was debited without Bob being credited.
139 We need a guarantee that if something goes wrong partway through the
140 operation, none of the steps executed so far will take effect. Grouping
141 the updates into a <I
144 > gives us this guarantee.
145 A transaction is said to be <I
149 view of other transactions, it either happens completely or not at all.
153 guarantee that once a transaction is completed and acknowledged by
154 the database system, it has indeed been permanently recorded
155 and won't be lost even if a crash ensues shortly thereafter.
156 For example, if we are recording a cash withdrawal by Bob,
157 we do not want any chance that the debit to his account will
158 disappear in a crash just as he walks out the bank door.
159 A transactional database guarantees that all the updates made by
160 a transaction are logged in permanent storage (i.e., on disk) before
161 the transaction is reported complete.
164 > Another important property of transactional databases is closely
165 related to the notion of atomic updates: when multiple transactions
166 are running concurrently, each one should not be able to see the
167 incomplete changes made by others. For example, if one transaction
168 is busy totalling all the branch balances, it would not do for it
169 to include the debit from Alice's branch but not the credit to
170 Bob's branch, nor vice versa. So transactions must be all-or-nothing
171 not only in terms of their permanent effect on the database, but
172 also in terms of their visibility as they happen. The updates made
173 so far by an open transaction are invisible to other transactions
174 until the transaction completes, whereupon all the updates become
175 visible simultaneously.
181 >, a transaction is set up by surrounding
182 the SQL commands of the transaction with
189 > commands. So our banking
190 transaction would actually look like
193 CLASS="PROGRAMLISTING"
195 UPDATE accounts SET balance = balance - 100.00
196 WHERE name = 'Alice';
202 > If, partway through the transaction, we decide we do not want to
203 commit (perhaps we just noticed that Alice's balance went negative),
204 we can issue the command <TT
211 >, and all our updates so far will be canceled.
217 > actually treats every SQL statement as being
218 executed within a transaction. If you do not issue a <TT
223 then each individual statement has an implicit <TT
230 > wrapped around it. A group of
231 statements surrounded by <TT
238 is sometimes called a <I
240 >transaction block</I
250 > Some client libraries issue <TT
257 commands automatically, so that you may get the effect of transaction
258 blocks without asking. Check the documentation for the interface
269 SUMMARY="Footer navigation table"
280 HREF="tutorial-fk.html"
298 HREF="tutorial-inheritance.html"
314 HREF="tutorial-advanced.html"
OSDN Git Service
