SQLite is so petite and cute, not much to know.

In my eyes, MySQL and Oracle are like this.

And SQLite is the way it is

So cute database, I really should know more about her.

Introduction

SQLite is a lightweight database that adheres to the acid-based relational database management system. It is designed to be embedded and is currently built into the SQLite database for Android and iOS devices.

SQLite Although petite, but also support the business and most of the SQL92 standard.

Key Features

1. Zero-configuration no need to install and manage configurations.

2. serverless does not require server support.

3. Single database file data files are stored in one disk file.

4. Stable cross-platform database file format cross-platform, regardless of the size of the end, or 32bit or 64bit machines are not related

5. The Compact full feature of SQLite compiled around 500KiB, clipping features can even get lower than 300KiB library (current version 3.8.11.1).

6. Manifest typing can declare a database field type, but the type stored by the field is the actual storage type associated with the actual value, and a separate field may contain values for different storage classes.

7. Variable-length records variable length records, for example, you store a character to a varchar (100) column, the actual required storage space is a word multibyte a byte of storage space.

8. The SQL statements compile into virtual machine code SQL statement will be compiled into VM codes, which are straightforward to read and easy to debug.

9. Public domain is fully open source.

SQL Language Extensions

Main Disadvantages

1. SQLite only provides database-level locking, so high concurrency is not supported.

2. Stored procedures are not supported.

3. SQLite does not have a user account concept, but instead determines the permissions of all databases based on the file system. This makes it difficult to enforce storage quotas, and enforcing user licensing becomes impossible.

If you only use SQLite on mobile devices, then his advantages are good enough, and the shortcomings are not obvious, so uncle MySQL away. SQLite sister come over ╭ (╯3╰) ╮.

Transactions and Locks (< 3.7.0)

SQLite's transactions and locks are very important concepts.

Lock

SQLite has 5 different lock states

1. Unlocked (Unlocked)

2. GKFX (Shared)

3. RESERVED (Reserved)

4. PENDING (Pending)

5. EXCLUSIVE (Row it)

SQLite has a lock table that records the lock status of the database connection. Each database connection can only be in one of the lock states at the same time. Each state (unlocked) has a lock corresponding to it.

Read

The database connection was initially in the unlocked state, where the connection did not have access to the database. When you connect to a database and even start a transaction with begin, the connection is still in the unlocked state. In order to be able to read data from the database, the connection must enter a shared state, that is, to obtain a shared lock first. Multiple connections can acquire and maintain shared locks at the same time, which means that multiple connections can read data from the same database at the same time, and SQLite supports concurrent read data.

Write

A connection wants to write a database, it must first obtain a reserved lock. There can be only one reserved lock on a database, a reserved lock can coexist with a shared lock, and a reserved lock does not prevent other connections that have shared locks from continuing to read the database, nor does it prevent other connections from acquiring a new shared lock. Once a connection obtains a reserved lock, it can write data to the buffer instead of actually writing to disk. When a connection wants to commit a modification (or a transaction), it needs to obtain a pending lock, and then the connection can no longer acquire a new shared lock, but a connection that already has a shared lock continues to read the database normally. When all other shared locks are released, a connection that has a pending lock can elevate its lock to the exclusive lock, which can then be written to the database file for modifications that were made to the buffer. Therefore, SQLite does not support concurrent write.

Transactions

SQLite has three different kinds of transactions

1. DEFERRED (postponed)

2. Mmediate (Immediate)

3. EXCLUSIVE (Row it)

The transaction type is specified in the BEGIN command:

DEFERRED

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.

mmediate

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.

EXCLUSIVE

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.

dead Lock

If two connections that begin a transaction at begin deferred are in a shared state and are waiting for each other to end the shared to enter reserved, the deadlock state is entered. So the beginning of the start deferred transaction is likely to produce a deadlock.

write-ahead Logging (>=3.7.0)

SQLite 3.7.0 is not supported to write before the time read. In order to be able to write, the Write-ahead Logging (WAL) mechanism is introduced, which can support one write and multiple read concurrency.

Before introducing the WAL mechanism, SQLite uses the rollback journal mechanism to implement atomic transactions.

The principle of the rollback journal mechanism is: before modifying the data in the database file, the data in the page of the modification is backed up in another place before the modification is written to the database file, and if the transaction fails, the backup data is copied back, the modification is undone, and if the transaction succeeds, Then delete the backup data and commit the changes.

The principle of the Wal mechanism is that the modification is not written directly to the database file, but to another file called the Wal; if the transaction fails, the record in the Wal is ignored, the modification is revoked, and if the transaction succeeds, it is written back to the database file at a later time and commits the modification.

The behavior of synchronizing the Wal file and the database file is called Checkpoint (checkpoint), which is executed automatically by SQLite, which defaults to the time when the Wal file accumulates to 1000 pages of modification, and, of course, the checkpoint can be executed manually at the appropriate time. SQLite provides the relevant interface. After executing the checkpoint, the Wal file will be emptied.

At the time of reading, SQLite will search in the Wal file, find the last write point, remember it, and ignore the write point after this (which ensures that both read-write and read-read can be executed in parallel); then it determines whether the page to be read is in the Wal file, and if so, read the data in the Wal file, If not, the data in the database file is read directly.

When writing, SQLite writes it to the Wal file, but must guarantee exclusive writes, so writes cannot be executed in parallel.

Wal in the process of implementation, the use of shared memory technology, so all the read and write process must be on the same machine, otherwise, data consistency is not guaranteed.

Advantages

1. Read and write can be executed completely concurrently, without blocking each other (but still not concurrency between writes).

2. Wal has better performance in most cases (because there is no need to write two files per write).

3. Disk I/O behavior is easier to predict

Disadvantages

1. 1. All programs that access the database must be on the same host and support shared memory technology.

2. 2. Each database now corresponds to 3 files:. Db,-wal,-shm.

3. 3. When the write data reaches the GB level, the database performance will degrade.

4. 4. SQLite before 3.7.0 does not recognize a database file with the WAL mechanism enabled.

5. 5. Compatibility issues introduced by Wal. After Wal was enabled, the version number of the database file format was upgraded from 1 to 2, so SQLite before 3.7.0 was unable to identify the database file with the WAL mechanism enabled. Disabling Wal restores the version number of the database file format to 1, which can be recognized by the previous version of SQLite 3.7.0.

6. 6. Performance issues introduced by Wal. In general, Wal increases the transaction performance of SQLite, but in some extreme cases, it can degrade the performance of SQLite transactions.

   ① when the transaction execution time is longer or the amount of data to be modified reaches the GB level, the Wal file will be occupied and it will temporarily block the execution of the checkpoint (checkpoint will empty the Wal file), which will cause the Wal file to become large, increase the addressing time, and ultimately lead to a decline in read and write performance.

7. ② when checkpoint is executed, it can degrade read and write performance at that time, so Wal can cause periodic performance degradation

Original link: Http://www.jointforce.com/jfperiodical/article/997?f=jf_tg_bky

The empty realm of the original

SQLite is so petite and cute, not much to know.