Updates that occurred in the database are not permanently stored until the COMMIT WORK statement is executed. "Permanently stored" means that storing the updates in the disk is completed; The WORK keyword can be omitted. In addition, other users of the database cannot see the updates until they are permanently applied. For example, when a new row is inserted into a class, only the user who inserted the row can access it until the database transaction is committed. (If the UNCOMMITTED INSTANCES isolation level is used, other users can see inconsistent uncommitted updates.)
All locks obtained by the transaction are released after the transaction is committed.
COMMIT [ WORK ]
The database transaction in the following example consists of three UPDATE statements and changes three column values of seats from the stadium. To compare the results, check the current values and names before the update is made. Since, by default, csql runs in an autocommit mode, the following example is executed after setting the autocommit mode to off.
AUTOCOMMIT IS OFF
SELECT name, seats
FROM stadium WHERE code IN (30138, 30139, 30140);
'Athens Olympic Tennis Centre' 3200
'Goudi Olympic Hall' 5000
'Vouliagmeni Olympic Centre' 3400
Let each UPDATE statement have the current seats of each stadium. To verify whether the command is correctly executed, you can retrieve the columns related to the seats table.
SET seats = seats + 1000
WHERE code IN (30138, 30139, 30140);
SELECT name, seats FROM stadium WHERE code in (30138, 30139, 30140);
'Athens Olympic Tennis Centre' 4200
'Goudi Olympic Hall' 6000
'Vouliagmeni Olympic Centre' 4400
If the update is properly done, the changes can be semi-permentanetly fixed. In this time, use the COMMIT WORK as below:
Note In CUBRID, an auto-commit mode is set by default for transaction management.
An auto-commit mode is a mode that commits or rolls back all SQL statements. The transaction is committed automatically if the SQL is executed successfully, or is rolled back automatically if an error occurs.Such auto commit modes are supported in any interfaces.
In CCI, PHP, ODBC and OLE DB interfaces, you can configure auto-commit mode by using CCI_DEFAULT_AUTOCOMMIT upon startup of an application. If configuration on broker parameter is omitted, the default value is set to ON. To change auto-commit mode, use the following functions by interface: cci_set_autocommit() for CCI inferface and cubrid_set_autocommit() for PHP interface.
For session command (;AUtocommit) which enables auto-commit configuration in CSQL Interpreter, see Session Commands.