Transactions and locks (SQLite)

From: http://www.sqlite.com.cn/MySqlite/4/539.Html

 

2. Transaction)

2.1. Transaction lifecycles)

Cheng
There are two things worth noting between order and transaction:
(1) What objects are running in transactions -- this is directly related to the API.
(2)
The lifecycle of a transaction, that is, when it starts, when it ends, and when it begins to affect other connections (this is important for concurrency)-This involves the specific implementation of SQLite.
I
Connections can contain multiple statement, and each connection has a B-tree associated with the database and a pager. Pager is connected
It manages transactions, locks, memory caches, and crash recovery (crash ).
Recovery ). When you perform database write operations, remember the most important thing: at any time, only one connection is executed in one transaction. These answer the first question.
Generally,
The life of a transaction is similar to statement. You can also manually end it. By default, the transaction is automatically committed. Of course, you can also manually submit the transaction through begin .. commit. Next
The lock problem.

2.2 lock states)

Lock
It is very important to implement concurrent access, while for large-scale generic DBMS, the implementation of locks is also very complicated, while SQLite is relatively simple. Generally, the duration is the same as the transaction duration. One Transaction opens
First, it locks, ends the transaction, and releases the lock. However, if the system crashes without the end of the transaction, the next connection to the database will handle this situation.
There are 5 types
A lock in the same status. A connection is in one of the statuses at any time. The status and lock lifecycle are displayed.

There are the following points about this graph:
Note:
(1)
A transaction can start in unlocked, reserved, or exclusive states. By default, it starts at unlocked.
(2)
Unlocked, pending, shared, and reserved in the white box can exist at the same time of a database.
(3)
From the gray pending, things become strict, meaning that the transaction wants to get exclusive (note the difference from the white box ).
Although there are so many locks
But physically, there are only two situations: Read transactions and write transactions.

2.3. Read transaction (read
Transactions)

Let's take a look at the lock status change process when the SELECT statement is executed, which is very simple: a connection executes the SELECT statement
Sending a transaction, from unlocked to shared, is as simple as returning to unlocked when the transaction is committed.
Consider the following example (for simplicity, use
Pseudo Code ):
DB = open ('Foods. db ')
Db.exe C ('begin ')
Db.exe C ('select
* From episodes ')
Db.exe C ('select * from episodes ')
Db.exe C ('commit ')
DB. Close ()

By
In explicit use of begin and commit, two select commands are executed in a transaction. When the first exec () is executed, the connection is in the shared state.
The second exec () execution. when the transaction is committed, the connection returns from the shared to the unlocked status again, as shown below:
Unlocked → pending → shared → unlocked
For example
If no begin or commit rows exist:
Unlocked → pending → shared → unlocked → pending →
Shared → unlocked

2.4. Write transaction (write
Transactions)

Next we will consider writing a database, such as update. Like a read transaction, it will also experience
Unlocked → pending → shared, but the following is the gray pending,

2.4.1, the reserved states

When a connection writes data to a database
The shared status changes to the reserved status. If it gets the reserved lock, it means it is ready for write operations. Even if it does not write the changes to the database
Save the modification to the page cache ).
When a connection enters the reserved status, pager starts to initialize the recovery log.
(Rollback journal ). In the reserved state, pager manages three types of pages:
(1) Modified
Pages: Contains records modified by B-tree, which are stored in page cache.
(2) unmodified
Pages: Contains records not modified by B-tree.
(3) journal pages: This is the previous version of the modified page, which is not stored in the page
Cache, but before the B-tree modification page.
Page
Cache is very important because of its existence. A connection in the reserved state can actually start working without interfering with other (read) connections. Therefore, SQLite can be high.
Multiple read connections and one write connection at the same time.

2.4.2,
Pending states

After a connection is modified, the transaction is committed. The pager that executes the process enters the exclusive state. Slave
Reserved status. Pager tries to obtain the pending lock. Once obtained, it excludes it and does not allow any other connections to obtain the pending lock (pending is
Gateway
Lock ). Since the write operation holds the pending lock, no other connection can enter the shared status from the unlocked status, that is, no connection can enter the data (No
New readers, no new
Writers ). Only connections in the shared state can continue to work. The pending writer waits until all these connections release their locks,
Then apply the excusive lock to the database, enter the exclusive state, and exclusively occupy the database (the locking mechanism for SQLite should be clearer here ).

2.4.3. The exclusive State

In the exclusive status, the master
The task is to write the modified page from the page cache to the database file, which is the real place for writing.
Write modified in Pager
Before pages, it has to do one thing first: Write logs. It checks whether all logs are written to the disk, which is usually in the Operation buffer, so pager must tell the OS to write all files
This is done by the program synchronous (by calling the corresponding API of the OS.
Logs are the only method for database recovery.
Important. If the log page is not completely written to the disk and crashes, the database cannot be restored to its original state, and the database is in an inconsistent state. After the log is written, pager writes all
Modified pages is written to the database file. Next, it depends on the transaction commit mode. If it is automatic commit, pager clears the log, page
Cache, and then exclusive enters unlocked. If it is manually submitted, pager continues to hold the exclusive lock and save the log until the commit
Or rollback.

In short, in terms of performance, the process occupies the exclusion lock as short as possible, so DBMS usually occupy the exclusion only when actually writing files.
Lock, which can greatly improve the concurrency performance.