Python Data Persistence - Transaction Control

Python Data Persistence – Transaction Control

As mentioned above, SQLite is a transactional database and all transactions are ACID compliant. ACID stands for Atomic, Consistent, Isolated, and Durable. As a result, it ensures that the SQLite database doesn’t lose integrity, even if transaction such as INSERT, DELETE, or UPDATE, is interrupted because of any reason whatsoever.

A transaction is the propagation of changes to the database. The operation performed by INSERT, UPDATE, or DELETE statement results in a transaction.

Atomicity: When we say that a transaction should be atomic, it means that a change cannot be affected in parts. Either the entire transaction is applied or not applied.

Consistency: After any transaction is completed, the database should hold on to the changes in its state.
Isolation: It must be ensured that the transaction such as INSERT, UPDATE, or DELETE, performed by a client should only be visible to other clients after successful completion.

Durability: The result of successfully committed transactions must be permanent in the database regardless of the condition such as power failure or program crash.

SQLite provides two statements for transaction control. They are COMMIT and ROLLBACK. All CRUD (CREATE. RETRIEVE, UPDATE] and DELETE) operations first take effect in memory, and then they are permanently saved (committed) to the disk file. SQLite transactions are automatically committed without giving any chance to undo (roll back) the changes.
To control the commitment and rolling back manually, start transactions after issuing the directive BEGIN TRANSACTION. Whatever operations are done thereafter will not be confirmed, until COMMIT is issued and will be annulled if ROLLBACK is issued.

sqlite> select * from products;
productID      Name         price
----------     --------        -------
      1            Laptop       27500
      3           Router         2200
      4          Scanner        5500
      5          printer        11000
      6          Mobile        16500
sqlite> begin transaction;
sqlite> update products set price=2000 where name = 'Router';
sqlite> select * from products;
productID         Name         price
----------        --------        -------
      1               Laptop        27500
      3               Router        2200
      4              Scanner       5500
      5              printer         11000
     6               Mobile        16500
sqlite> rollback;
productID      Name        price
----------       --------     -------
      1             Laptop      27500
      3             Router       2200
      4             Scanner      5500
      5             printer        11000
      6             Mobile       16500

In the above example, the price of ‘ Router ‘ is initially 2200. It was changed to 2000 but rolled back. Hence its earlier value is restored. The following example shows the effect of the commit statement where the effect of the UPDATE statement is confirmed.

sqlite> begin transaction;
sqlite> update products set price=3000 where name='Router'; 
sqlite> commit;
sqlite> select * from products; 
productID        Name           price
----------          --------        -------
     1                 Laptop         27500
     3                 Router          2200
     4                 Scanner        5500
     5                 printer         11000
     6                Mobile          16500

Python Data Persistence – Transaction Control

Related