Re: BEGIN inside transaction should be an error

I would suggest the guy simply use the popular ADODB package for his
database abstraction layer so he can make use of its "Smart Transaction"
feature.

http://phplens.com/lens/adodb/docs-adodb.htm#ex11

<quote>
Lastly, StartTrans/CompleteTrans is nestable, and only the outermost
block is executed. In contrast, BeginTrans/CommitTrans/RollbackTrans is
NOT nestable.

$conn->StartTrans();
$conn->Execute($sql);
$conn->StartTrans(); # ignored <--------------
if (!CheckRecords()) $conn->FailTrans();
$conn->CompleteTrans(); # ignored <--------------
$conn->Execute($Sql2);
$conn->CompleteTrans();
</quote>

The commands marked "ignored" aren't really ignored, since it keeps
track of what level the transactions are nested to, and won't actually
commit the transaction until the StartTrans() calls == CompleteTrans()
calls.

Its worked great for me for many years now.

On Wed, 2006-05-10 at 06:19 +0200, Dennis Bjorklund wrote:
> Hi
>
> Yesterday I helped a guy on irc with a locking problem, he thought
> that locking in postgresql was broken. It turned out that he had a PHP
> function that he called inside his transaction and the function did BEGIN
> and COMMIT. Since BEGIN inside a transaction is just a warning what
> happend was that the inner COMMIT ended the transaction and
> released the locks. The rest of his commands ran with autocommit
> and no locks and he got broken data into the database.
>
> Could we make BEGIN fail when we already are in a transaction?
>
> Looking it up in the sql99 standard I find this:
>
> "If a <start transaction statement> statement is executed when an
> SQL-transaction is currently active, then an exception condition is
> raised: invalid transaction state - active SQL-transaction."
>
> /Dennis
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
--
Mike Benoit <ipso(at)snappymail(dot)ca>