sqlalchemy lock and atomic

prepare:

Prepare a table

set evn

DBUSER=root
DBPASS=123
DBNAME=cyborg
TBNAME="atomic"
RDNAME="s0"

DB create

DBNAME=atomic
mysql -u$DBUSER -p$DBPASS <<< "create DATABASE $DBNAME"

Delete DB

mysql -u$DBUSER -p$DBPASS <<< "drop database $DBNAME"

table create

mysql -u$DBUSER -p$DBPASS $DBNAME <<< "CREATE TABLE $TBNAME(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
PRIMARY KEY (id))ENGINE=InnoDB DEFAULT CHARSET=utf8;
desc $TBNAME"

Delete table

mysql -u$DBUSER -p$DBPASS $DBNAME <<< "DROP TABLE $TBNAME"

insert table

mysql -u$DBUSER -p$DBPASS $DBNAME <<< "INSERT INTO $TBNAME
(name)
VALUES
("s0");"

update table

TBNAME="atomic"
RDNAME="s0"
mysql -u$DBUSER -p$DBPASS $DBNAME <<<"UPDATE $TBNAME SET name='$RDNAME' WHERE id=1"

数据库锁(DB lock)

sqlalchemy 使用`with_lockmode锁住DB锁（不是`sqlalchemy 实现的锁`）`

test.py 如下：

from sqlalchemy import create_engine,Table,Column,Integer,String,MetaData,ForeignKey
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base
import time
import sys
print(sys.argv)
mode = sys.argv[1] if len(sys.argv) > 1 else "get"
print("DB for %s" % mode)

Base = declarative_base()
Session = sessionmaker()

engine = create_engine("mysql://root:123@localhost/cyborg")
Session.configure(bind=engine)
session = Session()

class Atomic(Base):
    __tablename__ = 'atomic'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    def __str__(self):
        return 'Atomic[%d, %s]' % (self.id, self.name)

at_id = 1
filer_name = "s0"
exp_name = "s1"
wait = 10
print("start to get lock", time.strftime("%H:%M:%S"))
if mode == "get":
    lock = session.query(Atomic).with_for_update().filter(
        Atomic.id == at_id).first()
    # remove with_for_update,  "update" mode no need wait to get lock
    print("get lock: ", time.strftime("%H:%M:%S"))
    print(lock)
    print("lock the record and wait for %s", wait)
    time.sleep(wait)
    session.commit()
else:
    lock = session.query(Atomic).filter_by(name=filer_name).with_for_update().update(
        {"name": exp_name}, synchronize_session="fetch")
    print(lock)
    print("get lock: ", time.strftime("%H:%M:%S"))
    print("update the record and wait for %s", wait)
    time.sleep(wait)
    session.commit()
print(lock)

先执行update，再read

在terminal 1执行：

python2 test.py update

在terminal 2执行：　　

python2 test.py

很明显读取需要等待时间。

或者　　

mysql -u$DBUSER -p$DBPASS $DBNAME <<< "select * from $TBNAME"

不需要等待时间。

先执行read ’python2 test.py’，再update也需要等待。

New in version 0.9.0: Query.with_for_update() supersedes the Query.with_lockmode() method.

使用案例

结论：

　　update的时候，即使不指定with_for_update, 也会自动获取这个update锁。

仅仅query的时候，如果不指定with_for_update，那么立即执行，不会获取这个锁。

sqlalchemy session 执行 delete 时 synchronize_session 策略 (update 同样适用)

False：不同步 session，如果被删除的 objects 已经在 session 中存在，在 session commit 或者 expire_all 之前，这些被删除的对象都存在 session 中。

不同步可能会导致获取被删除 objects 时出错。

fetch: 删除之前从 db 中匹配被删除的对象并保存在 session 中，然后再从 session 中删除，这样做是为了让 session 的对象管理 identity_map 得知被删除的对象究竟是哪些以便更新引用关系。

evaluate: 默认值。根据当前的 query criteria 扫描 session 中的 objects，如果不能正确执行则抛出错误，这句话也可以理解为，如果 session 中原本就没有这些被删除的 objects，扫描当然不会发生匹配，相当于匹配未正确执行。

from sqlalchemy import create_engine,Table,Column,Integer,String,MetaData,ForeignKey
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
Session = sessionmaker()

engine = create_engine("mysql://root:123@localhost/cyborg")
Session.configure(bind=engine)
session = Session()

class Atomic(Base):
    __tablename__ = 'atomic'
    id = Column(Integer, primary_key=True)
    name = Column(String)

q = session.query(Atomic)
a = q.filter_by(name="s1")
print(type(a))
print(a)
at = a.one()
print(a.one())
import ipdb; ipdb.set_trace()
# "fetch"  "evaluate"
a1 =a.update({"name": "s2"}, synchronize_session=False)
print(type(a1))
print(a1)
print(Atomic.name=="s1")
session.commit()
print(a.one())

REF:

query example

SQL Atomic Operation on UPDATE and DELETE

sqlalchemy session

Cyborg DB example:

mysql:

DBUSER=root
DBPASS=y0devstk
DBNAME=cyborg
TBNAME="attach_handles"
RDNAME=0
FIELD="in_use"
QR_NAME="deployable_id"

QR_VALUE=`mysql -u$DBUSER -p$DBPASS $DBNAME <<<"SELECT $QR_NAME FROM $TBNAME LIMIT 1" | tail -n 1`
mysql -u$DBUSER -p$DBPASS $DBNAME <<<"UPDATE $TBNAME SET $FIELD=$RDNAME WHERE $QR_NAME=$QR_VALUE"
mysql -u$DBUSER -p$DBPASS $DBNAME <<<"SELECT $FIELD FROM $TBNAME WHERE $QR_NAME=$QR_VALUE"

python

import cyborg.conf
import cyborg.db.sqlalchemy.models
from oslo_db import options
from cyborg import context
from cyborg import db as db_api
user="root"
psw="y0devstk"
# connection_debug=1,
# connection_trace=True,
# set_override
CONF = cyborg.conf.CONF
CONF(["--config-file=/etc/cyborg/cyborg.conf"])
options.set_defaults(CONF)
# options.set_defaults(CONF,
# connection="mysql+pymysql://%s:%s@127.0.0.1/cyborg?charset=utf8" % (user, psw))
print(CONF["database"].items())
# from oslo_context import context
ct = context.get_admin_context()
sqlalchemy_api = db_api.get_instance()
try:
    r = sqlalchemy_api.attach_handle_list(ct)
    r0 = r[0]
    print(r0.in_use)
    # r = sqlalchemy_api.attach_handle_allocate(ct, r0.attach_type, r0.deployable_id)
    r1 = sqlalchemy_api.attach_handle_allocate(ct, r0.deployable_id)
    print(r1.in_use)
except Exception as e:
    print(e)

openstack oslo.config