Sqlite and FMDB for iOS, sqliteFMDB for iOS

Sqlite and FMDB for iOS, sqliteFMDB for iOS

The database sqlite plays an important role in iOS. This article mainly describes the concurrency, transactions, and common corruption issues of sqlite. Next, we will briefly describe the third-party database FMDB further encapsulated by sqlite.

Sqlite concurrency and transactions

Before learning about transactions and concurrency of sqlite, we should first understand the types and differences of several locks provided by sqlite. Sqlite provides five levels of locks:

From the above five lock mechanisms, we can see that sqlite can well support concurrent read operations, but for write operations, because it uses the lock library method, therefore, the concurrency of write operations will be greatly affected. And it is easy to cause deadlocks.

Database transactions are mainly used to ensure the atomicity, consistency, and isolation of data operations, and can be rolled back and committed in a unified manner.

By default, all SQL statements in sqlite are in the automatic submission mode. However, once "Begin Transaction" is declared, the mode must be changed to manual submission.

begin transactionselect * from table where ...insert into table values (...)rollback transaction / commit

When the select statement is executed, the shared lock is obtained to perform the read operation. When an insert, update, or delete operation is executed, the lock will be retained, but no exclusive lock will be obtained before commit to write data.

When performing rollback or commit operations, it does not mean that the data will be actually written. Instead, the manual mode is changed to the automatic mode, and the data is still written or read in the automatic mode process. However, the difference is that rollback sets an identifier to tell the automatic mode processing process that data needs to be rolled back.

Sqlite transactions are classified into three categories: BEGIN [DEFERRED | IMMEDIATE | EXCLUSIVE] TRANSACTION

DEFERRED: As described above, no locks are obtained at the beginning of begin. The corresponding locks are obtained only when the read or write statements are executed.

IMMEDIATE: If this type is specified, the transaction attempts to obtain the RESERVED lock. If the lock is successful, other connections cannot be written to the database and can be read. At the same time, other transactions are blocked to execute begin immediate or begin exclusive. Otherwise, SQLITE_BUSY is returned. The reason was when the RESERVED lock was mentioned: "At the same time, the same database can only have one RESERVED lock and multiple shared locks ".

EXCLUSIVE: Similar to IMMEDIATE, the EXCLUSIVE lock will be obtained.

Sqlite FAQs:

SQLITE_BUSY: It is usually caused by lock conflicts. For example, if a process holds a RESERVED lock and other processes want to hold the RESERVED lock again, this error is reported. Or if a process holds a PENDING lock, this error will also be reported if other processes want to hold the SHARED lock again. A deadlock also causes this error. For example, A process A holds the SHARED lock and is about to apply for the RESERVED lock. Another process B holds the RESERVED lock and is about to apply for the EXCLUSIVE lock, at this time, A will wait for the RESERVED lock of B, and B will wait for the release of the SHARED lock of A to generate A deadlock. For details, see https://sqlite.org/c3ref/busy_handler.html.

SQLITE_LOCKED (database is locked): The official explanation is: If you handle two incompatible tasks in the same database connection, this error will be reported. For example:

db eval {SELECT rowid FROM ex1} {     if {$rowid==10} {       db eval {DROP TABLE ex1}  ;# will give SQLITE_LOCKED error     }   }

What is the official address: http://sqlite.org/cvstrac/wiki? P = DatabaseIsLocked

Database corruption: Simply put, when the system is preparing to write data to the database file, it crashes (the app crashes, power is down, and the process is killed). At this time, the data to be written in the memory is lost, at this time, the only chance to recover data is the log, but the log may also be damaged. Therefore, if the log is damaged or lost, the database will also be damaged. The official explanation is as follows: sqlite writes data to a disk using the fsync () method provided by the system in unix systems, but this function does not work correctly every time, especially for some cheap disks .. This is an operating system bug and sqlite cannot solve this problem.

FMDatabaseQueue source code parsing

FMDB is a third-party open source library that encapsulates a series of sqlite operations, including:

We will mainly explain the FMDatabaseQueue class.

- (instancetype)initWithPath:(NSString*)aPath flags:(int)openFlags vfs:(NSString *)vfsName {        self = [super init];        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;}

Initialization Method:

• Create a serial queue, and all subsequent SQL operations will be placed in this queue. Why not use more efficient parallel queues? As mentioned above, because sqlite locks the database for write operations, if parallel queues are used, the SQL _BUSY error is easily returned.
• Generates an identifier for the current queue to determine whether the queue is the same when SQL is executed.

 

This method is called when used:

- (void)inDatabase:(void (^)(FMDatabase *db))block {    /* Get the currently executing queue (which should probably be nil, but in theory could be another DB queue     * and then check it against self to 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 lead to a deadlock");        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 [FMDatabaseQueue inDatabase:]");            #if defined(DEBUG) && DEBUG            NSSet *openSetCopy = FMDBReturnAutoreleased([[db valueForKey:@"_openResultSets"] copy]);            for (NSValue *rsInWrappedInATastyValueMeal in openSetCopy) {                FMResultSet *rs = (FMResultSet *)[rsInWrappedInATastyValueMeal pointerValue];                NSLog(@"query: '%@'", [rs query]);            }#endif        }    });        FMDBRelease(self);}

First, it will determine whether it is the same queue. If it is not the same queue, it is prone to deadlocks. The reason is that the same database instance is held by different queues, but because the write operation locks the database, therefore, when both queues need to be written to and read from the database, deadlocks may occur. For more information, see the above description of SQLITE_BUSY.

Then we use dispatch_sync to synchronously process the blocks in the queue. Here we may wonder why we do not use diapatch_async for asynchronous processing? This involves the difference between Synchronous Serial queue and asynchronous serial queue. The difference is that synchronization will block the current thread, while Asynchronization will not. The same point is that tasks in the queue are executed in one order. I expect that the author of FMDB only needs to provide the synchronous method. Providing the asynchronous method will enable new threads and increase the overhead. If the user needs it, set dispatch_async on the outside. In addition, dispatch_sync indicates that this 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 preceding method, inDeferredTransaction, indicates that the transaction uses the DEFERRED category. inTransaction indicates that the transaction uses the EXCLUSIVE category. For the two differences, see the preceding explanation of the transaction category.

 

A method is also provided below:

#if SQLITE_VERSION_NUMBER >= 3007000- (NSError*)inSavePoint:(void (^)(FMDatabase *db, BOOL *rollback))block

Provides a point for saving and rollback. You can set whether to roll back. No...

 

In general, because of the special nature of the database lock of sqlite, FMDatabaseQueue is designed in this way. Therefore, we must ensure the uniqueness of FMDatabaseQueue for the same database instance during use.

The improvement in the future is that the author has not created two queues, one for reading and the other for writing, because sqlite supports read sharing, therefore, whether to create a parallel read queue can be considered, but it is necessary to prevent "Writing hunger.

 

Reference link:

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

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