4D Doc Center : Transactions

Home

4D v14

Description

Transactions are a set of SQL statements that are executed together. Either all of them are successful or they have no effect. Transactions use locks to preserve data integrity during their execution. If the transaction finishes successfully, you can use the COMMIT statement to permanently store its modifications. Otherwise, using the ROLLBACK statement will cancel any modifications and restore the database to its previous state.

There is no difference between a 4D transaction and an SQL transaction. Both types share the same data and process. SQL statements passed between Begin SQL/End SQL tags, the QUERY BY SQL and the integrated generic SQL commands applied to the local database are always executed in the same context as standard 4D commands.

Note: 4D provides an "Auto-commit" option which can be used to start and validate transactions automatically when using SIUD commands (SELECT, , UPDATE and DELETE) in order to preserve data integrity. For more information, please refer to the Principles for integrating 4D and the 4D SQL engine section.

The following examples illustrate the different combinations of transactions.

Neither “John” nor “Smith” will be added to the emp table:

 SQL LOGIN(SQL_INTERNAL;"";"") `Initializes the 4D SQL engine

 START TRANSACTION `Starts a transaction in the current process

 Begin SQL

    INSERT INTO emp

    (NAME)

    VALUES ('John');

 End SQL

 SQL EXECUTE("START") `Another transaction in the current process

 SQL CANCEL LOAD

 SQL EXECUTE("INSERT INTO emp (NAME) VALUES ('Smith')") `This statement is executed in the same process

 SQL CANCEL LOAD

 SQL EXECUTE("ROLLBACK")<gen9> `Cancels internal transaction of the pro-cess

 CANCEL TRANSACTION

  `Cancels external transaction of the process

 SQL LOGOUT</gen9>

Only “John” will be added to the emp table:

 SQL LOGIN(SQL_INTERNAL;"";"")

 START TRANSACTION

 Begin SQL

    INSERT INTO emp

    (NAME)

    VALUES ('John');

 End SQL

 SQL EXECUTE("START")

 SQL CANCEL LOAD

 SQL EXECUTE("INSERT INTO emp (NAME) VALUES ('Smith')")

 SQL CANCEL LOAD

 SQL EXECUTE("ROLLBACK")<gen9> `Cancels internal transaction of the pro-cess

 VALIDATE TRANSACTION `Validates external transaction of the process

 SQL LOGOUT</gen9>

Neither “John” nor “Smith” will be added to the emp table. The exter-nal transaction cancels the internal transaction:

 SQL LOGIN(SQL_INTERNAL;"";"")

 START TRANSACTION

 Begin SQL

    INSERT INTO emp

    (NAME)

    VALUES ('John');

 End SQL

 SQL EXECUTE("START")

 SQL CANCEL LOAD

 SQL EXECUTE("INSERT INTO emp (NAME) VALUES ('Smith')")

 SQL CANCEL LOAD

 SQL EXECUTE("COMMIT") `Validates internal transaction of the process

 CANCEL TRANSACTION `Cancels external transaction of the process

 SQL LOGOUT

“John” and “Smith” will be added to the emp table:

 SQL LOGIN(SQL_INTERNAL;"";"")

 START TRANSACTION

 Begin SQL

    INSERT INTO emp

    (NAME)

    VALUES ('John');

 End SQL

 SQL EXECUTE("START")

 SQL CANCEL LOAD

 SQL EXECUTE("INSERT INTO emp (NAME) VALUES ('Smith')")

 SQL CANCEL LOAD

 SQL EXECUTE("COMMIT") `Validates internal transaction of the process

 VALIDATE TRANSACTION `Validates external transaction of the process

 SQL LOGOUT