The paper

Sqlite Database is a relatively stable and lightweight small Database, unlike mysql, Oracle and other databases with a separate server process. Sqlite-based reads and writes are written to and from raw disk files, which means sqLite operations are completely DISK I/O operations. SQLite operations have certain anomalies that are difficult to monitor on the client side, and in the case of a large user base, the impact is often fatal to a very small number of users. Knowing SQLite makes it easy to use it properly.

Common exceptions include the following:

  • File write wrong
  • File lock bug
  • File sync failed
  • Equipment damage
  • The memory cover
  • Operating System Bugs
  • SQLite bug

The official documentation is also introduced

How To Corrupt An SQLite Database File

In view of the operating system and Sqlite bugs and other uncontrollable aspects, we need to do more accurate monitoring, such as insufficient memory. Similar to wechat, when it detects insufficient memory, a prompt box will pop up to inform users to clear the memory. The following is to discuss anomalies caused by improper operation.

SQLiteDatabaseLockedException

Android. Database. Sqlite. SQLiteDatabaseLockedException DB engine to find job in the implementation of the access database lock will throw this exception. Database locks are used to prevent DB corruption caused by multiple threads writing data simultaneously. So it usually happens with multi-threaded transactions. For the same transaction code segment, if a transaction has not yet finished committing and another thread has started to intervene in execution, this exception will result in a collision.

So please check your code. The recommended actions are:

  1. Use only one SQLOpenHelper to access and manipulate the database, singleton your SQLOpenHelper, and avoid multiple OpenHelpers opening db multiple times and writing data.
  2. When db is no longer needed, close all database Helper instances
  3. Make sure that the endTransaction() or transaction Successful methods are called when the transaction is completed. The next transaction operation will request the lock after enTransaction ends.

File lock

System file locks SQLite relies on the implementation of file locks by the underlying file system. The default SQLite lock is co-lock. Suppose threads A and B access the database and write data at the same time. If A C thread does not use SQLite API to manipulate data (such as direct IO operations), then the database lock will be automatically cancelled. If A or B thread does not operate db without lock protection, then the DB may be corrupted.

Advice:

  1. Use the SQLite API for database reads and writes whenever possible.
  2. If there are IO threads operating directly on the database, the code logically guarantees the order of synchronization between threads.

File sync failed

PRAGMA synchronous=OFF (mdatabase. execSQL(“PRAGMA synchronous=OFF”)); PRAGMA synchronous=OFF (PRAGMA synchronous=OFF); This is equivalent to saying: I expect faster writes, and the disk drive, in a clever move to achieve this goal, ignores the SQLite synchronization and notifies the system that I’m done writing. In fact, the data may not be completely written, if there is an accident such as power outage at this time, the real data will fail to write, resulting in DB corruption.

Advice:

Try not to set PRAGMA synchronous=OFF for write operations such as large data. Start with SQL statement optimization and performance optimization.

More than the db operation

Many of the more complex applications may have more than one database per application. Here is a scenario where the application contains two DB’s. DatabaseA: A database with preset data, tables already designed and data inserted ahead of time. There is no need to follow the application to create DB dynamically. When the user installs the DATABASE for the first time, the database path is copied to the application package path by I/O for application access. Generally, the DATABASE is static, with little or no data written. DatabaseB: Dynamically created and upgraded with the installation and upgrade of an application as one of the persistent methods for application data. Read and write operations are frequent.

DababaseA migrates IO copies upon application startup. DatabaseB dynamic Create can be used everywhere (including startup)

Danger 1: File overwrite

DatabaseB’s DBOpenHelperB is reading and writing DatabaseA’s tables while DatabaseA is being overwritten by the IO thread copy write file. DBOpenHelperB may still establish a connection to the old database, and read and write to the old database. Some file systems even interrupt THE I/O copy.

Danger 2: Cross Read and write

Generally, multiple dbOpenHelpers will read and write data to multiple dbOpenHelpers. If DBOpenHelper performs cross-DB read and write data, repeated DBOpen or Close data will be generated, affecting the normal read and write of other DBOpenHelpers. The lock is abnormal or the DB is damaged.

Multiple DB suggestions:

  1. Non-sqlite API thread I/O database Please wait until I/O is complete before setting up read/write connections to the database or manage your DBHelper
  2. DBHelper avoids cross-DB operations; one DB corresponds to one DBHelper instance

To sum up, most of the cases are caused by improper operation of the code, to understand the principle of good medicine, avoid the danger zone.

Add: reduce multi-process or multi-thread operation, single thread write as much as possible; Reduce transaction operations and reduce transaction complexity.