BackUp LevelNext

Transaction Processing (CFTRANSACTION)

You can use the CFTRANSACTION tag to maintain consistency across queries. All queries contained within a CFTRANSACTION tag are treated as a transactional unit. This means that changes made to the database are not permanently committed until all queries in the transaction block execute successfully. If an error occurs in one of the queries, all changes made by previous queries within the transaction block are rolled back.

Syntax and example

The following example illustrates the use of CFTRANSACTION. If an error occurs in the second query, CFTRANSACTION guarantees that a transfer of account funds does not leave the database in an inconsistent state:

<CFTRANSACTION>

<CFQUERY NAME="WithdrawCash"
    DATASOURCE="BankDB">
    UPDATE Accounts
    SET Balance=Balance - #Amount#
        WHERE Account_ID=#AccountFrom#
</CFQUERY>

<CFQUERY NAME="DepositCash"
    DATASOURCE="BankDB">

    UPDATE Accounts
    SET Balance=Balance + #Amount#
        WHERE Account_ID=#AccountTo#

</CFQUERY>

</CFTRANSACTION>

If an error occurs during the execution of the "DepositCash" query, the update made in the "WithdrawCash" query is automatically rolled back. Transactions are only supported for the same datasource.

Setting transaction isolation

You can specify an optional ISOLATION attribute with the CFTRANSACTION tag. The ISOLATION attribute provides fine-grained control over how the database engine performs locking during the transaction.

Valid values for the ISOLATION attribute are:

Note

VERSIONING is no longer a valid value for this attribute and is no longer supported in ColdFusion.

ODBC driver support for transactions

Not every driver supports transactions and not every driver that supports transactions supports all ISOLATION levels. When you attempt to use a transaction/isolation-level combination for a specific driver, ColdFusion queries the driver for its transaction capabilities. ColdFusion returns an error if the driver indicates it is not capable of implementing the request. Consult your driver's documentation for more information on the ISOLATION levels it supports and on the behavior of the driver for each level.


BackUp LevelNext

allaire

AllaireDoc@allaire.com
Copyright © 1998, Allaire Corporation. All rights reserved.