Transactions
A transaction defines the boundary of a group of SQL commands. This group of commands can be fully or not executed as a whole. A typical transaction instance is transfer.
Transaction Scope
Transactions are controlled by three commands: Begin, commit, and rollback. Begin starts a transaction, and all subsequent operations can be canceled. Commit confirms all commands after begin, and rollback restores all operations after begin. For example:
SQLite> begin;
SQLite> Delete from foods;
SQLite> rollback;
SQLite> select count (*) from foods;
Count (*)
412
A transaction is started. All rows in the foods table are deleted first, but rollback is used for rollback. When the SELECT statement is executed, no changes are found in the table.
By default, each SQL statement is a self-contained transaction (automatic commit mode ).
Conflict Resolution
As described above, violation of the constraints will lead to the illegal completion of the transaction. Most databases (Management Systems) simply cancel all previous modifications.
SQLite has its own unique method to handle constraints (or recover from constraints), known as conflict resolution.
For example:
SQLite & gt; update foods Set ID = 800-ID;
SQL error: primary key must be unique
SQLite provides five conflict solutions: replace, ignore, fail, abort, and rollback.
Replace: when sending a message that violates the unique integrity, SQLite will delete this violation record, replacing the new record with new inserts or modifications. The SQL statement continues to be executed and no error is reported.
Ignore
Fail
Abort
Rollback
Database lock
In SQLite, locks are closely related to transactions. To effectively use transactions, you need to know some knowledge about how to lock them.
SQLite adopts extensive locks. When a connection needs to write data to the database, all other connections are locked until the write connection ends its transactions. SQLite has a lock table to help different write databases lock at the last moment to ensure maximum concurrency.
SQLite uses the lock escalation mechanism. In order to write the database, the connection needs to obtain the exclusive lock step by step. SQLite has five different lock states: unlocked, shared, reserved, pending, and exclusive ). Each database connection can only be in one of the statuses at the same time. Each status (except the unlocked status) corresponds to a lock.
The initial status is unlocked. In this status, the connection has not accessed the database. When you connect to a database or even start a transaction with begin, the connection is still unlocked.
The next status in the unlocked status is the shared status. In order to be able to read (not write) data from the database, the connection must first enter the sharing state, that is, first obtain a shared lock. Multiple connections can obtain and maintain a shared lock at the same time, that is, multiple connections can read data from the same database at the same time. However, even if only one shared lock has not been released, no connection to the database is allowed.
If a connection wants to write data to the database, it must first obtain a retention lock. A database can have only one retained lock at the same time. The retained lock can coexist with the shared lock. The retained lock is the 1st stage of database writing. The reserved locks do not prevent other connections with shared locks from continuing to read the database, nor prevent other connections from getting new shared locks.
Once a connection gets a retained lock, it can start to process database modification operations, although these modifications can only be performed in the buffer, rather than actually written to the disk. Changes made to read content are saved in the memory buffer.
When a connection wants to commit a modification (or transaction), it must upgrade the lock to an exclusive lock. To obtain the exclusive lock, you must first upgrade the lock to an outstanding lock. After obtaining the pending lock, other connections won't be able to get a new shared lock, but the connections that already have the shared lock can still read the database normally. In this case, connections with pending locks wait for other connections with shared locks to complete their work and release their shared locks.
Once all other shared locks are released, connections with pending locks can escalate the locks to the exclusive locks, and the database can be freely modified. All previous modifications to the buffer zone will be written to the database file.
Deadlock
Why is the unlock mechanism required? To avoid deadlocks.
Consider the situations in Table 4-7 below. Two connections -- A and B -- work in the same database at the same time but completely independently. A executes 1st commands, B executes 2nd, 3, and so on.
Table 4-7 assumption of a deadlock
A is connected to B.
SQLite> begin;
SQLite> begin;
SQLite> insert into Foo values ('x ');
SQLite> select * From Foo;
SQLite> commit;
SQL error: database is locked
SQLite> insert into Foo values ('x ');
SQL error: database is locked
Both connections end in a deadlock. B first tries to write data to the database, so it has an outstanding lock. A tries to write again, but fails when its insert statement tries to promote the shared lock to the reserved lock.
For the convenience of discussion, it is assumed that connection A and B are always waiting for the database to be writable. At this time, other connections cannot even read the database, because B has a pending lock (which can prevent other connections from obtaining a shared lock ). At this time, not only does a and B not work, but all other processes cannot operate on the database.
What if this problem is avoided? Of course, a and B cannot be resolved through negotiation because they do not even know each other's existence. The answer is to use the correct transaction type to complete the work.
Transaction type
SQLite has three different transactions that use different lock states. The transaction can start with: deferred, mmediate, or exclusive. Specify the transaction type in the begin command:
Begin [deferred | immediate | exclusive] transaction;
A deferred transaction does not get any lock (until it needs to lock), and The begin statement itself does not do anything-it starts in the unlock state. By default, this is the case. If you only use begin to start a transaction, the transaction is deferred, and it does not obtain any locks. When you perform the first read operation on the database, it obtains the shared lock. Similarly, it obtains the reserved lock when performing the first write operation.
The immediate transaction starting from begin attempts to obtain the reserved lock. If the connection succeeds, begin immediate ensures that no other connection can be used to write data to the database. However, other connections can read the database. However, the reserved lock will prevent other connections from running the begin immediate or begin exclusive command. When other connections execute the preceding command, the sqlite_busy error will be returned. Now you can modify the database, but you cannot submit it. When you commit, the sqlite_busy error will be returned, which means that other read transactions are not completed, you must wait until they are executed before committing the transaction.
The exclusive transaction tries to obtain the exclusive lock on the database. This is similar to immediate, but once successful, the exclusive transaction ensures that there are no other connections, so you can perform read and write operations on the database.
The problem in the above example is that the two connections finally want to write data to the database, but they did not give up their original locks. In the end, the shared lock causes the problem. If both connections start the transaction with begin immediate, the deadlock will not occur. In this case, only one connection can enter begin immediate at the same time, and other connections will have to wait. Begin immediate and begin exclusive are usually used by write transactions. Like the synchronization mechanism, it prevents deadlocks.
The basic principle is: if the database you are using has no other connections, it is enough to use begin. However, if you have other connections to the database, you have to use begin immediate or begin exclusive to start your transaction.
