SQLite使用中的几个问题

一、Sqlite删除记录后数据库文件大小不变

原因是：
sqlite采用的是变长纪录存储，当你从Sqlite删除数据后，未使用的磁盘空间被添加到一个内在的”空闲列表”中用于存储你下次插入的数据，用于提高效率，磁盘空间并没有丢失，但也不向操作系统返回磁盘空间，这就导致删除数据乃至清空整个数据库后，数据文件大小还是没有任何变化，还是很大。

解决方法：两种

1、手动：

在数据删除后，手动执行VACUUM命令，执行方式很简单

sqlite> vacuum；

在Navicat中可以直接执行，eg：

DELETE FROM Company WHERE Age < 190;VACUUM;

但是此语句在C#代码中执行不生效，可以查看下文的方法。

2、自动：

在数据库文件建成中，将auto_vacuum设置成“1”。

参考：

解决sqlite 删除记录后数据库文件大小不变

压缩Sqlite数据文件大小，解决数据删除后占用空间不变的问题

扩展：C# 中如何解决？

方法也是两种：

1、手动释放空间

先写一个执行sql语句的函数：

private void ExecuteSql(string sDbPath, string sqlStr)
        {
            using (SQLiteConnection conn = new SQLiteConnection("data source = " + sDbPath))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    cmd.Connection = conn;

                    conn.Open();
                    cmd.CommandText = sqlStr;
                    cmd.ExecuteNonQuery();

                    conn.Close();
                }
            }
        }

在删除数据表/大量数据后，调用上述函数（dbPath为数据库的地址）。

ExecuteSql(dbPath, "VACUUM");

参考代码：SQLiteRepository.cs 

以上代码视sqlite-net.dll 的版本而改变，

SQLiteCommand cmd = new SQLiteCommand(SQLiteConnection);              //sqlite-net-pcl1.7.335
SQLiteCommand SQLiteCmd = SQLiteConnection.CreateCommand(sqlStr);    //sqlite-net-pcl1.4.118

2、设置数据库为自动释放空间

当数据库中无数据表时，设置其属性：

ExecuteSql(dbPath, "PRAGMA auto_vacuum = 1;");

测试了一下发现效果也挺好的。

比较两种方法，自动更方便。但是需要注意的是，在进行频繁的插入、更新、删除操作时，数据库会产生大量的内存碎片。自动释放空间的方法只能释放空闲数据页，但是并不会对内存碎片进行整理，在这个过程中反而会产生额外的内存碎片；

而手动方式可以同时释放空闲空间和整理内存碎片。

推荐使用第一种方式。

参考：C# 压缩 SQLite 数据库

二、Sqlite执行效率优化

参考：C# SQLite执行效率的优化教程

实践：

/// <summary>
    /// 执行时间测试
    /// </summary>
    public class ExecuteTimeHelper
    {
        public static void Test()
        {
            SQLiteConnection connection = Run(() => new SQLiteConnection("Test.db"), "连接对象初始化并打开连接");
            Run(() => connection.CreateTable<Info>(), "创建数据表Info");

            SQLiteCommand command = Run(() => new SQLiteCommand(connection), "命令对象初始化");

            Run(() =>
            {
                command.CommandText = $"DELETE FROM Info;VACUUM;UPDATE sqlite_sequence SET seq ='0' where name ='Info';";
                command.ExecuteNonQuery();
            }, "执行DELETE命令及收缩数据库");

            int count = 200;
            Run(() =>
            {
                for (int i = 0; i < count; i++)
                {
                    command.CommandText = $"INSERT INTO Info(Name, Age) VALUES ('A{i:000}','{i}')";
                    command.ExecuteNonQuery(); //将返回操作所影响的记录条数
                }
                command.ExecuteScalar<object>();
            }, $"[---不使用事务---]事务执行INSERT命令，插入{count}条数据");
            Run(() =>
            {
                command.CommandText = $"DELETE FROM Info;VACUUM;UPDATE sqlite_sequence SET seq ='0' where name ='Info';";
                command.ExecuteNonQuery();
            }, "执行DELETE命令及收缩数据库");


            Run(() => connection.BeginTransaction(), "开始事务");
            int count2 = 3000;
            Run(() =>
            {
                for (int i = 0; i < count2; i++)
                {
                    command.CommandText = $"INSERT INTO Info(Name, Age) VALUES ('A{i:000}','{i}')";
                    command.ExecuteNonQuery();
                }
                var result = command.ExecuteScalar<object>();
            }, $"[---使用事务---]执行INSERT命令，插入{count2}条数据");
            Run(() => connection.Commit(), "提交事务");

            //或者用事务方法 执行批量sql语句
            connection.RunInTransaction(() =>
            {
                int count2 = 3000;
                Run(() =>
                {
                    for (int i = 0; i < count2; i++)
                    {
                        command.CommandText = $"INSERT INTO Info(Name, Age) VALUES ('A{i:000}','{i}')";
                        command.ExecuteNonQuery();
                    }
                    var result = command.ExecuteScalar<object>();
                }, $"[---使用事务---]执行INSERT命令，插入{count2}条数据");
            });

            Run(() => connection.Close(), "关闭连接");
            Console.ReadKey();
        }

        public static void Run(Action action, string description)
        {
            Stopwatch sw = Stopwatch.StartNew();
            action();
            Console.WriteLine($"--> {description}: {sw.ElapsedMilliseconds}ms");
        }

        public static T Run<T>(Func<T> func, string description)
        {
            Stopwatch sw = Stopwatch.StartNew();
            T result = func();
            Console.WriteLine($"--> {description}: {sw.ElapsedMilliseconds}ms");
            return result;
        }
    }

    class Info
    {
        public long ID { set; get; }
        public string Name { set; get; }
        public long Age { set; get; }
    }
View Code

  •  无论是执行插入或查询操作，使用事务比不使用事务快，尤其是在批量插入操作时，减少得时间非常明显；比如在不使用事务的情况下插入3000条记录，执行所花费的时间为17.252s，而使用事务，执行时间只用了0.057s，效果非常明显，而SQL Server不存在这样的问题。
  • 不能每次执行一条SQL语句前开始事务并在SQL语句执行之后提交事务，这样的执行效率同样是很慢，最好的情况下，是在开始事务后批量执行SQL语句，再提交事务，这样的效率是最高的。

即使只插入一条数据也是有很大差异的：

三、SQLite.SQLiteException异常: database is locked、Busy

这就涉及到sqlite的并发读写问题：

1.sqlite3支持多线程同时读操作，但不支持多线程同时写操作。

2.同一时刻只能有一个线程去进行写操作，并且在一个线程进行写操作的时候，其他线程是不能进行读操作的。

  当一个线程正在写操作时，其他线程的读写都会返回操作失败的错误，显示数据库文件被锁住。

1、sqlite3的锁及事务类型

sqlite3总共有三种事务类型：BEGIN [DEFERRED /IMMEDIATE / EXCLUSIVE] TRANSCATION，
五种锁，按锁的级别依次是：UNLOCKED /SHARED /RESERVERD /PENDING /EXCLUSIVE。
  • UNLOCKED ，无锁状态。数据库文件没有被加锁。
  • SHARED 共享状态。数据库文件被加了共享锁。可以多线程执行读操作，但不能进行写操作。
  • RESERVED 保留状态。数据库文件被加保留锁。表示数据库将要进行写操作。
  • PENDING 未决状态。表示即将写入数据库，正在等待其他读线程释放 SHARED 锁。一旦某个线程持有 PENDING 锁，其他线程就不能获取 SHARED 锁。这样一来，只要等所有读线程完成，释放 SHARED 锁后，它就可以进入 EXCLUSIVE 状态了。
  • EXCLUSIVE 独占锁。表示它可以写入数据库了。进入这个状态后，其他任何线程都不能访问数据库文件。因此为了并发性，它的持有时间越短越好。

说明：

  • 当执行select即读操作时，需要获取到SHARED锁（共享锁），
  • 当执行insert/update/delete操作(即内存写操作时)，需要进一步获取到RESERVERD锁（保留锁），
  • 当进行commit操作(即磁盘写操作时)，需要进一步获取到EXCLUSIVE锁（排它锁）。
  1. 对于RESERVERD锁，sqlite3保证同一时间只有一个连接可以获取到保留锁，也就是同一时间只有一个连接可以写数据库(内存)，但是其它连接仍然可以获取SHARED锁，也就是其它连接仍然可以进行读操作（这里可以认为写操作只是对磁盘数据的一份内存拷贝进行修改，并不影响读操作）。
  2. 对于EXCLUSIVE锁，是比保留锁更为严格的一种锁，在需要把修改写入磁盘即commit时需要在保留锁/未决锁的基础上进一步获取到排他锁，顾名思义，排他锁排斥任何其它类型的锁，即使是SHARED锁也不行，所以，在一个连接进行commit时，其它连接是不能做任何操作的（包括读）。
  3. PENDING锁（即未决锁），则是比较特殊的一种锁，它可以允许已获取到SHARED锁的事务继续进行，但不允许其它连接再获取SHARED锁，当已存在的SHARED锁都被释放后（事务执行完成），持有未决锁的事务就可以获得commit的机会了。sqlite3使用这种锁来防止writer starvation（写饿死）。

sqlite3只支持库级锁，库级锁意味着什么？

意味着同时只能允许一个写操作，也就是说，即事务T1在A表插入一条数据，事务T2在B表中插入一条数据，这两个操作不能同时进行，即使你的机器有100个CPU，也无法同时进行，而只能顺序进行。表级都不能并行，更别说元组级了——这就是库级锁。

但是，SQLite尽量延迟申请X锁，直到数据块真正写盘时才申请X锁，这是非常巧妙而有效的。

2、死锁的情况

死锁的情况：
当两个连接使用begin transaction开始事务时，第一个连接执行了一次select操作（已经获取到SHARED锁），第二个连接执行了一次insert操作（已经获取到了RESERVERD锁）。
此时第一个连接需要进行一次insert/update/delete（需要获取到RESERVERD锁），第二个连接则希望执行commit（需要获取到EXCLUSIVE锁）。
由于第二个连接已经获取到了RESERVERD锁，根据RESERVERD锁同一时间只有一个连接可以获取的特性，第一个连接获取RESERVERD锁的操作必定失败；
而由于第一个连接已经获取到SHARED锁，第二个连接希望进一步获取到EXCLUSIVE锁的操作也必定失败【因为排他锁 排斥任何其他类型的锁】。这就导致2个连接都在等待对方释放锁，于是就出现了事务死锁。
 
要解决这个问题，就必须了解事务：事务类型的使用原则
在用”begin transaction”显式开启一个事务时，默认的事务类型为DEFERRED，锁的状态为UNLOCKED，即不获取任何锁，如果在使用的数据库没有其它的连接，用begin就可以了。
如果有多个连接都需要对数据库进行写操作，那就得使用BEGIN IMMEDIATE/EXCLUSIVE开始事务【解决死锁的问题】。
使用事务的好处是：
1.一个事务的所有操作相当于一次原子操作，如果其中某一步失败，可以通过回滚来撤销之前所有的操作，只有当所有操作都成功时，才进行commit，保证了操作的原子特性；
2.对于多次的数据库操作，如果我们希望提高数据查询或更新的速度，可以在开始操作前显式开启一个事务，在执行完所有操作后，再通过一次commit来提交所有的修改或结束事务。
 
更多参考：

3、对SQLITE_BUSY的处理

当有多个连接同时对数据库进行写操作时，根据事务类型的使用原则，我们在每个连接中用BEGIN IMMEDIATE开始事务，即多个连接都尝试取得保留锁的情况，根据保留锁同一时间只有一个连接可以获取到的特性，其它连接都将获取失败，即事务开始失败，这种情况下，sqlite3将返回一个SQLITE_BUSY的错误。
如果我们不希望操作就此失败而返回，就必须处理SQLITE_BUSY的情况，让其重试，sqlite3提供了sqlite3_busy_handler或sqlite3_busy_timeout来处理SQLITE_BUSY。
对于sqlite3_busy_handler，我们可以指定一个busy_handler来处理，并可以指定失败重试的次数。
而sqlite3_busy_timeout则是由sqlite3自动进行sleep并重试，当sleep的累积时间超过指定的超时时间时，最终返回SQLITE_BUSY。
需要注意的是，这两个函数同时只能使用一个，后面的调用会覆盖掉前次调用。从使用上来说，sqlite3_busy_timeout更易用一些，只需要指定一个总的超时时间，然后sqlite自己会决定多久进行重试以及重试的次数，直到达到总的超时时间最终返回SQLITE_BUSY。并且，这两个函数一经调用，对其后的所有数据库操作都有效，非常方便。

参考：SQLite 线程安全和并发

更多：Sqlite WAL原理

How to open SQLite connection in WAL mode

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/peterYong/p/14416849.html