SQLite's affairs and locks, a very thorough explanation of "turn"

Original: SQLite's transaction and lock http://3y.uu456.com/bp-877d38906bec097sf46se240-1.html

Transaction

A transaction defines the bounds of a set of SQL commands that are either fully executed as a whole or are not executed. A typical instance of a transaction is a transfer. Scope of the transaction

A transaction is controlled by 3 commands: BEGIN, Commit, and rollback. Begin a transaction, and all subsequent operations can be canceled. Commit causes all commands after begin to be confirmed, while rollback restores all operations after begin. Such as:

Sqlite> BEGIN;

Sqlite> DELETE from Foods;

Sqlite> ROLLBACK;

Sqlite> SELECT COUNT (*) from foods;

COUNT (*)

412

The above starts a transaction that deletes all rows of the foods table, but then rolls back with rollback. When you execute select again, no changes are found in the table.

SQLite by default, each SQL statement is self-transactional (autocommit mode).

Conflict resolution

As mentioned earlier, a violation of a constraint can result in the illegal termination of a transaction. Most databases (management systems) simply cancel all of the previous modifications. SQLite has its own unique way of handling constraint violations (or recovering from constraint violations), known as conflict resolution.

Such as:

sqlite> UPDATE Foods SET Id=800-id;

SQL error:primary KEY must be unique

SQLite offers 5 conflict resolution options: REPLACE, IGNORE, FAIL, Abort, and rollback.

Replace: When a violation of the unique integrity, SQLite will cause this violation of the record deletion, replace with new insert or modify the new record, SQL continues to execute, no error.

IGNORE

FAIL

ABORT

ROLLBACK

Database lock

In SQLite, locks and transactions are closely related. In order to use transactions effectively, you need to know some knowledge about how to lock them.

SQLite uses extensive locks. When a connection is to be written to the database, all other connections are locked until the write connection ends its transaction. SQLite has a lock table to help different write databases can be locked at the last minute, to ensure maximum concurrency.

SQLite uses the lock step-up mechanism, in order to write the database, the connection needs to obtain an exclusive lock. SQLite has 5 different lock states: unlocked

(unlocked), share (shared), reserved (RESERVED), Pending (PENDING), and Exclusive (EXCLUSIVE). Each database connection can only be in one state at a time. Each state (with the exception of an unlocked state) has a lock corresponding to it.

The initial state is unlocked, and in this state the connection does not have access to the database. When you connect to a database and even start a transaction with begin, the connection remains unlocked.

The next state of the unlocked state is a shared state. In order to be able to read (not write) data from the database, the connection must first enter a shared state, meaning that a shared lock is first obtained. Multiple connections can acquire and maintain shared locks at the same time, meaning that multiple connections can read data from the same database at the same time. But even if only one shared lock is not released, no connection is allowed to write to the database.

If a connection wants to write a database, it must first obtain a retention lock. There can be only one reserved lock on a database. Reserved locks can be associated with shared locks

Coexistence, retention locks are the 1th phase of a write database. A retention lock does not prevent other connections that have shared locks from continuing to read the database, nor does it prevent other connections from acquiring new shared locks.

Once a connection has a retention lock, it can begin processing database modifications, although these modifications can only be performed in a buffer, rather than actually writing to disk. Changes made to the read content are saved in the memory buffer.

When a connection wants to commit a modification (or a transaction), it needs to promote the retention lock to an exclusive lock. In order to get an exclusive lock, you must first promote the retention lock to a pending lock. After a pending lock is acquired, the other connection cannot acquire a new shared lock, but a connection that already has a shared lock can continue to read the database normally. At this point, a connection with a pending lock waits for another connection that has a shared lock to complete its work and release its shared lock.

Once all other shared locks are released, a connection with a pending lock can elevate its lock to an exclusive lock, and the database can be modified freely. All previous modifications to the buffer are written to the database file.

Dead lock

Why do we need to understand the mechanism of locking? In order to avoid deadlocks.

Consider the scenario that is assumed in table 4-7 below. Two connections--a and b--at the same time but work completely independently on the same database. A executes the 1th order, B executes 2nd, 3, and so on.

Table 4-7 hypothetical situation of a deadlock

A connection B connection

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

All two connections end in a deadlock. b first tries to write the database, it also has a pending lock. A again attempts to write, but fails when its INSERT statement attempts to promote a shared lock to a reserved lock.

For ease of discussion, it is assumed that connections A and B are always waiting for the database to be writable. At this point, the other connections are not even able to read the database anymore because B has a pending lock (it can prevent other connections from acquiring a shared lock). In this case, not only A and B cannot work, all other processes can no longer operate the database. What if we avoid this situation? Of course, A and B cannot be negotiated, because they do not even know the existence of each other. The answer is to do the work with the right type of transaction.

Types of transactions

SQLite has three different kinds of transactions, using different lock states. A transaction can start at: DEFERRED, Mmediate, or exclusive. The transaction type is specified in the BEGIN command:

BEGIN [DEFERRED | IMMEDIATE | EXCLUSIVE] TRANSACTION;

A deferred transaction does not acquire any locks (until it needs a lock), and the BEGIN statement itself does not do anything-it begins in the unlock state. By default, this is the case, if you start a transaction with begin, then the transaction is deferred, and it does not acquire any locks, and when the first read is performed on the database it acquires a shared lock, and it acquires the reserved lock when the first write operation is made.

The immediate transaction starting with Begin will attempt to acquire the reserved lock. If successful, BEGIN immediate guarantees that no other connection can write to the database. However, other connections can read the database, but the reserved lock blocks the begin immediate or begin exclusive command for other connections, and returns a sqlite_busy error when other connections execute the above command. At this point you can modify the database, but you cannot commit, when you commit, you will return a sqlite_busy error, which means that there are other read transactions are not completed, you have to wait until they execute before committing the transaction.

The exclusive transaction tries to get a exclusive lock on the database. This is similar to immediate, but once successful, the exclusive transaction guarantees that there is no other connection, so the database can be read and written.

The problem with the example in the previous section is that two connections eventually want to write the database, but none of them give up their original locks, and finally, the shared lock causes the problem to occur. If two connections begin a transaction with begin immediate, the deadlock does not occur. In this case, only one connection can enter the begin IMMEDIATE at the same time, and the other connections will have to wait. Begin immediate and begin exclusive are usually used by write transactions. Just like the synchronization mechanism, it prevents the creation of deadlocks.

The basic guideline is that if you are using a database that has no other connections, the begin is sufficient. However, if you use a database that has other connections that also write to the database, you have to start your transaction using begin immediate or begin exclusive.

Sqlite3 using transaction processing [ZZ]

It is slow to operate on sqlite3 insert into, and so on.

Cause: It exists as a file in the disk, each time access to open a file, if the database for a large number of operations, it is very slow.

WORKAROUND: Commit in the form of a thing, because after starting a transaction, a large number of operations statements are kept in memory, and when the commit is all written to the database, the database file is only opened once. If the operation is wrong, you can also roll back the transaction.

Interface: The operation of a transaction has no special interface function, it is a normal SQL statement, respectively, as follows:

int ret;

ret = sqlite3_exec (db, "BEGIN Transaction", 0, 0, & zerrormsg); Start a transaction

ret = sqlite3_exec (db, "Commit Transaction", 0, 0, & zerrormsg); Commit a transaction

ret = sqlite3_exec (db, "ROLLBACK TRANSACTION", 0, 0, & zerrormsg);

Routine: Use the following statement before doing a large number of operations

ret = sqlite3_exec (db, "BEGIN Transaction", 0, 0,& zerrormsg);

For (...)

{

INSERT INTO operate

If the operation is wrong

ret = sqlite3_exec (db, "ROLLBACK TRANSACTION", 0, 0, & Zerrormsg)

}

ret = sqlite3_exec (db, "Commit Transaction", 0, 0, & zerrormsg);

The development process encountered this problem:

Operates on a separate table of two database files, executed in the following order: Open DB a->begin Trasaction->open db b->select from DB B->close db B->select from DB A->rollbak or Commit->close db A

The test found that the select from DB B step would be wrong, the error message is the library routine called out of sequence, after the error execution rollback, this step will also error.

It turns out that the reason is that starting a transaction can only operate on a single database.

The test finds that, even if the transaction is not started, the order of execution is: Open DB A->open db b->select from DB B->close db B->select from DB A->close db A, still appears Ibrary routine called out of sequence error.

SQLite's affairs and locks, a very thorough explanation of "turn"