DBレコードを排他ロックする方法を教えてください。

DBレコードの排他ロックは、modelモジュールを用いてSQLを組み立てることで以下のように記述することができます。

>>> traceon(sql=True)↵
Current trace mode is True
namespace=False, sql=True
>>> async with model.aiodb() as conn:↵
...     async with conn.begin() as tran:↵
...         await conn.execute(model.Resource.select().where(model.Resource.c.name=="resourceA").with_for_update())↵...排他ロック(FOR UPDATE)
... ↵
↵
[SQL] 2021-03-30T10:02:31.258816+09:00
BEGIN
[SQL] 2021-03-30T10:02:31.261912+09:00
SELECT `Resource`.instance, `Resource`.xid, `Resource`.xname, `Resource`.id, `Resource`.name 
FROM `Resource` 
WHERE `Resource`.name = 'resourceA' FOR UPDATE
[SQL] 2021-03-30T10:02:31.262653+09:00
COMMIT
>>> async with model.aiodb() as conn:↵
...     async with conn.begin() as tran:↵
...         await conn.execute(model.Resource.select().where(model.Resource.c.name=="resourceA").with_for_update(read=True))↵...共有ロック(FOR SHARE)
... ↵
↵
[SQL] 2021-03-30T10:03:28.385709+09:00
BEGIN
[SQL] 2021-03-30T10:03:28.388575+09:00
SELECT `Resource`.instance, `Resource`.xid, `Resource`.xname, `Resource`.id, `Resource`.name 
FROM `Resource` 
WHERE `Resource`.name = 'resourceA' LOCK IN SHARE MODE
[SQL] 2021-03-30T10:03:28.389219+09:00
COMMIT
>>> traceoff()↵
Trace mode has been DISABLED.
>>>

ATOMオブジェクトの場合は、retrieveやloadクラスメソッドに対してコネクションオブジェクトを指定してfor_update(排他ロックモード)もしくはfor_share(共有ロックモード)キーワード引数を指定することでロックを制御することもできます。for_updateとfor_share双方をTrueにした場合は、for_updateが優先されます。

>>> traceon(sql=True)↵
Trace mode has been ACTIVATED. 'hotspot.scenario.'
>>> async with model.aiodb() as conn:↵
...     async with conn.begin() as tran:↵
...         await atom.Resource.retrieve(conn=conn, for_update=True, name="resourceA")↵
... ↵
↵
[SQL] 2021-03-30T10:07:30.172120+09:00
BEGIN
[SQL] 2021-03-30T10:07:30.179407+09:00
SELECT `Resource`.instance, `Resource`.xid, `Resource`.xname, `Resource`.id, `Resource`.name 
FROM `Resource` 
WHERE `Resource`.name = 'resourceA' FOR UPDATE
[SQL] 2021-03-30T10:07:30.184423+09:00
SELECT `Resource`.instance, `Resource`.xid, `Resource`.xname, `Resource`.id, `Resource`.name 
FROM `Resource` 
WHERE `Resource`.instance = 'UmVzb3VyY2U6MzI5NmUxM2U5MGYzMTFlYmE5NjhhY2RlNDgwMDExMjI=' OR `Resource`.id = 'UmVzb3VyY2U6MzI5NmUxM2U5MGYzMTFlYmE5NjhhY2RlNDgwMDExMjI=' FOR UPDATE
[SQL] 2021-03-30T10:07:30.189537+09:00
COMMIT
>>> async with model.aiodb() as conn:↵
...     async with conn.begin() as tran:↵
...         await atom.Resource.retrieve(conn=conn, for_share=True, name="resourceA")↵
... ↵
↵
[SQL] 2021-03-30T10:07:43.096747+09:00
BEGIN
[SQL] 2021-03-30T10:07:43.099849+09:00
SELECT `Resource`.instance, `Resource`.xid, `Resource`.xname, `Resource`.id, `Resource`.name 
FROM `Resource` 
WHERE `Resource`.name = 'resourceA' LOCK IN SHARE MODE
[SQL] 2021-03-30T10:07:43.103590+09:00
SELECT `Resource`.instance, `Resource`.xid, `Resource`.xname, `Resource`.id, `Resource`.name 
FROM `Resource` 
WHERE `Resource`.instance = 'UmVzb3VyY2U6MzI5NmUxM2U5MGYzMTFlYmE5NjhhY2RlNDgwMDExMjI=' OR `Resource`.id = 'UmVzb3VyY2U6MzI5NmUxM2U5MGYzMTFlYmE5NjhhY2RlNDgwMDExMjI=' LOCK IN SHARE MODE
[SQL] 2021-03-30T10:07:43.105746+09:00
COMMIT
>>> async with model.aiodb() as conn:↵
...     async with conn.begin() as tran:↵
...         await atom.Resource.load("resourceA", conn=conn, for_update=True)↵
... ↵
↵
[SQL] 2021-03-30T10:13:12.370639+09:00
BEGIN
[SQL] 2021-03-30T10:13:12.374583+09:00
SELECT `Resource`.instance, `Resource`.xid, `Resource`.xname, `Resource`.id, `Resource`.name 
FROM `Resource` 
WHERE `Resource`.instance = 'resourceA' OR `Resource`.id = 'resourceA' FOR UPDATE
[SQL] 2021-03-30T10:13:12.375362+09:00
COMMIT
>>> async with model.aiodb() as conn:↵
...     async with conn.begin() as tran:↵
...         await atom.Resource.load("resourceA", conn=conn, for_share=True)↵
... ↵
↵
[SQL] 2021-03-30T10:13:22.786775+09:00
BEGIN
[SQL] 2021-03-30T10:13:22.792347+09:00
SELECT `Resource`.instance, `Resource`.xid, `Resource`.xname, `Resource`.id, `Resource`.name 
FROM `Resource` 
WHERE `Resource`.instance = 'resourceA' OR `Resource`.id = 'resourceA' LOCK IN SHARE MODE
[SQL] 2021-03-30T10:13:22.793184+09:00
COMMIT
>>> traceoff()↵
Trace mode has been DISABLED.
>>>