sqlite3 多綫程/事務回滾/busy handler/簡單使用

最新推荐文章于 2024-06-20 14:28:37 发布

vegeta852

最新推荐文章于 2024-06-20 14:28:37 发布

阅读量1.2k

点赞数

分类专栏：第三方库文章标签：数据库

本文链接：https://blog.csdn.net/vegeta852/article/details/112045557

版权

SQLite是一个轻量级的嵌入式数据库，支持多线程模式，包括单线程、多线程和串行。在多线程环境中，需要注意线程安全问题，避免并发使用相同的数据库连接。SQLite3提供busy_handler处理SQLITE_BUSY状态，确保事务的正常进行。此外，文章还介绍了基础API和事务回滚的相关内容。

摘要由CSDN通过智能技术生成

sqlite3 簡介及數據庫操作

简介

SQLite数据库是专门针对嵌入式开发的数据库软件，占用资源非常少（仅需几百K内存即可），处理速度快，而且可以直接使用C语言编程，同时也可以配合C#、PHP、Java等其他语言。SQLite诞生于2000年，2015年发布了新版本SQLite3，一经推出马上变成了最流行的嵌入式数据库软件。

//SQL中文翻译为“结构化查询语言”，是单词"Structured Query Language"的缩写。SQL是一种操作数据库的高级的非过程化编程语言，用户使用SQL命令通过DBMS对数据库内的数据进行访问。1987年，SQL得到国际标准组织ISO的支持成为数据库的国际标准，不过各种数据库的SQL命令略有不同，因此并不能够完全通用。

SQLite 是跨平台的，支持众多操作系统，如 UNIX（Linux, Mac OS-X, Android, iOS）、Windows（Win32, WinCE, WinRT）

sqlite3 线程安全

(6) Is SQLite threadsafe?

Threads are evil. Avoid them.

SQLite is threadsafe. We make this concession since many users choose to ignore the advice given in the previous paragraph. But in order to be thread-safe, SQLite must be compiled with the SQLITE_THREADSAFE preprocessor macro set to 1. Both the Windows and Linux precompiled binaries in the distribution are compiled this way. If you are unsure if the SQLite library you are linking against is compiled to be threadsafe you can call the sqlite3_threadsafe() interface to find out.

SQLite is threadsafe because it uses mutexes to serialize access to common data structures. However, the work of acquiring and releasing these mutexes will slow SQLite down slightly. Hence, if you do not need SQLite to be threadsafe, you should disable the mutexes for maximum performance. See the threading mode documentation for additional information.

Under Unix, you should not carry an open SQLite database across a fork() system call into the child process.

** SQLite can be compiled with or without mutexes. When
** the [SQLITE_THREADSAFE] C preprocessor macro is 1 or 2, mutexes
** are enabled and SQLite is threadsafe. When the
** [SQLITE_THREADSAFE] macro is 0,
** the mutexes are omitted. Without the mutexes, it is not safe
** to use SQLite concurrently from more than one thread.

SQLite支持3种线程模式：
　　单线程：这种模式下，没有进行互斥，多线程使用不安全。禁用所有的mutex锁，并发使用时会出错。当SQLite编译时加了SQLITE_THREADSAFE=0参数，或者在初始化SQLite前调用sqlite3_config(SQLITE_CONFIG_SINGLETHREAD)时启用。
　　多线程：这种模式下，只要一个数据库连接不被多个线程同时使用就是安全的。源码中是启用bCoreMutex，禁用bFullMutex。实际上就是禁用数据库连接和prepared statement(准备好的语句)上的锁，因此不能在多个线程中并发使用同一个数据库连接或prepared statement。当SQLite编译时加了SQLITE_THREADSAFE=2参数时默认启用。若SQLITE_THREADSAFE不为0，可以在初始化SQLite前，调用sqlite3_config(SQLITE_CONFIG_MULTITHREAD)启用;或者在创建数据库连接时，设置SQLITE_OPEN_NOMUTEX flag。
　　串行：sqlite是线程安全的。启用所有的锁，包括bCoreMutex和bFullMutex 。因为数据库连接和prepared statement都已加锁，所以多线程使用这些对象时没法并发，也就变成串行了。当SQLite编译时加了SQLITE_THREADSAFE =1参数时默认启用。若SQLITE_THREADSAFE不为0，可以在初始化SQLite前，调用sqlite3_config(SQLITE_CONFIG_SERIALIZED)启用;或者在创建数据库连接时，设置SQLITE_OPEN_FULLMUTEX flag。

已使用SQLITE_THREADSAFE=1 情况下
需使用sqlite3_busy_handler对SQLITE_BUSY状态进行处理
https://www.sqlite.org/c3ref/busy_handler.html

sqlite3 锁相关：https://www.sqlite.org/lockingv3.html

注意事项

“线程安全”是指二个或三个线程可以同时调用独立的不同的sqlite3_open() 返回的"sqlite3"结构。而不是在多线程中同时使用同一个 sqlite3 结构指针。

一个sqlite3结构只能在调用 sqlite3_open创建它的那个进程中使用。你不能在一个线程中打开一个数据库然后把指针传递给另一个线程使用。这是因为大多数多线程系统的限制（或 Bugs？）例如RedHat9上。在这些有问题的系统上，一个线程创建的fcntl()锁不能由另一个线程删除或修改。由于SQLite依赖fcntl()锁来进行并发控制，当在线程间传递数据库连接时会出现严重的问题。

也许在Linux下有办法解决fcntl()锁的问题，但那十分复杂并且对于正确性的测试将是极度困难的。因此，SQLite目前不允许在线程间共享句柄。

在UNIX下，你不能通过一个 fork() 系统调用把一个打开的 SQLite 数据库放入子过程中，否则会出错。

在多线程情况下，一个sqlite3句柄不能共享给多个线程使用

基础API

1.Constructors

int sqlite3_open_v2(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb,         /* OUT: SQLite db handle */
  int flags,              /* Flags */
  const char *zVfs        /* Name of VFS module to use */
);

函数功能：These routines open an SQLite database file as specified by the filename argument. The filename argument is interpreted as UTF-8 for sqlite3_open() and sqlite3_open_v2() and as UTF-16 in the native byte order for sqlite3_open16(). A database connection handle is usually returned in *ppDb, even if an error occurs. The only exception is that if SQLite is unable to allocate memory to hold the sqlite3 object, a NULL will be written into *ppDb instead of a pointer to the sqlite3 object. If the database is opened (and/or created) successfully, then SQLITE_OK is returned. Otherwise an error code is returned. The sqlite3_errmsg() or sqlite3_errmsg16() routines can be used to obtain an English language description of the error following a failure of any of the sqlite3_open() routines.

Whether or not an error occurs when it is opened, resources associated with the database connection handle should be released by passing it to sqlite3_close() when it is no longer required.

The sqlite3_open_v2() interface works like sqlite3_open() except that it accepts two additional parameters for additional control over the new database connection. The flags parameter to sqlite3_open_v2() must include, at a minimum, one of the following three flag combinations:

SQLITE_OPEN_READONLY
    The database is opened in read-only mode. If the database does not already exist, an error is returned.
SQLITE_OPEN_READWRITE
    The database is opened for reading and writing if possible, or reading only if the file is write protected by the operating system. In either case the database must already exist, otherwise an error is returned.
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE
    The database is opened for reading and writing, and is created if it does not already exist. This is the behavior that is always used for sqlite3_open() and sqlite3_open16().

In addition to the required flags, the following optional flags are also supported:

SQLITE_OPEN_URI
    The filename can be interpreted as a URI if this flag is set.
SQLITE_OPEN_MEMORY
    The database will be opened as an in-memory database. The database is named by the "filename" argument for the purposes of cache-sharing, if shared cache mode is enabled, but the "filename" is otherwise ignored.
SQLITE_OPEN_NOMUTEX
    The new database connection will use the "multi-thread" threading mode. This means that separate threads are allowed to use SQLite at the same time, as long as each thread is using a different database connection.
SQLITE_OPEN_FULLMUTEX
    The new database connection will use the "serialized" threading mode. This means the multiple threads can safely attempt to use the same database connection at the same time. (Mutexes will block any actual concurrency, but in this mode there is no harm in trying.)
SQLITE_OPEN_SHAREDCACHE
    The database is opened shared cache enabled, overriding the default shared cache setting provided by sqlite3_enable_s