Skip to content

Commit 0dfe913

Browse files
tglsfdctglsfdc
committed
Fill in empty tutorial section about transactions.
1 parent 09634ea commit 0dfe913

File tree

1 file changed

+104
-2
lines changed

1 file changed

+104
-2
lines changed

‎doc/src/sgml/advanced.sgml

Lines changed: 104 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/advanced.sgml,v 1.23 2001/11/19 05:37:53 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/advanced.sgml,v 1.24 2001/11/19 23:17:38 tgl Exp $
33
-->
44

55
<chapter id="tutorial-advanced">
@@ -143,11 +143,113 @@ ERROR: &lt;unnamed&gt; referential integrity violation - key referenced from we
143143
<sect1 id="tutorial-transactions">
144144
<title>Transactions</title>
145145

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>
147216

148217
<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>
149234

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</>.
150243
</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>
151253
</sect1>
152254

153255

0 commit comments

Comments
 (0)