|
1 | 1 | <!-- |
2 | | -$Header: /cvsroot/pgsql/doc/src/sgml/advanced.sgml,v 1.23 2001/11/1905:37:53 tgl Exp $ |
| 2 | +$Header: /cvsroot/pgsql/doc/src/sgml/advanced.sgml,v 1.24 2001/11/1923:17:38 tgl Exp $ |
3 | 3 | --> |
4 | 4 |
|
5 | 5 | <chapter id="tutorial-advanced"> |
@@ -143,11 +143,113 @@ ERROR: <unnamed> referential integrity violation - key referenced from we |
143 | 143 | <sect1 id="tutorial-transactions"> |
144 | 144 | <title>Transactions</title> |
145 | 145 |
|
146 | | - <comment>This section needs to be written.</comment> |
| 146 | + <indexterm zone="tutorial-transactions"> |
| 147 | + <primary>transactions</primary> |
| 148 | + </indexterm> |
| 149 | + |
| 150 | + <para> |
| 151 | + <firstterm>Transactions</> are a fundamental concept of all database |
| 152 | + systems. The essential point of a transaction is that it bundles |
| 153 | + multiple steps into a single, all-or-nothing operation. The intermediate |
| 154 | + states between the steps are not visible to other concurrent transactions, |
| 155 | + and if some failure occurs that prevents the transaction from completing, |
| 156 | + then none of the steps affect the database at all. |
| 157 | + </para> |
| 158 | + |
| 159 | + <para> |
| 160 | + For example, consider a bank database that contains balances for various |
| 161 | + customer accounts, as well as total deposit balances for branches. |
| 162 | + Suppose that we want to record a payment of $100.00 from Alice's account |
| 163 | + to Bob's account. Simplifying outrageously, the SQL commands for this |
| 164 | + might look like |
| 165 | +<programlisting> |
| 166 | +UPDATE accounts SET balance = balance - 100.00 |
| 167 | + WHERE name = 'Alice'; |
| 168 | +UPDATE branches SET balance = balance - 100.00 |
| 169 | + WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice'); |
| 170 | +UPDATE accounts SET balance = balance + 100.00 |
| 171 | + WHERE name = 'Bob'; |
| 172 | +UPDATE branches SET balance = balance + 100.00 |
| 173 | + WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob'); |
| 174 | +</programlisting> |
| 175 | + The details of these commands are not important here; the important |
| 176 | + point is that there are several separate updates involved to accomplish |
| 177 | + this rather simple operation. Our bank's officers will want to be |
| 178 | + assured that either all these updates happen, or none of them happen. |
| 179 | + It would certainly not do for a system failure to result in Bob |
| 180 | + receiving $100.00 that was not debited from Alice. Nor would Alice long |
| 181 | + remain a happy customer if she was debited without Bob being credited. |
| 182 | + We need a guarantee that if something goes wrong partway through the |
| 183 | + operation, none of the steps executed so far will take effect. Grouping |
| 184 | + the updates into a <firstterm>transaction</> gives us this guarantee. |
| 185 | + A transaction is said to be <firstterm>atomic</>: from the point of |
| 186 | + view of other transactions, it either happens completely or not at all. |
| 187 | + </para> |
| 188 | + |
| 189 | + <para> |
| 190 | + We also want a |
| 191 | + guarantee that once a transaction is completed and acknowledged by |
| 192 | + the database system, it has indeed been permanently recorded |
| 193 | + and won't be lost even if a crash ensues shortly thereafter. |
| 194 | + For example, if we are recording a cash withdrawal by Bob, |
| 195 | + we do not want any chance that the debit to his account will |
| 196 | + disappear in a crash just as he walks out the bank door. |
| 197 | + A transactional database guarantees that all the updates made by |
| 198 | + a transaction are logged in permanent storage (i.e., on disk) before |
| 199 | + the transaction is reported complete. |
| 200 | + </para> |
| 201 | + |
| 202 | + <para> |
| 203 | + Another important property of transactional databases is closely |
| 204 | + related to the notion of atomic updates: when multiple transactions |
| 205 | + are running concurrently, each one should not be able to see the |
| 206 | + incomplete changes made by others. For example, if one transaction |
| 207 | + is busy totalling all the branch balances, it would not do for it |
| 208 | + to include the debit from Alice's branch but not the credit to |
| 209 | + Bob's branch, nor vice versa. So transactions must be all-or-nothing |
| 210 | + not only in terms of their permanent effect on the database, but |
| 211 | + also in terms of their visibility as they happen. The updates made |
| 212 | + so far by an open transaction are invisible to other transactions |
| 213 | + until the transaction completes, whereupon all the updates become |
| 214 | + visible simultaneously. |
| 215 | + </para> |
147 | 216 |
|
148 | 217 | <para> |
| 218 | + In <productname>Postgres</>, a transaction is set up by surrounding |
| 219 | + the SQL commands of the transaction with |
| 220 | + <command>BEGIN</> and <command>COMMIT</> commands. So our banking |
| 221 | + transaction would actually look like |
| 222 | +<programlisting> |
| 223 | +BEGIN; |
| 224 | +UPDATE accounts SET balance = balance - 100.00 |
| 225 | + WHERE name = 'Alice'; |
| 226 | +-- etc etc |
| 227 | +COMMIT; |
| 228 | +</programlisting> |
| 229 | + If, partway through the transaction, we decide we don't want to |
| 230 | + commit (perhaps we just noticed that Alice's balance went negative), |
| 231 | + we can issue the command <command>ROLLBACK</> instead of |
| 232 | + <command>COMMIT</>, and all our updates so far will be canceled. |
| 233 | + </para> |
149 | 234 |
|
| 235 | + <para> |
| 236 | + <productname>Postgres</> actually treats every SQL statement as being |
| 237 | + executed within a transaction. If you don't issue a <command>BEGIN</> |
| 238 | + command, |
| 239 | + then each individual statement has an implicit <command>BEGIN</> and |
| 240 | + (if successful) <command>COMMIT</> wrapped around it. A group of |
| 241 | + statements surrounded by <command>BEGIN</> and <command>COMMIT</> |
| 242 | + is sometimes called a <firstterm>transaction block</>. |
150 | 243 | </para> |
| 244 | + |
| 245 | + <note> |
| 246 | + <para> |
| 247 | + Some client libraries issue <command>BEGIN</> and <command>COMMIT</> |
| 248 | + commands automatically, so that you may get the effect of transaction |
| 249 | + blocks without asking. Check the documentation for the interface |
| 250 | + you are using. |
| 251 | + </para> |
| 252 | + </note> |
151 | 253 | </sect1> |
152 | 254 |
|
153 | 255 |
|
|