Transaction Handling by the InterfaceThe interface aims to maximize transaction handling flexibility without sacrificing simplicity. It does so by defining transaction modes of operation and allowing the developer to choose the mode on connection. Transaction ModesThere are four transaction modes: - User
- Auto Commit
- On Modify
- Always
The transaction modes are enumerated in dbapi.sqlite3.TransactionMode. User Transaction ModeProvides the most control for the developer (user of the library) as the interface will not handle any transaction events leaving the developer the ability to handle transactionality as they see fit. - Transaction type is ignored.
- Creating statements with transaction QL (BEGIN,...) are allowed.
- Commit/Rollback does nothing and the statements are not created.
- When ROLLBACK resolution conflict is enacted, the interface does nothing.
- When a DDL statement is executed, the interface does nothing.
- With executemany, the interface does not perform any transaction logic.
Auto Commit ModeA direct derivative of the User mode and is almost identical except in one case, batch operations are performed in a transaction. - Transaction type is ignored.
- Creating statements with transaction QL (BEGIN,...) are invalid.
- Commit/Rollback does nothing and the statements created are but for use by executemany.
- When ROLLBACK resolution conflict is enacted, the interface does nothing.
- When a DDL statement is executed, the interface does nothing.
- For executemany, a transaction is created, all the iterations are executed and then the transaction is committed and the method ends.
On Modify ModeCreates a transaction on execution of an operation that modifies data. The connection starts without a transaction. - When transaction type is :
- DEFAULT : "BEGIN"
- DEFERRED : "BEGIN DEFERRED"
- IMMEDIATE : "BEGIN IMMEDIATE"
- EXCLUSIVE : "BEGIN EXCLUSIVE"
- Creating statements with transaction QL (BEGIN,...) are invalid.
- A transaction is created when a STATEMENT_MODIFY statement is executed and a transaction is not currently open.
- Commit and Rollback methods will commit and rollback transactions.
- When ROLLBACK conflict resolution is triggered, the interface does nothing.
- When a DDL statement is executed, the interface commits an open transaction before the statement is executed. A new transaction is not started.
- For executemany, if no transaction was started, then a new transaction is started. On completion of the method, the transaction is not committed or rolled back. That is for the user to do.
Always ModeA transaction is created on connection and the mode ensures that a transaction is always in effect. If the transaction is ended for any reason, a new transaction is started. - Transaction type is as per ON_MODIFY.
- Creating statements with transaction QL (BEGIN,...) are invalid.
- The transaction is created at the start of the connection.
- Commit and Rollback methods will commit and rollback transactions; a new transaction is started.
- When ROLLBACK conflict resolution is triggered, the interface creates a new transaction.
- When a DDL statement is executed, the interface commits an open transaction before the statement is executed and then creates a new transaction.
- For executemany, only if the conflict resolution causes a rollback will transaction logic be performed. In that case, a new transaction is created.
Transaction TypesThe transaction types are taken from SQLite3. - Deferred
- Immediate
- Exclusive
DeferredNo locks are acquired on the database until the database is first accessed. Thus with a deferred transaction, the BEGIN statement itself does nothing. Locks are not acquired until the first read or write operation. The first read operation against a database creates a SHARED lock and the first write operation creates a RESERVED lock. Because the acquisition of locks is deferred until they are needed, it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed. ImmediateRESERVED locks are acquired on all databases as soon as the BEGIN command is executed, without waiting for the database to be used. After a BEGIN IMMEDIATE, you are guaranteed that no other thread or process will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to read from the database, however. ExclusiveEXCLUSIVE locks are acquired on all databases. After a BEGIN EXCLUSIVE, you are guaranteed that no other thread or process will be able to read or write the database until the transaction is complete.
|
Sign in to add a comment
