4D v14

Transactions

Home

 
4D v14
Transactions

Transactions  


 

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

 
PROPERTIES 

Product: 4D
Theme: Transactions

 
ARTICLE USAGE

4D SQL Reference ( 4D v11 SQL Release 4 )
4D SQL Reference ( 4D v14)
4D SQL Reference ( 4D v12.1)
4D SQL Reference ( 4D v13.4)
4D SQL Reference ( 4D v14 R2)
4D SQL Reference ( 4D v14 R3)
4D SQL Reference ( 4D v14 R4)