4D v13.4
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
Product: 4D
Theme: Transactions
