The SQLite and Fmdb of iOS

Database SQLite plays an important role in iOS, this article mainly describes the concurrency of SQLite, transactions and common corruption problems, the following will briefly describe the further packaging of SQLite third-party library Fmdb.

The concurrency and Transactions of SQLite

Before we understand the transactions and concurrency of sqlite, we need to understand the types and differences of the various locks provided by SQLite. SQLite offers five levels of lock:

1. Unlocked (Unlocked): No read and write operations exist for the current database. to the default state.
2. shared lock: The current database can be read, but cannot perform write operations. At the same time, any process can hold a shared lock on the database, so the read operation of SQLite is concurrent. Once the shared lock is active, no other write operations are made and must wait.
3. RESERVED (Reserved lock): Indicates that there is currently a process scheduled to write to the database at some point in the future, but because there are still other processes holding shared locks in the read data. At the same time, the same database can have only one reserved lock and multiple shared locks.
4. PENDING (Pending Lock): Indicates that the process holding the pending lock is about to start writing to the database, but waits for the shared lock to read the data so that the exclusive (exclusive lock) can be obtained. At this point, other read operations will not be allowed, which is a good solution to the problem of writing hunger (if there is always a read operation, then the write operation will not be executed).
5. EXCLUSIVE (Exclusive lock): Once an exclusive lock is acquired, the database will no longer accept additional locks until the write operation is complete. So sqlite in order to improve concurrency, will be in the shortest possible time to finish processing.

From the above 5 kinds of lock mechanism, we can see that sqlite for the read operation is very good support concurrency, but for the write operation, because he used the lock library, so the concurrency of its write operations will be greatly affected. And more prone to deadlocks.

The transaction of the database is mainly used to ensure the atomicity, consistency, and isolation of the data operations, and can roll back and commit the transaction uniformly.

The SQL default under SQLite is in autocommit mode, but once the "Begin Transaction" is declared, it means that you want to change the schema to manual commit.

Begin Transactionselect * FROM table where ... insert into table values (...) Rollback Transaction/commit

When executing to select, gets the shared lock to perform the read operation. When executed to insert or update,delete, a reserved lock is acquired, but no exclusive lock is acquired until commit to actually write the data.

When executing to rollback or commit, it does not mean that the data will actually be written, but instead the manual mode is changed to Automatic mode, and the process of automatic mode is still processed to write the data or read the data. But a little bit different, rollback will set an identity to tell the automatic mode of the process flow, the data needs to be rolled back.

SQLite's transactions are divided into three categories: BEGIN [DEFERRED | IMMEDIATE | EXCLUSIVE] TRANSACTION

DEFERRED: As we've described above, begin without acquiring any locks, and when the execution of a read or write statement executes, the corresponding lock is acquired.

IMMEDIATE: If this category is specified, then the transaction will attempt to acquire the reserved lock, and if successful, the other connection will not be able to write to the database and can be read. It also prevents other transactions from executing the begin immediate or begin exclusive, or returns SQLITE_BUSY. The reason for this is that when reserved locks, "at the same time, the same database can have only one reserved lock and multiple shared locks."

EXCLUSIVE: Similar to immediate, an attempt is taken to obtain a EXCLUSIVE lock.

Common problems with SQLite:

sqlite_busy: usually because of a lock conflict, for example: Once a process holds a reserved lock, other processes want to hold the reserved lock, the error is reported, or a process holds a pending lock, Other processes that want to hold a shared lock will also report this error. Deadlocks can also cause this error, such as if a process a holds a shared lock and then is about to apply for a reserved lock, another process B holds the reserved lock and is about to apply for a exclusive lock, at which point a waits for the reserved lock of B, and b to wait for A's shared lock release, resulting in deadlock, see: https://sqlite.org/c3ref/busy_handler.html.

sqlite_locked (Database is LOCKED): The official explanation is that if you are dealing with two incompatible things in the same database connection, this error will be reported. Like what:

DB eval {SELECT rowid from ex1} {     if {$rowid ==10} {       db eval {DROP TABLE ex1}  ; # would give Sqlite_locked error< c8/>}   }

Official explanation Address: http://sqlite.org/cvstrac/wiki?p=DatabaseIsLocked

database corruption : In short, when the system is ready to write data into the database file crashes (app crashes, power outages, kill process, etc.), this time in memory to write the data information is lost, then the only chance to recover data at this time is the log, but the log may be corrupted, So if the log is damaged or lost, the database is corrupted. The official explanation is this: SQLite uses the system-provided Fsync () method to write data to the disk under a UNIX system, but this function does not work correctly every time, especially for some inexpensive disks. This is the bug,sqlite of the operating system that does not solve this problem.

Fmdatabasequeue Source Code Analysis

Fmdb is a third-party open Source library that encapsulates a range of sqlite operations, including:

1. Fmresultset: Represents the result set and some actions fmdatabase after the query is executed.

2. Fmdatabase: Represents a single instance of the SQLite database operation, through which the database can be increased and censored and so on.

3. Fmdatabaseadditions: Extends the Fmdatabase class, adding the ability to simplify the way that query results return only a single value, whether a table, a column exists, a version number, a checksum SQL, and so on.

4. Fmdatabasequeue: Support for multi-threaded operational databases.

5. Fmdatabasepool: Provides support for multithreading operations using the form of a task pool. (not recommended for official use)

We mainly explain the Fmdatabasequeue class.

-(Instancetype) Initwithpath: (nsstring*) Apath flags: (int) openflags VFS: (NSString *) Vfsname {self        = [Super init];< C1/>if (self! = nil) {                _db = [[[Self class] databaseclass] databasewithpath:apath];        Fmdbretain (_db);        #if sqlite_version_number >= 3005000        bool success = [_db openwithflags:openflags vfs:vfsname]; #else        BOOL Success = [_db open]; #endif        if (!success) {            NSLog (@ "Could not create database queue for path%@", Apath);            Fmdbrelease (self);            return 0x00;        }                _path = fmdbreturnretained (Apath);                _queue = Dispatch_queue_create ([[NSString stringwithformat:@ "fmdb.%@", Self] utf8string], NULL);        Dispatch_queue_set_specific (_queue, Kdispatchqueuespecifickey, (__bridge void *) self, NULL);        _openflags = openflags;    }        return self;}

Initialize method, we pick up the important saying:

• Create a serial queue, after which the SQL operations are placed in this queue. Why not use a more efficient parallel queue? As I said earlier, because SQLite is a lock library for write operations, it is easy to return sql_busy errors if you use parallel queues.
• Generates an identity for the current queue, which is used later to determine whether the same queue is executed at the time of SQL execution.

When used, this method is called:

-(void) Indatabase: (void (^) (Fmdatabase *db)) block {/* Get the currently executing queue (which should probably be nil , but in theory could is another DB queue * and then check it against the self-make sure we ' re not about to deadlock.    */Fmdatabasequeue *currentsyncqueue = (__bridge id) dispatch_get_specific (kdispatchqueuespecifickey); ASSERT (Currentsyncqueue! = self && "indatabase:was called reentrantly on the same queue, which would leads to a de        Adlock ");        Fmdbretain (self);        Dispatch_sync (_queue, ^ () {Fmdatabase *db = [self database];                Block (DB); if ([db hasopenresultsets]) {NSLog (@ "Warning:there is at least one open result set around after performing [F            Mdatabasequeue indatabase:] "); #if defined (Debug) && debug Nsset *opensetcopy = fmdbreturnautoreleased ([[DB valueforkey:@] _openresults            ETS "] copy]);    For (Nsvalue *rsinwrappedinatastyvaluemeal in opensetcopy) {            Fmresultset *rs = (Fmresultset *) [rsinwrappedinatastyvaluemeal Pointervalue];            NSLog (@ "query: '%@ '", [RS query]);        } #endif}); Fmdbrelease (self);}

The first is to determine whether the same queue, if not the same queue, it is easy to deadlock situation, the reason is: the same DB instance is held by different queues, but because the write operation is the lock library, so when the two queue to write the library and read the library, it is prone to deadlock situation, See the sqlite_busy explanation above for details.

Then using Dispatch_sync to synchronize the blocks in the queue, there may be questions about why you don't use Diapatch_async for asynchronous processing. This involves the difference between a synchronous serial queue and an asynchronous serial queue, except that synchronization blocks the current thread and asynchronously does not, the same point is that the tasks in the queue are executed in one order after another. I expect it to be because the Fmdb author thinks that it is only necessary to provide a synchronous method, providing an async method that opens a new thread, increases the overhead, and if the user needs it, sets a layer of dispatch_async on the outside. and using Dispatch_sync means that the method is thread-safe.

When we use transactions, we use:

-(void) Indeferredtransaction: (void (^) (fmdatabase *db, BOOL *rollback)) block {    [self begintransaction:yes Withblock:block];} -(void) Intransaction: (void (^) (fmdatabase *db, BOOL *rollback)) block {    [self begintransaction:no withblock:block] ;}

The above method indeferredtransaction indicates that the transaction uses the deferred category, and Intransaction indicates that the transaction uses the exclusive category, which is explained by the transaction category above.

A method is also provided later:

#if sqlite_version_number >= 3007000-(nserror*) Insavepoint: (void (^) (fmdatabase *db, BOOL *rollback)) block

Provides a point where a save can be rolled back, and can be set for rollback. It didn't work.

Overall, because SQLite this database lock particularity, so led to Fmdatabasequeue to this design, so when we use, for the same database instance, to ensure the uniqueness of fmdatabasequeue.

The next thing you can think about is that the author did not create two queues, one for reading, one for writing, because SQLite supports read sharing, so you can consider creating a parallel read queue, but you need to prevent "write starvation" from producing.

Reference Links:

Https://www.sqlite.org/lockingv3.html

http://shanghaiseagull.com/index.php/tag/fmdb/

The SQLite and Fmdb of iOS