OceanBase Database supports statement-level atomicity, which means that a statement is an atomic unit of work and either completely succeeds or completely fails.
If a statement is executed without an error, it is successful. If a statement causes an error during execution, it is not successful and operations of the statement are rolled back, which is called a statement-level rollback. A statement-level rollback has the following features:
A statement-level rollback affects only the operations of the current statement. Changes made by other statements earlier in the transaction are not affected.
Assume that a transaction contains two
UPDATEstatements. The firstUPDATEstatement is successfully executed but the secondUPDATEstatement fails to be executed. In this case, the secondUPDATEstatement is rolled back, and changes made by the firstUPDATEstatement are retained.The effect of the rollback is as if the statement had never been executed. All the global indexes, triggers, and row locks involved in the statement can be considered as operations of the statement and must be rolled back to the state before the statement is started.
Statement-level rollbacks often occur in the following scenarios:
A duplicate primary key is to be inserted.
The execution of a single statement times out.
Multiple transactions are involved in a deadlock due to row lock conflicts, and one of the transactions is killed by the deadlock detection mechanism.
However, errors in syntax parsing of SQL statements do not cause a statement-level rollback because the statement has not changed any data.