Re: start of transaction (was: Re: [PERFORM] Help with

On Sun, 17 Nov 2003, Greg Stark wrote:

> Neil Conway <neilc(at)samurai(dot)com> writes:
>
> > What does BEGIN actually do now, from a user's perspective?
>
> I think you're thinking about this all wrong. BEGIN doesn't "do" anything.
> It's not a procedural statement, it's a declaration. It declares that the
> block of statements form a transaction so reads should be consistent and
> failures should be handled in a particular way to preserve data integrity.
>
> Given that declaration and the guarantees it requires of the database it's
> then up to the database to figure out what constraints that imposes on what
> the database can do and still meet the guarantees the BEGIN declaration
> requires. The more clever the database is about minimizing those restrictions
> the better as it means the database can run more efficiently.
>
> For what it's worth, this is how Oracle handles things too. On the
> command-line issuing a BEGIN following a COMMIT is just noise; you're _always_
> in a transaction. A COMMIT ends the previous the transaction and implicitly
> starts the next transaction. But the snapshot isn't frozen until you first
> read from a table.

The earlier portion of the described behavior is AFAICS not complient to
SQL99 at least. COMMIT (without AND CHAIN) terminates a transaction and
does not begin a new one. The new transaction does not begin until a
transaction initiating command (for example START TRANSACTION, CREATE
TABLE, INSERT, ...) is executed. The set of things you can do that aren't
initiating is fairly small admittedly, but it's not a null set.