第七章：数据持久存储与交换-sqlite3:嵌入式关系数据库-隔离级别

7.4.10 隔离级别
sqlite3支持3种加锁模式，也被称为隔离级别(isolation level),这会控制使用何种技术来避免连接之间不兼容的变更。打开一个链接时可以传入一个字符串作为isolation level参数来设置隔离级别，所以不同的链接可以使用不同的隔离级别值。下面这个程序展示了使用同一个数据库的不同连接时，不同的隔离级别对于线程中事件的顺序会有什么影响。这里创建了4个线程：两个线程更新现有的行，将变更写入数据库，另外两个线程尝试从task表读取所有行。

import logging
import sqlite3
import sys
import threading
import time

logging.basicConfig(
    level=logging.DEBUG,
    format='%(asctime)s (%(threadName)-10s) %(message)s',
    )

db_filename = 'todo.db'
isolation_level = sys.argv[1]

def writer():
    with sqlite3.connect(
        db_filename,
        isolation_level=isolation_level) as conn:

        cursor = conn.cursor()
        cursor.execute('update task set priority = priority + 1')
        logging.debug('waiting to synchronize')
        ready.wait()   # synchronize threads
        logging.debug('PAUSING')
        time.sleep(1)
        conn.commit()
        logging.debug('CHANGES COMMITTED')

def reader():
    with sqlite3.connect(
        db_filename,
        isolation_level=isolation_level) as conn:

        cursor = conn.cursor()
        logging.debug('waiting to synchronize')
        ready.wait()  # synchronize threads
        logging.debug('wait over')
        cursor.execute('select * from task')
        logging.debug('SELECT EXECUTED')
        cursor.fetchall()
        logging.debug('results fetched')

if __name__ == '__main__':
    ready = threading.Event()

    threads = [
        threading.Thread(name='Reader 1',target=reader),
        threading.Thread(name='Reader 2',target=reader),
        threading.Thread(name='Writer 1',target=Writer),
        threading.Thread(name='Writer 2',target=Writer),
        ]

    [t.start() for t in threads]

    time.sleep(1)
    logging.debug('setting ready')
    ready.set()

    [t.join() for t in threads]

这些线程使用threading模块的一个Event完成同步。write()函数连接数据库，并完成数据库变更，不过在事件触发前并不提交。reader()函数连接数据库，然后等待查询数据库，直到出现同步事件。

7.4.10.1 延迟
默认的隔离级别是DEFERRED。使用延迟(deferred)模式会锁定数据库，但只是在变更真正开始时锁定一次。前面的所有例子都使用了延迟模式。

7.4.10.2 立即
采用立即(immediate)模式时，变更一开始时就会锁定数据库，在事务提交之前避免其他游标修改数据库。如果数据库有复杂的写操作，而且阅读器多于书写器，那么这种模式就很适合，因为在事务进行时不会阻塞阅读器。

7.4.10.3 互斥
互斥(exclusive)模式会对所有阅读器和书写器锁定数据库。如果数据库性能很重要，则应该限制使用这种模式，因为每个互斥的连接都会组数所有其他用户。

由于第一个书写器已经开始修改，所以阅读器和第二个书写器会阻塞，知道第一个书写器提交。sleep()调用会在书写器线程中引入一个认为的延迟，以强调其他连接被阻塞。

7.4.10.4 自动提交
连接的isolation_level参数还可以被设置为None,这会启用自动提交(autocommit)模式。启用自动提交时，每个execute()调用会在语句完成时立即提交。自动提交模式很适合持续时间短的事务，如向一个表插入少量数据。数据库锁定时间尽可能短，所以线程间竞争
资源的可能性更小。
sqlite3.autocommit.py中删除了commit()的显式调用，并将隔离级别设置为None,不过除此之外，这个方法与sqlite3_isolation_level.py相同。但输出是不同的，因为两个书写器线程会在阅读器开始查询之前完成工作。

import logging
import sqlite3
import sys
import threading
import time

logging.basicConfig(
    level=logging.DEBUG,
    format='%(asctime)s (%(threadName)-10s) %(message)s',
    )

db_filename = 'todo.db'
isolation_level = None

def writer():
    with sqlite3.connect(
        db_filename,
        isolation_level=isolation_level) as conn:

        cursor = conn.cursor()
        cursor.execute('update task set priority = priority + 1')
        logging.debug('waiting to synchronize')
        ready.wait()   # synchronize threads
        logging.debug('PAUSING')
        time.sleep(1)
        #conn.commit()
        logging.debug('CHANGES COMMITTED')

def reader():
    with sqlite3.connect(
        db_filename,
        isolation_level=isolation_level) as conn:

        cursor = conn.cursor()
        logging.debug('waiting to synchronize')
        ready.wait()  # synchronize threads
        logging.debug('wait over')
        cursor.execute('select * from task')
        logging.debug('SELECT EXECUTED')
        cursor.fetchall()
        logging.debug('results fetched')

if __name__ == '__main__':
    ready = threading.Event()

    threads = [
        threading.Thread(name='Reader 1',target=reader),
        threading.Thread(name='Reader 2',target=reader),
        threading.Thread(name='Writer 1',target=writer),
        threading.Thread(name='Writer 2',target=writer),
        ]

    [t.start() for t in threads]

    time.sleep(1)
    logging.debug('setting ready')
    ready.set()

    [t.join() for t in threads]

运行结果：