1、数据库事务

数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作可以全部执行,否则全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

1）事务具有ACID属性

Atomic原子性是事务应作为一个工作单元,事务处理完成，所有的工作要么都在数据库中保存下来，要么完全回滚，全部不保留。

Consistent一致性是事务完成或者撤销后，都应该处于一致的状态。

Isolated隔离性是多个事务同时进行，它们之间应该互不干扰。事务查看数据时数据所处的状态，可以是另一并发事务修改它之前的状态，也可以是另一事务修改它之后的状态，事务不会查看中间状态的数据。

Durable永久性是事务提交以后，所做的操作就被永久的保存下来。

2）事务隔离级别

为了达到事务的四大特性，数据库定义了4种不同的事务隔离级别，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读等问题。

3）事件实例

create procedure TransferMoeny
(
    @FromAccountNo varchar(50),-- 转出账号
    @ToAccountNo varchar(50),--转入账号
    @MoneyCount money--转账金额
)
as
--判断账号是否存在
if exists (select 1 from accounts where aId = @FromAccountNo) 
begin
    if exists (select 1 from accounts where aId = @ToAccountNo) 
    begin
        --判断转出金额是否大于当前余额
        if (select balance from accounts where aId = @FromAccountNo) >= @MoneyCount
        begin
            --开始转账
            begin transaction
            insert into [record] ([aId],[type], [money]) values(@FromAccountNo, -1,@MoneyCount)
            if @@error <> 0
            begin
                rollback transaction--发生错误则回滚事务，无条件退出l
                return
            end
                       insert into [record] ([aId],[type], [money]) values(@ToAccountNo, 1,@MoneyCount)
            if @@error <> 0
            begin
                rollback tran
                return
            end
            commit transaction --两条语句都完成，提交事务
        end
        else    
            raiserror ('转账金额不能大于该账号的余额',16,1)
    end
    else
    raiserror ('转入账号不存在',16,1)

end
else
    raiserror ('转出账号不存在',16,1)

2、Pony中事务

数据库事务是一个逻辑工作单位，它可以由一个或多个查询组成。事务是原子式的，当事务对数据库进行更改时，若事务提交时所有的更改都成功，否则事务回滚时所有的更改都被撤销。Pony使用db_session提供了自动事务管理。

1）用于事务处理的函数

commit()

rollback()

flush()

2）数据库对象对应函数

Database.commit()

Database.rollback()

Database.flush()

3）乐观的并发控制

默认情况下，Pony使用乐观并发控制概念来提高性能。Pony不会获取数据库的行锁，取而代之的是，它会验证是否有其他事务修改了它已读取或正在尝试修改的数据。如果检查到冲突的修改，提交事务时就会抛出一个异常：

OptimisticCheckError, 'Object XYZ was updated outside of current transaction'

参考文档：

https://docs.ponyorm.org/transactions.html#optimistic-concurrency-control

https://docs.ponyorm.org/transactions.html#optimistic-concurrency-control

4）悲观锁定

并发情况下，可能需要在数据库中锁定一个对象，以防止其他事务修改同一记录。锁定应该使用SELECT FOR UPDATE查询来完成，可以调用Query.for_update()方法。

select(p for p in Product if p.price > 100).for_update()

需要锁定单个对象，可以使用实体的get_for_update方法：

Product.get_for_update(id=11)

使用for_update()锁定一个对象，而这个对象已经被另一个事务锁定了，请求将需要等待直到行级锁被释放。为了防止操作等待其他事务提交，请使用 nowait=True参数，

select(p for p in Product if p.price > 100).for_update(nowait=True)

或

Product.get_for_update(id=123, nowait=True)

注意：在这种情况下，如果选择的行(单行/多行)不能立即锁定，则请求报告一个错误，而不是等待。悲观锁定的主要缺点是性能下降，因为要耗费数据库锁和限制并发量。

5）使用 SERIALIZABLE 隔离级别

如使用 SERIALIZABLE 隔离级别，数据库将不允许在提交过程中抛出一个异常来提交第二个事务，此方法的缺点是，这个级别需要更多的系统资源。使用示例如下，

@db_session(serializable=True)
def transfer_money(account_id1, account_id2, money):
    account1 = Account[account_id1]
    account2 = Account[account_id2]
    if balance > account1.balance:
        raise ValueError("Not enough funds")
    account1.balance -= money
    account2.balance += money

3、db_session

与数据库交互的代码必须放在数据库会话中，会话设置了与数据库会话的边界。每个与数据库交互的应用程序线程都建立了一个单独的数据库会话，并使用一个单独的Identity Map实例。Identity Map作为缓存，当通过主键或唯一键访问一个对象，而这个对象已经存储在Identity Map中时，这个Identity Map可以帮助避免数据库查询。

为了使用数据库会话与数据库工作，可以使用@db_session()装饰器或db_session()上下文管理器。当会话结束时，它会执行以下操作：

如果数据被改变，并且没有发生异常，则提交事务，否则回滚事务。

返回数据库连接到连接池。

清除Identity Map缓存。

当Pony从数据库中读取对象时，它会将这些对象放到Identity Map中。之后，当你更新一个对象的属性(update)，创建(create)或删除(delete)一个对象时，这些变化会先累积到Identity Map中。

这些更改将在事务提交时，或在调用select(), get(), exists()或 execute()方法之前，保存在数据库中。

1）使用@db_session()装饰器

@db_session
def has_user(username):
    return User.exists(username=username)

2）db_session()上下文管理器的使用

def process_request():
    with db_session:
        u = User.get(username=username)

3）事务范围

通常情况下，在db_session()中会有一个单一的事务。没有显式命令来启动一个事务。一个事务从发送到数据库的第一个SQL查询开始。在发送第一个查询之前，Pony会从连接池中获取一个数据库连接，接下来的任何SQL查询都将在同一事务的上下文中执行。

当一个事务使用commit()或rollback()调用或隐式地离开db_session()作用域时，事务就结束了。

@db_session
def func():
    #一个新的事务被启动
    p = Product[11]
    p.price +=10
    # 数据库会话缓存将被自动清除
    #数据库连接将被返回到池中

注意：SQLite的Python驱动不会在SELECT语句上开始一个事务。它只在一个可以修改数据库的语句上开始一个事务：insert, update, delete。其他驱动在任何SQL语句上启动一个事务，包括SELECT。

4）同一个db_session内的多个事务

@db_session
def func1():
    p1 = Product[11]
    p1.price +=10
    commit() # 第一个事务被提交
    p2 = Product[22] # 另一个新的事务开始了
    p2.price -= 10

5）同时使用多个数据库

Pony可以与多个数据库同时工作。

from pony.orm import *

db1 = Database()

class User(db1.Entity):
    name = Required(str)
    uid = Required(int, unique=True, nullable=True)
    face = Required(str)
    info = Required(str)
    gift_rec = Set('GiftRec')
    
db.bind(provider='mysql', user='admin', password='123456', host='129.204.43.2', port=44444, database='mydata')

db2 = Database()
class GiftRec(db2.Entity):
    key = Required(str, unique=True)
    room_id = Required(int)
    gift_id = Required(int)
    gift_name = Required(str)
    gift_type = Required(str)
    sender_id = Required(User)
    sender_type = Required(int, nullable=True)
    created_time = Required(datetime.datetime, default=datetime.datetime.now)
    status = Required(int)

db2.bind(provider='sqlite', filename='filename', create_db=True)

@db_session
get_data(user_id, giftrec_id):
    u = User[user_id]
    g = GiftRe[giftrec_id]

get_data()函数中退出时，Pony会对两个数据库执行commit()或rollback()，

如需要在退出函数之前提交到一个数据库，则可以使用db1.commit()或db2.commit()方法。

4、db_session的嵌套

如递归地进入db_session()作用域，例如，从另一个用db_session()装饰符装饰的函数中调用一个用db_session()装饰符装饰的函数，Pony不会创建一个新的会话，而是两个函数共享同一个会话，数据库会话在离开最外层的db_session()装饰器或上下文管理器的范围时结束。但需要注意以下情况：

1）如果内部的db_session()使用了与外部的db_session()不兼容的选项（ddl=True或serializable=True），Pony会抛出一个异常。

2）对于sql_debug 选项，Pony 在内部的db_session()中使用新的 sql_debug 选项值，并在返回到外部的 db_session()时恢复它。

3）其他选项（strict、optimistic、immediate和retry）对于内部db_session()来说是被忽略的。

如在内部的db_session()内部调用了rollback()，它将被应用到外部db_session()。

有些数据库支持嵌套事务，但目前Pony不支持。

使用yield只能使用@db_session，而不能使用with db_session。如下，

@db_session
def my_generator( x ):
    obj = MyEntity.get(id=x)
    yield obj

5、db_session缓存

Pony在几个阶段缓存数据，以提高性能。如下：

1）生成器表达式转换的结果

如同一个生成器表达式查询在程序中被多次使用，那么它将只被翻译成SQL一次。这个缓存对整个程序是全局的，而不仅仅是单个数据库会话的缓存。

2）从数据库中创建或加载的对象

Pony会将这些对象保存在Identity Map中。这个缓存在离开db_session()作用域或事务回滚时被清除。

3）查询结果数据

如果使用相同的参数再次调用相同的查询，Pony会从缓存中返回查询结果。但是任何一个实体实例被改变，这个缓存就会被清除。这个缓存在离开db_session()作用域或事务回滚时被清除。