Learning to use SQLite (version 3.7.4) (medium I)

Transaction:

A transaction defines the boundary of an SQL command. This group of commands, or all executed as a whole, are not executed, or are called atomic principles. The essence of a transaction is that all transactions are executed, or none of them are executed, and they are at the same time.

The most convincing is the bank transfer process. (Baidu orgoogle for details ).

Transaction scope:

Three transaction control commands: Begin, commit, and rollback. Begin starts the transaction. If there is no commit, almost all operations can be canceled. After committing a transaction, commit performs all operations. Rollback restores all operations after begin.

After starting the transaction, but not committed:

At this point, we can see that the table Foo does not have any data.

After rollback? Let's see:

As you can see, after rollback, the data has not changed, relative to the beginning of the transaction.

Let's look at the features of commit:

It is worth noting that in SQLite, each SQL language is a self-contained transaction and can be automatically committed. It can be automatically rolled back when an incorrect command is encountered. That is, implicit transactions. SQLite also supports the savepoint and release commands.

Method for creating and starting the savepoint command;

Savepoint justincase;

Rollback [transaction] To justincase;

Let's look at it again:

The expected insert row is rolled back here.

Conflict Resolution:

Transactions can be terminated due to violation of constraints.

Five possible punching solutions for SQLite: replace, ignore, fail, abort, and rollback. The fault tolerance range and sensitivity increase sequentially and become increasingly strict.

Replace: When a constraint is violated, a new record is used to replace the violation record and delete the original violation record. The SQL statement continues to be executed and no error is reported. Trigger is not triggered.

Ignore: continue execution. The records of the rows that violate the constraints remain unchanged and are modified before and after the row. No error is reported.

Fail: it is directly terminated at the error record. It does not restore the modified records before violation of the constraints, and does not roll back. Command termination.

Abort: Restores changes made by all commands when the constraint is invalid. Is the default solution, defined by SQL standards. At this time, this conflict resolution policy is the most expensive and requires additional work.

Rollback: When the constraints are violated, SQLite executes rollback to terminate the current command and the entire transaction. Changes to the current and previous commands will be rolled back. The strictest possible solution to conflicts.

Conflict resolution can be specified in the SQL command or executed in the execution of tables and indexes. That is to say, the conflict solution can be specified in insert, update, create table, and create index.

Syntax format of the conflict solution in insert or update:

Insert or resolution into table (column_list) values (value_list );

Update or resolution into table set (value_list) Where predicate; see merge and upset of other databases.

Solution to conflicts in SQL commands:

You can specify a conflict resolution for a single field in the table definition:

The transaction cannot be committed because it is automatically terminated in advance. Pay attention to the features of rollback.

 

Database lock:

In SQLite, the lock is closely related to the transaction. It uses a coarse-grained lock. When a connection

When a connection writes data to the database, other connections are locked until the write transaction is completed.

Use the lock to gradually improve the technology. Five lock statuses of SQLite: unlocked, shared, reserved, pending, and exclusive ). One connection, one status, and one lock.

The database is not accessed when the database is connected or the transaction has been started. When reading data from the database, it enters the sharing status. Multiple connections can maintain multiple shared locks, that is, multiple connections can read data from the same database. If at least one shared lock is not released, data cannot be written.

When writing data through a connection, you must apply for a reserved lock. A database can only have one reserved lock, which can coexist with a shared lock. The reserved lock does not prevent other connections from reading the database and other connections from obtaining a new shared lock for the database. At this time, the write operation is started when the reserved lock status is reserved. The actual buffer zone is used and is not written to the disk. When the connection commits a transaction, the reserved lock is upgraded to the exclusive lock. In the process of upgrading the reserved lock to the exclusive lock, you must first promote the reserved lock to the pending lock. After obtaining the pending lock, other connections cannot obtain a new shared lock, however, the existing shared locks can still be connected and read from the database. During the read process of the shared locks, the pending locks are waiting for the shared locks to be completed and the shared locks are released.
After all the shared locks are released, connections with pending locks can be upgraded to exclusive locks. At this time, changes to the database begin, all previously cached changes will be written to database files.

Deadlock :*******************

Deadlock is rare and can be solved through the correct transaction type. First, let's take a look at the situation of deadlocks:

Hypothetical deadlock

Execution sequence	Connect to	Connect B
1	Begin;
2		Begin;
3		Insert into foo Values (XXX );
4	Select * From Foo;
5		Commit;
6		Error: db id locked
7	Insert into Foo values (Yyy );
8	Error: DB is locked;

Both connections end with deadlocks. B connection writes data to the database first, resulting in a pending lock to prevent sharing locks for other connections. At this time, a starts to write data to the database, but cannot obtain the sharded lock as the reserved lock. Other connections are rejected at this time, neither reading nor writing. At this time, the locks of A and B do not give up control, and other operations are blocked from the database. Other processes cannot operate the database.

The two connections eventually wanted to write data to the database, but they did not give up their original locks, and shared locks resulted in errors. If the two connections start with begin immediate/exclusive, no error occurs.