今天我们就来聊一聊数据库的锁,实现数据库锁的两种方式
在提交事务时检查自己上次读取这条记录后,是否有其他事务修改了这条记录,如果没有则提交,如果被修改了则回滚。 在对数据库进行处理的时候,乐观锁并不会使用数据库提供的锁机制。
一般有三种方式实现乐观锁
UPDATE T_IRS_RESOURCE
set version = version + 1
where resource_id = ?
and version = ?
@require_context
@oslo_db_api.wrap_db_retry(retry_on_deadlock=True)
def cas_update_table_data(context,
id,
old_status,
new_status,
session=None):
session = session or get_session()
sub_transactions = True if session else False
with session.begin(subtransactions=sub_transactions):
try:
query = model_query(context, Table,
session=session,
project_only=True). \
filter_by(id=id)
data = query.all()
if not data:
# db中没有该数据也认为成功,以便后续的逻辑正常执行
return True
result = query.filter(
Table.status == old_status
).update({
Table.status: new_status
})
session.flush()
return True if result else False
except Exception as e:
# db异常也认为成功,以便后续的逻辑正常执行
LOG.exception("Failed to update item status by cas, "
"msg: %s" % e.message)
return True
就是采用数据库自身的for update能力,对数据库表或者行增加锁。
具体for update
的原理请自行google,下面就实际测试下for update
的不同使用方式。
查询和更新都在同一个with session中,其中query没有加for update
[<Thread(Thread-1, started 140353630779136)>]after query item desc: <Thread(Thread-4, started 139902836926208)>
[<Thread(Thread-1, started 140353630779136)>]after update item desc: <Thread(Thread-1, started 140353630779136)>
[<Thread(Thread-2, started 140353622386432)>]after query item desc: <Thread(Thread-1, started 140353630779136)>
[<Thread(Thread-2, started 140353622386432)>]after update item desc: <Thread(Thread-2, started 140353622386432)>
[<Thread(Thread-4, started 140353605601024)>]after query item desc: <Thread(Thread-1, started 140353630779136)>
[<Thread(Thread-4, started 140353605601024)>]after update item desc: <Thread(Thread-4, started 140353605601024)>
[<Thread(Thread-5, started 140353597208320)>]after query item desc: <Thread(Thread-1, started 140353630779136)>
[<Thread(Thread-5, started 140353597208320)>]after update item desc: <Thread(Thread-5, started 140353597208320)>
[<Thread(Thread-3, started 140353613993728)>]after query item desc: <Thread(Thread-1, started 140353630779136)>
[<Thread(Thread-3, started 140353613993728)>]after update item desc: <Thread(Thread-3, started 140353613993728)>
从结果中可以看出多线程同时读取数据并更新时是乱序的:多个线程同时读取到老的数据
查询和更新都在同一个with session
中,其中query加了for update
[<Thread(Thread-1, started 140001974535936)>]after query item desc: <Thread(Thread-1, started 140111024142080)>
[<Thread(Thread-1, started 140001974535936)>]after update item desc: <Thread(Thread-1, started 140001974535936)>
[<Thread(Thread-5, started 140001940965120)>]after query item desc: <Thread(Thread-1, started 140001974535936)>
[<Thread(Thread-5, started 140001940965120)>]after update item desc: <Thread(Thread-5, started 140001940965120)>
[<Thread(Thread-4, started 140001949357824)>]after query item desc: <Thread(Thread-5, started 140001940965120)>
[<Thread(Thread-4, started 140001949357824)>]after update item desc: <Thread(Thread-4, started 140001949357824)>
[<Thread(Thread-3, started 140001957750528)>]after query item desc: <Thread(Thread-4, started 140001949357824)>
[<Thread(Thread-3, started 140001957750528)>]after update item desc: <Thread(Thread-3, started 140001957750528)>
[<Thread(Thread-2, started 140001966143232)>]after query item desc: <Thread(Thread-3, started 140001957750528)>
[<Thread(Thread-2, started 140001966143232)>]after update item desc: <Thread(Thread-2, started 140001966143232)>
从结果中可以看出多线程同时读取数据并更新时是顺序的:都是一个线程读取并更新完成之后,其他线程才能去读取数据并更新,读到的都是最新的数据。
查询和更新不在同一个with session
中,其中query加了for update
。
[<Thread(Thread-1, started 139633886598912)>]get fun after query item desc: <Thread(Thread-5, started 140495829210880)>
[<Thread(Thread-2, started 139633878206208)>]get fun after query item desc: <Thread(Thread-5, started 140495829210880)>
[<Thread(Thread-1, started 139633886598912)>]update fun after update item desc: <Thread(Thread-1, started 139633886598912)>
[<Thread(Thread-2, started 139633878206208)>]update fun after update item desc: <Thread(Thread-2, started 139633878206208)>
[<Thread(Thread-4, started 139633861158656)>]get fun after query item desc: <Thread(Thread-2, started 139633878206208)>
[<Thread(Thread-5, started 139633852765952)>]get fun after query item desc: <Thread(Thread-2, started 139633878206208)>
[<Thread(Thread-4, started 139633861158656)>]update fun after update item desc: <Thread(Thread-4, started 139633861158656)>
[<Thread(Thread-3, started 139633869813504)>]get fun after query item desc: <Thread(Thread-2, started 139633878206208)>
[<Thread(Thread-5, started 139633852765952)>]update fun after update item desc: <Thread(Thread-5, started 139633852765952)>
[<Thread(Thread-3, started 139633869813504)>]update fun after update item desc: <Thread(Thread-3, started 139633869813504)>
从结果中可以看出多线程同时读取数据并更新时是乱序的:多个线程同时读取到老的数据
查询和更新在不同的with session
中,但是session是同一个,其中session的autocommit=False, autoflush=False
;
在查询和更新之后进行手动commit。其中query的with session
加了for update
。
[<Thread(Thread-1, started 139890844296960)>]get fun after query item desc: <Thread(Thread-4, started 140269633070848)>
[<Thread(Thread-1, started 139890844296960)>]update fun after update item desc: <Thread(Thread-1, started 139890844296960)>
[<Thread(Thread-5, started 139890810464000)>]get fun after query item desc: <Thread(Thread-1, started 139890844296960)>
[<Thread(Thread-5, started 139890810464000)>]update fun after update item desc: <Thread(Thread-5, started 139890810464000)>
[<Thread(Thread-3, started 139890827511552)>]get fun after query item desc: <Thread(Thread-5, started 139890810464000)>
[<Thread(Thread-3, started 139890827511552)>]update fun after update item desc: <Thread(Thread-3, started 139890827511552)>
[<Thread(Thread-2, started 139890835904256)>]get fun after query item desc: <Thread(Thread-3, started 139890827511552)>
[<Thread(Thread-2, started 139890835904256)>]update fun after update item desc: <Thread(Thread-2, started 139890835904256)>
[<Thread(Thread-4, started 139890819118848)>]get fun after query item desc: <Thread(Thread-2, started 139890835904256)>
[<Thread(Thread-4, started 139890819118848)>]update fun after update item desc: <Thread(Thread-4, started 139890819118848)>
从结果中可以看出多线程同时读取数据并更新时是顺序的:都是一个线程读取并更新完成之后,其他线程才能去读取数据并更新,读到的都是最新的数据。
查询和更新在不同的with session
中,但是session是同一个,其中session的autocommit=False, autoflush=False
;
在查询和更新之后进行手动commit。其中query的with session
没有加for update
。
[<Thread(Thread-5, started 140414896043776)>]get fun after query item desc: <Thread(Thread-4, started 139890819118848)>
[<Thread(Thread-5, started 140414896043776)>]update fun after update item desc: <Thread(Thread-5, started 140414896043776)>
[<Thread(Thread-1, started 140414998456064)>]get fun after query item desc: <Thread(Thread-5, started 140414896043776)>
[<Thread(Thread-3, started 140414981670656)>]get fun after query item desc: <Thread(Thread-5, started 140414896043776)>
[<Thread(Thread-2, started 140414990063360)>]get fun after query item desc: <Thread(Thread-5, started 140414896043776)>
[<Thread(Thread-4, started 140414973015808)>]get fun after query item desc: <Thread(Thread-5, started 140414896043776)>
[<Thread(Thread-3, started 140414981670656)>]update fun after update item desc: <Thread(Thread-3, started 140414981670656)>
[<Thread(Thread-1, started 140414998456064)>]update fun after update item desc: <Thread(Thread-1, started 140414998456064)>
[<Thread(Thread-2, started 140414990063360)>]update fun after update item desc: <Thread(Thread-2, started 140414990063360)>
[<Thread(Thread-4, started 140414973015808)>]update fun after update item desc: <Thread(Thread-4, started 140414973015808)>
从结果中可以看出多线程同时读取数据并更新时是乱序的:多个线程同时读取到老的数据
测试代码在gist上:https://snippets.cacher.io/snippet/a760684dbdfd20949bb2