SQLite, the database is a lightweight, ACID compliance relational database management system. It is designed to be embedded, the current Android and iOS devices are built-in SQLite database. SQLite Although petite, but also supports the transaction and most of SQL92 standard.
Zero-Configuration management without having to install and configure.
Serverless without server support.
In a single disk file Single Database File data file storage.
Stable Cross-Platform Database File database file format is cross-platform, whether it is the size of the end, or a 32bit or 64bit machine does not matter
SQLite Compact full-featured compiled about 500KiB, cutting characteristics can be obtained even lower than 300KiB library (the current version 220.127.116.11).
Manifest typing can declare a database field type, but the type of field stores the actual storage type and actual values related to a single field may contain values with different storage classes.
Variable-length records of variable length records, for example, you store to a VARCHAR (100) column character, the actual needs of storage space plus a character one byte of storage space.
SQL statements compile into virtual machine code SQL statements will be compiled into virtual machine code, this virtual machine readable code is straightforward, easy to debug.
Public domain completely open source.
SQL language extensions
The main disadvantage
SQLite only database-level locking, so it does not support high concurrency.
It does not support stored procedures.
SQLite is no concept of a user account, but to determine permissions on all of the database based on the file system. This makes enforcement difficult storage quota happen, enforce user license becomes impossible.
If only in mobile devices SQLite, then he's good enough advantages, disadvantages and obvious, so Uncle MySQL away. SQLite sister Come on.
Affairs and locks (< 3.7.0)
SQLite transaction and locks are very important concept.
SQLite has five different lock state
SQLite has a lock list, lock status records database connection. Each database connection at the same time can be in only one lock state. Each state (UNLOCKED) has a corresponding one kind of lock.
UNLOCKED database connection is initially in a state, in this state, the connection has not yet access to the database. When connecting to a database, it has even started a transaction using BEGIN, connections are still in the UNLOCKED state. To be able to read data from the database, the connection must be entered SHARED state, that is the first to obtain a SHARED lock. Multiple connections can achieve and maintain a shared lock, which means that multiple connections can be simultaneously read data from the same database, SQLite supports concurrent read data.
Want to write a database connection, it must first obtain a RESERVED lock. At the same time there can be only one database on a RESERVED lock, lock retention can coexist with shared locks, namely RESERVED lock does not prevent other connections have SHARED locks continue to read the database, nor prevent other connections to obtain new SHARED lock. Once a connection to get a RESERVED lock, it can be written to the buffer data instead of actually written to disk. When the connection you want to commit your changes (or transactions), you need to get PENDING lock, then you can not get a new connection SHARED lock, but already has a SHARED lock connection can continue normal read database. When all other SHARED locks are released, the connection has PENDING lock can lock it up to EXCLUSIVE lock, then you can modify previously made to the buffer written to the database file. So SQLite does not support concurrent write.
SQLite has three different transactions
Transaction type specified in the BEGIN command:
DEFERRED a transaction does not acquire any locks (until it needs time to lock), BEGIN statement itself does not do anything - it began in the UNLOCK state. By default is this, if only by BEGIN start a transaction, then the transaction is DEFERRED, but it will not acquire any locks; when the database is first read, it gets SHARED locks; Similarly, when performing The first time you write, it gets RESERVED lock.
IMMEDIATE transaction started by the BEGIN attempts to get RESERVED lock. If successful, BEGIN IMMEDIATE ensure that no other connections can write to the database. However, other connections to the database can be read; however, RESERVED lock prevents other BEGIN IMMEDIATE or BEGIN EXCLUSIVE command connection when other connections execute the above command will return SQLITE_BUSY error. Then you can modify the operation of the database, but you can not commit, when you COMMIT, SQLITE_BUSY will return an error, which means that there are other read transaction is not completed, they have to wait after the implementation in order to commit the transaction.
EXCLUSIVE transaction will try to get the database EXCLUSIVE lock. This is similar to IMMEDIATE, but if successful, EXCLUSIVE transaction to ensure that no other connection to the database so you can read and write operation.
If two start to connect BEGIN DEFERRED affairs are in SHARED state and are waiting to enter the other end SHARED RESERVED, it would enter a deadlock state. So the beginning of the transaction BEGIN DEFERRED is possible to produce a deadlock.
Write-Ahead Logging (> = 3.7.0)
Before SQLite 3.7.0 does not support the writing of the read. To be able to read when written, the introduction of Write-Ahead Logging (WAL) mechanism, which can support a plurality of read and write concurrently.
Prior to the introduction WAL mechanism, SQLite using atomic transaction rollback journal mechanisms.
Principle rollback journal mechanism is: Before you modify the data in the database file, modify the data was backed up the first tab in another place, and then writes the modifications to the database file; if the transaction fails, the backup copy of the data back and undo changes; if the transaction is successful, delete the backup data, commit your changes.
WAL is the principle mechanism: modifications are not written directly to the database file, but is written to a file called WAL in another; if the transaction fails, WAL records are ignored, undo changes; if the transaction is successful, it will be written back in the subsequent time a database file, commit the change.
WAL sync files and database files is termed checkpoint (checkpoint), it is performed automatically by SQLite, default is accumulated to 1000 WAL file modification time; of course, in due course, can also be performed manually checkpoint, SQLite It provides the relevant interfaces. After performing checkpoint, WAL file will be cleared.
, SQLite will search when read in WAL file, locate the last write point, remember it, and ignores the write after this point (this ensures that read and write can be executed in parallel); Then, it is determined to read the data page is located in the WAL file, if, the read data WAL file, if not, directly read data in a database file.
At the time of writing, SQLite will be written to the WAL files can be, but must ensure exclusive write, and therefore can not be executed in parallel between writing.
WAL in the process of implementation, the use of shared memory technology, therefore, must all read and write processes on the same machine, otherwise, can not guarantee data consistency.
Reading and writing can be completely concurrent execution, do not block each other (but still can not write between concurrent).
WAL in most cases, better performance (because the two files do not need to have to write each write).
Disk I / O is more easily predicted behavior
All programs access the database must be on the same host, and support shared memory technology.
Each database now corresponds to three files: .db, -wal, -shm.
When data is written to GB level, database performance will drop.
3. 7.0 before SQLite unrecognized mechanism enabled WAL database file.
WAL introduced compatibility problems. When enabled WAL, the version number of the database file format upgrade from 1 to 2, therefore, unable WAL 3.7.0 before SQLite database file identification mechanism enabled. Disable WAL database file format will revert to the version number 1, which can be recognize SQLite 3.7.0 version before.
WAL introduction of performance issues. Under normal circumstances, WAL will improve transaction performance SQLite; however, in some extreme cases, it can cause performance degradation SQLite transaction.
The amount of data transaction execution time is longer or to modify reached GB's when, WAL file is occupied, it will temporarily stop the implementation checkpoint (checkpoint will clear WAL file), which will cause WAL files become very large increase addressing the time, eventually leading to reading and writing performance degradation.
When the checkpoint execution, will reduce the time of the read and write performance, therefore, WAL may result in periodic performance degradation