Thread: order of database modifications in a single transaction

Can it be assumed that the database will try to commit all the updates,
deletes, and inserts in the order they are submitted during a single
transaction for the sake of planning trigger firing order? In the
following scenarios?

    A/ From within a procedure run from the command shell or a external
script.
    B/ From withing a procedure from the command line.
    C/ with Autocommit off and within Begin and end blocks of a
transaction.

Is this a general assumption possible with all transaction supporting
databases, that at the moment of commitment a single transaction, the
database modifications in that transactions will be popped from a queue
FIFO and committed individually in order?

Also, when one transaction is committing does it own that bottleneck
blocking other transactions from committing until the whole queue of DB
modifications in the current committing transaction are done? I.E. it
gets a semaphore for the commitment functionality? ( I guess I could
have worded that to say, is the commitment phase of a transaction atomic
relative to other pending transactions? )
--

Carpe Dancem ;-)
-----------------------------------------------------------------
Remember your friends while they are alive
-----------------------------------------------------------------
                         Sincerely, Dennis Gearon

No one going to bite on this? Any developers have any idea what goes on in the code for
committment?

2/23/2003 10:44:49 PM, Dennis Gearon <gearond@cvc.net> wrote:

>Can it be assumed that the database will try to commit all the updates,
>deletes, and inserts in the order they are submitted during a single
>transaction for the sake of planning trigger firing order? In the
>following scenarios?
>
>    A/ From within a procedure run from the command shell or a external
>script.
>    B/ From withing a procedure from the command line.
>    C/ with Autocommit off and within Begin and end blocks of a
>transaction.
>
>Is this a general assumption possible with all transaction supporting
>databases, that at the moment of commitment a single transaction, the
>database modifications in that transactions will be popped from a queue
>FIFO and committed individually in order?
>
>Also, when one transaction is committing does it own that bottleneck
>blocking other transactions from committing until the whole queue of DB
>modifications in the current committing transaction are done? I.E. it
>gets a semaphore for the commitment functionality? ( I guess I could
>have worded that to say, is the commitment phase of a transaction atomic
>relative to other pending transactions? )
>--
>
>Carpe Dancem ;-)
>-----------------------------------------------------------------
>Remember your friends while they are alive
>-----------------------------------------------------------------
>                         Sincerely, Dennis Gearon
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Dennis Gearon <gearond@cvc.net> writes:
> No one going to bite on this? Any developers have any idea what goes
> on in the code for committment?

Knowing what the code does today is not the same thing as wanting to
promise that it will always do that.  In general, writing an application
that depends on trigger firing order is bad application design IMHO.

            regards, tom lane