Transaction in SQLite: how several changes become one operation

Useful to know

One task in the application may require several changes to the database. If they are related, their partial performance may leave an undesirable condition. The transaction allows these changes to be viewed as a common operation. Upon confirmation with COMMIT, they are retained, and upon cancellation with ROLLBACK they are cancelled according to the rules of the base. In SQLite, the BEGIN command can explicitly initiate a transaction that covers subsequent work until its completion. [1]

Atomicity means that changes in a transaction are manifested as having been made entirely or not done. SQLite describes the mechanisms by which it provides such behavior when recording data. [2] It is a guarantee of the organization of the operation, not an assessment of whether the values themselves are correct. If the application successfully confirms a wrong price, atomicity will not choose another price instead. The correctness of the rules and the non-allowance of a partial record are different parts of the reliable work.

Let's have a fictional system for enrolling participants in a workshop. Upon confirmation, an enrollment must be created and the number of vacancies must be updated. Before programming, it is useful to describe the state that is permissible after the operation. Both records must match the same solution. This example shows why the transaction boundary should follow the meaning of the task, rather than arbitrarily covering only the convenient-to-write query.

SQLite allows multiple simultaneous read transactions from separate connections, but only one simultaneous write transaction. There are different ways to start, including DEFERRED and IMMEDIATE, with different timing of acquiring the necessary recording capability. [1] This is important for applications that process competing actions. It is not enough to add the word transaction and assume that any situation of simultaneous access already has appropriate behavior.

A practical test can cause failure after the first of two related changes. After completion, we check the base, not just the displayed message. Is the first recording left? Has the number of seats changed? Then we test the successful scenario and retry. This checks the specific contract of the application: which changes belong together and what condition should remain at each selected outcome of the operation.

The transaction in the base does not automatically describe the fate of all external actions, for example, a message already sent. Therefore, when designing, it is useful to list the records and the other effects separately. For each of them, it is determined when it is permissible to occur and how it is reacted to failure. Atomicity is a strong mechanism for associated base changes when the boundaries of the operation are clear and the tests verify the actual final state.