sqlite的编码

heshao2005

编码

使用多个连接
如果你曾经为其它的关系型数据库编写过程序，你就会发现有些适用于那些数据库的方法不一定适用于SQLite。使用其它数据库时，经常会在同一个代码块中打开多个连接，典型的例子就是在一个连接中返复遍历一个表而在另一个连接中修改它的记录。
在SQLite中，在同一个代码块中使用多个连接会引起问题，必须小心地对待这种情况。请看下面代码：
c1 = open('foods.db')
c2 = open('foods.db')

stmt = c1.prepare('SELECT * FROM episodes')
while stmt.step()
    print stmt.column('name')
    c2.exec('UPDATE episodes SET …)
end
stmt.finalize()

c1.close()
c2.close()
问题很明显，当c2试图执行UPDATE时，c1拥有一个SHARED锁，这个锁只有等stmt.finalize()之后才会释放。所以，是不可能成功写数据库的。最好的办法是在一个连接中完成工作，并且在同一个BEGIN IMMEDIATE事务中完成。新程序如下：
c1 = open('foods.db')

# Keep trying until we get it
while c1.exec('BEGIN IMMEDIATE') != SQLITE_OK
end

stmt = c1.prepare('SELECT * FROM episodes')
while stmt.step()
    print stmt.column('name')
    c1.exec('UPDATE episodes SET …)
end
stmt.finalize()

c1.exec('COMMIT')
c1.close()
在这种情况下，你应该在单独的连接中使用语句(statement)来完成读和写，这样，你就不必担心数据库锁会引发问题了。但是，这个特别的示例仍然不能工作。如果你在一个语句(statement)中返复遍历一个表而在另一个语句中修改它的记录，还有一个附加的锁问题你需要了解，我们将在下面介绍。
表锁
即使只使用一个连接，在有些边界情况下也会出现问题。不要认为一个连接中的两个语句(statements)就能协调工作，至少有一个重要的例外。
当在一个表上执行了SELECT命令，语句对象会在表上创建一个B-tree游标。如果表上有一个活动的B-tree游标，即使是本连接中的其它语句也不能够再修改这个表。如果做这种尝试，将会得到SQLITE_BUSY。看下面的例子：
c = sqlite.open("foods.db")
stmt1 = c.compile('SELECT * FROM episodes LIMIT 10')

while stmt1.step() do
    # Try to update the row
    row = stm1.row()
    stmt2 = c.compile('UPDATE episodes SET …')
    # Uh oh: ain't gonna happen
    stmt2.step()
end

stmt1.finalize()
stmt2.finalize ()
c.close()
这里我们只使用了一个连接。但当调用stmt2.step()则不会工作，因为stmt1拥有episodes表的一个游标。在这种情况下，stmt2.step()有可能成功地将锁升级到EXCLUSIVE，但仍会返回SQLITE_BUSY，因为episodes的游标会阻止它修改表。完成这种操作有两种方法：
遍历一个语句的结果集，在内存中保存需要的信息。定案这个读语句，然后执行修改操作。
将SELECT的结果存到一个临时表中并用读游标打开它。这时同时有一个读语句和一个写语句，但它们在不同的表上，所以不会影响主表上的写操作。写完成后，删掉临时表就是了。
当表上打开了一个语句，它的B-tree游标在两种情况下会被移除：
到达了语句结果集的尾部。这时step()会自动地关闭语句的游标。从VDBE的角度，当到达结果集的尾部时，CDBE遇到Close命令，这将导致所有相关游标的关闭。
程序显式地调用了finalize()，所有相关游标将关闭。
在很多编程语言扩展中，statement对象的close()函数会自动调用sqlite3_finalize()。
有趣的临时表
临时表使你可以做到不违反规则。如果你确实需要在一个代码块中使用两个连接，或者使用两个语句(statement)操作同一个表，你可以安全地在临时表上如此做。当一个连接创建了一个临时表，不需要得到RESERVED锁，因为临时表存在于数据库文件之外。有两种方法可以做到这一点，看你想如何管理并发。请看如下代码：
c1 = open('foods.db')
c2 = open('foods.db')

c2.exec('CREATE TEMPORARY TABLE temp_epsidodes as SELECT * from episodes')
stmt = c1.prepare('SELECT * FROM episodes')
while stmt.step()
    print stmt.column('name')
    c2.exec('UPDATE temp_episodes SET …')
end
stmt.finalize()

c2.exec('BEGIN IMMEDIATE')
c2.exec('DELETE FROM episodes')
c2.exec('INSERT INTO episodes SELECT * FROM temp_episodes')
c2.exec('COMMIT')

c1.close()
c2.close()
上面的例子可以完成功能，但不好。episodes表中的数据要全部删除并重建，这将丢失episodes表中的所有完整性约束和索引。下面的方法比较好：
c1 = open('foods.db')
c2 = open('foods.db')

c1.exec('CREATE TEMPORARY TABLE temp_episodes as SELECT * from episodes')
stmt = c1.prepare('SELECT * FROM temp_episodes')
while stmt.step()
    print stmt.column('name')
    c2.exec('UPDATE episodes SET …') # What about SQLITE_BUSY?
end
stmt.finalize()

c1.exec('DROP TABLE temp_episodes')
c1.close()
c2.close()
定案的重要性
使用SELECT语句必须要意识到，其SHARED锁(大多数时候)直到finalize()被调用后才会释放，请看下面代码：
stmt = c1.prepare('SELECT * FROM episodes')
while stmt.step()
    print stmt.column('name')
end
c2.exec('BEGIN IMMEDIATE; UPDATE episodes SET …; COMMIT;')
stmt.finalize()
如果你用C API写了与上例等价的程序，它实际上是能够工作的。尽管没有调用finalize()，但第二个连接仍然能够修改数据库。在告诉你为什么之前，先来看第二个例子：
c1 = open('foods.db')
c2 = open('foods.db')

stmt = c1.prepare('SELECT * FROM episodes')
stmt.step()
stmt.step()
stmt.step()

c2.exec('BEGIN IMMEDIATE; UPDATE episodes SET …; COMMIT;')

stmt.finalize()
假设episodes中有100条记录，程序仅仅访问了其中的3条，这时会发生什么情况呢？第2个连接会得到SQLITE_BUSY。
在第1个例子中，当到达语句结果集尾部时，会释放SHARED锁，尽管还没有调用finalize()。在第2个例子中，没有到达语句结果集尾部，SHARED锁没有释放。所以，c2不能执行UPDATE操作。
这个故事的中心思想是：不要这么做，尽管有时这么做是可以的。在用另一个连接进行写操作之前，永远要先调用finalize()。
共享缓冲区模式
现在你对并发规则已经很清楚了，但我还要找些事来扰乱你。SQLite提供一种可选的并发模式，称为共享缓冲区模式，它允许在单一的线程中操作多个连接。
在共享缓冲区模式中，一个线程可以创建多个连接来共享相同的页缓冲区。进而，这组连接可以有多个“读”和一个“写”同时工作于相同的数据库。缓冲区不能在线程间共享，它被严格地限制在创建它的线程中。因此，“读”和“写”就需要准备处理与表锁有关的一些特殊情况。
当 readers读表时， SQLite自动在这些表上加锁，writer就不能再改这些表了。如果writer试图修改一个有读锁的表，会得到SQLITE_LOCKED。如果readers运行在read-uncommitted模式(通过read_uncommitted pragma来设置)，则当readers读表时，writer也可以写表。在这种情况下，SQLite不为readers所读的表加读锁，结果就是readers和writer互不干扰。也因此，当一个writer修改表时，这些readers可能得到不一致的结果。