2 <!DOCTYPE book PUBLIC "-//OASIS//DTD DocBook XML V4.2//EN"
3 "http://www.oasis-open.org/docbook/xml/4.2/docbookx.dtd" [
4 <!ENTITY % myents SYSTEM "entities.inc">
8 <!-- Transaction handling -->
10 <reference id="ref-transaction">
11 <title>Transaction Handling</title>
14 This section describes the interface provided by &clsql; for
15 handling database transactions. The interface allows for opening
16 transaction blocks, committing or rolling back changes made and
17 controlling autocommit behaviour.
20 In contrast to &commonsql;, &clsql;, by default, starts in
21 transaction AUTOCOMMIT mode (see <link
22 linkend="set-autocommit"><function>set-autocommit</function></link>).
23 To begin a transaction in autocommit mode, <link
24 linkend="start-transaction"><function>start-transaction</function></link>
25 has to be called explicitly.
29 <refentry id="start-transaction">
31 <refentrytitle>START-TRANSACTION</refentrytitle>
34 <refname><emphasis>Function</emphasis> <emphasis role="bold">START-TRANSACTION</emphasis></refname>
35 <refpurpose>Open a transaction block.</refpurpose>
36 <refclass>Function</refclass>
41 <function>start-transaction</function> &key <replaceable>database</replaceable> => <returnvalue>&nil;</returnvalue></synopsis>
44 <title>Arguments and Values</title>
47 <term><parameter>database</parameter></term>
50 <glossterm linkend="gloss-database-object">database
51 object</glossterm>. This will default to the value
52 of <symbol>*default-database*</symbol>.</para>
58 <title>Description</title>
59 <para>Starts a transaction block on
60 <parameter>database</parameter> which defaults to
61 <symbol>*default-database*</symbol> and which continues until
62 <function>rollback</function> or <function>commit</function> are
67 <title>Examples</title>
71 (select [*] :from [foo] :field-names nil)
77 (insert-records :into [foo] :av-pairs '(([bar] 1) ([baz] "one")))
79 (select [*] :from [foo] :field-names nil)
85 (select [*] :from [foo] :field-names nil)
90 <title>Side Effects</title>
92 Autocommit mode is disabled and if
93 <parameter>database</parameter> is currently within the scope
94 of a transaction, all commit and rollback hooks are removed
95 and the transaction level associated with
96 <parameter>database</parameter> is modified.
100 <title>Affected by</title>
106 <title>Exceptional Situations</title>
108 Signals an error of type <symbol>sql-database-error</symbol>
109 if <parameter>database</parameter> is not a database object.
113 <title>See Also</title>
115 <member><link linkend="commit"><function>commit</function></link></member>
116 <member><link linkend="rollback"><function>rollback</function></link></member>
117 <member><link linkend="in-transaction-p"><function>in-transaction-p</function></link></member>
118 <member><link linkend="set-autocommit"><function>set-autocommit</function></link></member>
119 <member><link linkend="with-transaction"><function>with-transaction</function></link></member>
125 <function>start-transaction</function> is a &clsql; extension.
130 <refentry id="commit">
132 <refentrytitle>COMMIT</refentrytitle>
135 <refname><emphasis>Function</emphasis> <emphasis role="bold">COMMIT</emphasis></refname>
136 <refpurpose>Commit modifications made in the current transaction.</refpurpose>
137 <refclass>Function</refclass>
140 <title>Syntax</title>
142 <function>commit</function> &key <replaceable>database</replaceable> => <returnvalue>&nil;</returnvalue></synopsis>
145 <title>Arguments and Values</title>
148 <term><parameter>database</parameter></term>
151 <glossterm linkend="gloss-database-object">database
152 object</glossterm>. This will default to the value
153 of <symbol>*default-database*</symbol>.</para>
159 <title>Description</title>
160 <para>If <parameter>database</parameter>, which defaults to
161 <symbol>*default-database*</symbol>, is currently within the
162 scope of a transaction, commits changes made since the
167 <title>Examples</title>
171 (select [*] :from [foo] :field-names nil)
177 (insert-records :into [foo] :av-pairs '(([bar] 1) ([baz] "one")))
179 (select [*] :from [foo] :field-names nil)
185 (select [*] :from [foo] :field-names nil)
190 <title>Side Effects</title>
192 Changes made within the scope of the current transaction are
193 committed in the underlying database and the transaction level
194 of <parameter>database</parameter> is reset.
198 <title>Affected by</title>
200 The transaction level of <parameter>database</parameter> which
201 indicates whether a transaction has been initiated by a call to
202 <function>start-transaction</function> since the last call to
203 <function>rollback</function> or <function>commit</function>.
207 <title>Exceptional Situations</title>
209 Signals an error of type <symbol>sql-database-error</symbol>
210 if <parameter>database</parameter> is not a database object. A
211 warning of type <symbol>sql-warning</symbol> is signalled if there
212 is no transaction in progress.
216 <title>See Also</title>
218 <member><link linkend="start-transaction"><function>start-transaction</function></link></member>
219 <member><link linkend="rollback"><function>rollback</function></link></member>
220 <member><link linkend="in-transaction-p"><function>in-transaction-p</function></link></member>
221 <member><link linkend="add-transaction-commit-hook"><function>add-transaction-commit-hook</function></link></member>
222 <member><link linkend="set-autocommit"><function>set-autocommit</function></link></member>
223 <member><link linkend="with-transaction"><function>with-transaction</function></link></member>
234 <refentry id="rollback">
236 <refentrytitle>ROLLBACK</refentrytitle>
239 <refname><emphasis>Function</emphasis> <emphasis role="bold">ROLLBACK</emphasis></refname>
240 <refpurpose>Roll back modifications made in the current transaction.</refpurpose>
241 <refclass>Function</refclass>
244 <title>Syntax</title>
246 <function>rollback</function> &key <replaceable>database</replaceable> => <returnvalue>&nil;</returnvalue></synopsis>
249 <title>Arguments and Values</title>
252 <term><parameter>database</parameter></term>
255 <glossterm linkend="gloss-database-object">database
256 object</glossterm>. This will default to the value
257 of <symbol>*default-database*</symbol>.</para>
263 <title>Description</title>
264 <para>If <parameter>database</parameter>, which defaults to
265 <symbol>*default-database*</symbol>, is currently within the
266 scope of a transaction, rolls back changes made since the
271 <title>Examples</title>
275 (select [*] :from [foo] :field-names nil)
281 (insert-records :into [foo] :av-pairs '(([bar] 1) ([baz] "one")))
283 (select [*] :from [foo] :field-names nil)
289 (select [*] :from [foo] :field-names nil)
294 <title>Side Effects</title>
296 Changes made within the scope of the current transaction are
297 reverted in the underlying database and the transaction level
298 of <parameter>database</parameter> is reset. </para>
301 <title>Affected by</title>
303 The transaction level of <parameter>database</parameter> which
304 indicates whether a transaction has been initiated by a call to
305 <function>start-transaction</function> since the last call to
306 <function>rollback</function> or <function>commit</function>.
310 <title>Exceptional Situations</title>
312 Signals an error of type <symbol>sql-database-error</symbol>
313 if <parameter>database</parameter> is not a database object. A
314 warning of type <symbol>sql-warning</symbol> is signalled if
315 there is no transaction in progress.
319 <title>See Also</title>
321 <member><link linkend="start-transaction"><function>start-transaction</function></link></member>
322 <member><link linkend="commit"><function>commit</function></link></member>
323 <member><link linkend="in-transaction-p"><function>in-transaction-p</function></link></member>
324 <member><link linkend="add-transaction-rollback-hook"><function>add-transaction-rollback-hook</function></link></member>
325 <member><link linkend="set-autocommit"><function>set-autocommit</function></link></member>
326 <member><link linkend="with-transaction"><function>with-transaction</function></link></member>
337 <refentry id="in-transaction-p">
339 <refentrytitle>IN-TRANSACTION-P</refentrytitle>
342 <refname><emphasis>Function</emphasis> <emphasis role="bold">IN-TRANSACTION-P</emphasis></refname>
343 <refpurpose>A predicate for testing whether a transaction is currently in progress.</refpurpose>
344 <refclass>Function</refclass>
347 <title>Syntax</title>
349 <function>in-transaction-p</function> &key <replaceable>database</replaceable> => <returnvalue>result</returnvalue></synopsis>
352 <title>Arguments and Values</title>
355 <term><parameter>database</parameter></term>
358 <glossterm linkend="gloss-database-object">database
359 object</glossterm>. This will default to the value
360 of <symbol>*default-database*</symbol>.</para>
364 <term><parameter>result</parameter></term>
366 <para>A Boolean.</para>
372 <title>Description</title>
373 <para>A predicate to test whether
374 <parameter>database</parameter>, which defaults to
375 <symbol>*default-database*</symbol>, is currently within the
376 scope of a transaction.
380 <title>Examples</title>
395 <title>Side Effects</title>
401 <title>Affected by</title>
407 <title>Exceptional Situations</title>
413 <title>See Also</title>
415 <member><link linkend="start-transaction"><function>start-transaction</function></link></member>
416 <member><link linkend="commit"><function>commit</function></link></member>
417 <member><link linkend="rollback"><function>rollback</function></link></member>
418 <member><link linkend="set-autocommit"><function>set-autocommit</function></link></member>
424 <function>in-transaction-p</function> is a &clsql; extension.
429 <refentry id="add-transaction-commit-hook">
431 <refentrytitle>ADD-TRANSACTION-COMMIT-HOOK</refentrytitle>
434 <refname><emphasis>Function</emphasis> <emphasis role="bold">ADD-TRANSACTION-COMMIT-HOOK</emphasis></refname>
435 <refpurpose>Specify hooks to be run when committing changes.</refpurpose>
436 <refclass>Function</refclass>
439 <title>Syntax</title>
441 <function>add-transaction-commit-hook</function> <replaceable>commit-hook</replaceable> &key <replaceable>database</replaceable> => <returnvalue>result</returnvalue></synopsis>
444 <title>Arguments and Values</title>
447 <term><parameter>commit-hook</parameter></term>
449 <para>A designator for a function with no required arguments.</para>
453 <term><parameter>database</parameter></term>
456 <glossterm linkend="gloss-database-object">database
457 object</glossterm>. This will default to the value
458 of <symbol>*default-database*</symbol>.</para>
462 <term><parameter>result</parameter></term>
464 <para>The list of currently defined commit hooks for
465 <parameter>database</parameter>.
472 <title>Description</title>
474 Adds <parameter>commit-hook</parameter>, which should a
475 designator for a function with no required arguments, to the
476 list of hooks run when <function>commit</function> is called
477 on <parameter>database</parameter> which defaults to
478 <symbol>*default-database*</symbol>.
482 <title>Examples</title>
486 (add-transaction-commit-hook #'(lambda () (print "Successfully committed.")))
487 => (#<Interpreted Function (LAMBDA # #) {48E2E689}>)
489 "Successfully committed."
494 <title>Side Effects</title>
496 <parameter>commit-hook</parameter> is added to the list of
497 commit hooks for <parameter>database</parameter>.
501 <title>Affected by</title>
507 <title>Exceptional Situations</title>
509 If <parameter>commit-hook</parameter> has one or more required
510 arguments, an error will be signalled when
511 <function>commit</function> is called.
515 <title>See Also</title>
517 <member><link linkend="commit"><function>commit</function></link></member>
518 <member><link linkend="rollback"><function>rollback</function></link></member>
519 <member><link linkend="add-transaction-rollback-hook"><function>add-transaction-rollback-hook</function></link></member>
520 <member><link linkend="with-transaction"><function>with-transaction</function></link></member>
522 </refsect1> <refsect1>
525 <function>add-transaction-commit-hook</function> is a &clsql; extension.
530 <refentry id="add-transaction-rollback-hook">
532 <refentrytitle>ADD-TRANSACTION-ROLLBACK-HOOK</refentrytitle>
535 <refname><emphasis>Function</emphasis> <emphasis role="bold">ADD-TRANSACTION-ROLLBACK-HOOK</emphasis></refname>
536 <refpurpose>Specify hooks to be run when rolling back changes.</refpurpose>
537 <refclass>Function</refclass>
540 <title>Syntax</title>
542 <function>add-transaction-rollback-hook</function> <replaceable>rollback-hook</replaceable> &key <replaceable>database</replaceable> => <returnvalue>result</returnvalue></synopsis>
545 <title>Arguments and Values</title>
548 <term><parameter>rollback-hook</parameter></term>
550 <para>A designator for a function with no required arguments.</para>
554 <term><parameter>database</parameter></term>
557 <glossterm linkend="gloss-database-object">database
558 object</glossterm>. This will default to the value
559 of <symbol>*default-database*</symbol>.</para>
563 <term><parameter>result</parameter></term>
565 <para>The list of currently defined rollback hooks for
566 <parameter>database</parameter>.
573 <title>Description</title>
575 Adds <parameter>rollback-hook</parameter>, which should a
576 designator for a function with no required arguments, to the
577 list of hooks run when <function>rollback</function> is called
578 on <parameter>database</parameter> which defaults to
579 <symbol>*default-database*</symbol>. </para>
582 <title>Examples</title>
586 (add-transaction-rollback-hook #'(lambda () (print "Successfully rolled back.")))
587 => (#<Interpreted Function (LAMBDA # #) {48E37C31}>)
589 "Successfully rolled back."
594 <title>Side Effects</title>
596 <parameter>rollback-hook</parameter> is added to the list of
597 rollback hooks for <parameter>database</parameter>.
601 <title>Affected by</title>
607 <title>Exceptional Situations</title>
609 If <parameter>rollback-hook</parameter> has one or more
610 required arguments, an error will be signalled when
611 <function>rollback</function> is called.
615 <title>See Also</title>
617 <member><link linkend="commit"><function>commit</function></link></member>
618 <member><link linkend="rollback"><function>rollback</function></link></member>
619 <member><link linkend="add-transaction-commit-hook"><function>add-transaction-commit-hook</function></link></member>
625 <function>add-transaction-rollback-hook</function> is a &clsql; extension.
630 <refentry id="set-autocommit">
632 <refentrytitle>SET-AUTOCOMMIT</refentrytitle>
635 <refname><emphasis>Function</emphasis> <emphasis role="bold">SET-AUTOCOMMIT</emphasis></refname>
636 <refpurpose>Turn on or off autocommit for a database.</refpurpose>
637 <refclass>Function</refclass>
640 <title>Syntax</title>
642 <function>set-autocommit</function> <replaceable>value</replaceable> &key <replaceable>database</replaceable> => <returnvalue>result</returnvalue></synopsis>
645 <title>Arguments and Values</title>
648 <term><parameter>value</parameter></term>
650 <para>A Boolean specifying the desired autocommit
651 behaviour for <parameter>database</parameter>.
656 <term><parameter>database</parameter></term>
659 <glossterm linkend="gloss-database-object">database
660 object</glossterm>. This will default to the value
661 of <symbol>*default-database*</symbol>.</para>
665 <term><parameter>result</parameter></term>
667 <para>The previous autocommit value for
668 <parameter>database</parameter>.
675 <title>Description</title>
676 <para>Turns autocommit off for <parameter>database</parameter>
677 if <parameter>value</parameter> is &nil;, and otherwise turns it
678 on. Returns the old value of autocommit flag.
681 For RDBMS (such as Oracle) which don't automatically commit
682 changes, turning autocommit on has the effect of explicitly
683 committing changes made whenever SQL statements are executed.
686 Autocommit is turned on by default.
690 <title>Examples</title>
696 <title>Side Effects</title>
698 <parameter>database</parameter> is associated with the specified
703 <title>Affected by</title>
709 <title>Exceptional Situations</title>
715 <title>See Also</title>
717 <member><link linkend="start-transaction"><function>start-transaction</function></link></member>
718 <member><link linkend="commit"><function>commit</function></link></member>
719 <member><link linkend="add-transaction-commit-hook"><function>add-transaction-commit-hook</function></link></member>
720 <member><link linkend="with-transaction"><function>with-transaction</function></link></member>
726 <function>set-autocommit</function> is a &clsql; extension.
731 <refentry id="with-transaction">
733 <refentrytitle>WITH-TRANSACTION</refentrytitle>
736 <refname><emphasis>Macro</emphasis> <emphasis role="bold">WITH-TRANSACTION</emphasis></refname>
737 <refpurpose>Execute a body of code within a transaction.</refpurpose>
738 <refclass>Macro</refclass>
741 <title>Syntax</title>
743 <function>with-transaction</function> &key <replaceable>database</replaceable> &rest <replaceable>body</replaceable> => <returnvalue>result</returnvalue></synopsis>
746 <title>Arguments and Values</title>
749 <term><parameter>database</parameter></term>
752 <glossterm linkend="gloss-database-object">database
753 object</glossterm>. This will default to the value
754 of <symbol>*default-database*</symbol>.</para>
757 <term><parameter>body</parameter></term>
765 <term><parameter>result</parameter></term>
767 <para>The result of executing <parameter>body</parameter>.</para>
774 <title>Description</title>
775 <para>Starts a transaction in the database specified by
776 <parameter>database</parameter>, which is
777 <symbol>*default-database*</symbol> by default, and executes
778 <parameter>body</parameter> within that transaction. If
779 <parameter>body</parameter> aborts or throws,
780 <parameter>database</parameter> is rolled back and otherwise the
781 transaction is committed.
785 <title>Examples</title>
789 (select [email] :from [employee] :where [= [emplid] 1] :flatp t :field-names nil)
790 => ("lenin@soviet.org")
792 (update-records [employee]
793 :av-pairs '((email "lenin-nospam@soviet.org"))
794 :where [= [emplid] 1]))
796 (select [email] :from [employee] :where [= [emplid] 1] :flatp t :field-names nil)
797 => ("lenin-nospam@soviet.org")
803 <title>Side Effects</title>
805 Changes specified in <parameter>body</parameter> may be made
806 to the underlying database if <parameter>body</parameter>
807 completes successfully.
811 <title>Affected by</title>
817 <title>Exceptional Situations</title>
819 Signals an error of type <symbol>sql-database-error</symbol>
820 if <parameter>database</parameter> is not a database object.
824 <title>See Also</title>
826 <member><link linkend="start-transaction"><function>start-transaction</function></link></member>
827 <member><link linkend="commit"><function>commit</function></link></member>
828 <member><link linkend="rollback"><function>rollback</function></link></member>
829 <member><link linkend="add-transaction-commit-hook"><function>add-transaction-commit-hook</function></link></member>
830 <member><link linkend="add-transaction-rollback-hook"><function>add-transaction-rollback-hook</function></link></member>