flask-sqlalchemy乐观锁的使用

一 .自己维护版本号

flask-sqlalchemy的表结构

class Shop_type(db.Model):
    __tablename__ = "tb_shop_type"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(50), nullable=False)
    icon = db.Column(db.String(200), nullable=True)
    sort = db.Column(db.Integer, nullable=False)
    version_id = db.Column(db.Integer, nullable=False)
    create_time = db.Column(db.TIMESTAMP, default=datetime.now)
    update_time = db.Column(db.TIMESTAMP, default=datetime.now)

1.使用ORM语句更新数据

shop_type = Shop_type.query.get(1)
# 第一种方式
result = db.session.query(Shop_type).filter_by(id=1, version_id=shop_type.version_id).update(
    {"version_id": Shop_type.version_id + 1, 'name': '123'})
# 第二种方式
result = db.session.query(Shop_type).filter(Shop_type.id == 1, Shop_type.version_id == shop_type.version_id).update({Shop_type.version_id: Shop_type.version_id + 1})
print(result)

2.使用原始sql语句更新数据

shop_type = Shop_type.query.get(1)
sql = "UPDATE tb_shop_type SET name=:name,version_id = version_id + 1 WHERE id=:id AND version_id=:version_id"
result = db.session.execute(sql, [{"name": '小明', 'id': 1, "version_id": shop_type.version_id}])
print(result.rowcount)
db.session.commit()

二.使用sqlalchemy参数自动维护版本号

1.直接指定字段为版本号，修改库存的同时相当于修改了版本号

# 秒杀券数据库表结构
class SeckillVoucher(db.Model, SerializerMixin):
    __tablename__ = "tb_seckill_voucher"
    __table_args__ = (
        CheckConstraint('stock >= 0', name='check_stock_unsign'),
    )
    voucherID = db.Column(db.BigInteger, primary_key=True, name="voucher_id")
    stock = db.Column(db.Integer, nullable=False)  # 库存
    beginTime = db.Column(db.DateTime, nullable=False, name="begin_time")
    endTime = db.Column(db.DateTime, nullable=False, name="end_time")
    createTime = db.Column(db.DateTime, default=datetime.now, name='create_time')
    updateTime = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now, name='update_time')
    __mapper_args__ = {
        "version_id_col": stock
    }

然后正常使用更新语句就可以

voucher = SeckillVoucher.query.get(id)
voucher.stock -= 1
db.session.commit()

2.添加额外的字段为版本号，不需要手动维护

class SeckillVoucher(db.Model, SerializerMixin):
    __tablename__ = "tb_seckill_voucher"
    __table_args__ = (
        CheckConstraint('stock >= 0', name='check_stock_unsign'),
    )
    voucherID = db.Column(db.BigInteger, primary_key=True, name="voucher_id")
    stock = db.Column(db.Integer, nullable=False)  # 库存
    version_id = db.Column(db.Integer, nullable=False)  # 版本号
    beginTime = db.Column(db.DateTime, nullable=False, name="begin_time")
    endTime = db.Column(db.DateTime, nullable=False, name="end_time")
    createTime = db.Column(db.DateTime, default=datetime.now, name='create_time')
    updateTime = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now, name='update_time')
    __mapper_args__ = {
        "version_id_col": version_id,
        "version_id_generator": lambda version: version + 1, # 也可以用uuid.uuid4().hex 官方推荐
    }

然后还是正常执行更新语句

voucher = SeckillVoucher.query.get(id)
voucher.stock -= 1
db.session.commit()

之后用脚本批量扣除库存后 stock 500 -> 410 , version 0 -> 90 说明版本号没有问题

sqlalchemy 官方文档 (https://docs.sqlalchemy.org/en/14/orm/versioning.html)

最后还有，如果要查看ORM对应sql语句可以在上下文环境中使用

from sqlalchemy import event
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()

with app.app_context():
    @event.listens_for(db.engine, "before_cursor_execute")
    def log_execute(conn, cursor, statement, params, context, executemany):
        print(statement)  # sql语句
        print(params)  # 传递参数

这样，每次使用ORM就可以输出对应的sql语句了！