sqlalchemy的事物加锁问题，求助

[jiffies]

 session = DBSession()


 #spot = session.execute("select * from spot where id=1 for update;")
 #spot.fetchall()
 send_spot = session.query(Spot).filter(

                Spot.id == 1).with_for_update().one()
spot.name='qwerdsdfdf'
 #session.execute("update spot set name='ccbbbaaaaa' where id=1;")
 session.commit()

如上所示，想在更新时加个排它锁，但是执行结果是(1205, u'Lock wait timeout exceeded; try restarting transaction')

通过查询事物和锁的信息，发现会产生2个线程2个事物，update语句在等待第一条的锁。可是这明明是一个事物，为什么变成2个了？

如果把update语句用execute执行就会正常。求助！

[jiffies]

show engine innodb status结果如下

TRANSACTIONS

------------

Trx id counter 92098

Purge done for trx's n:o < 92067 undo n:o < 0 state: running but idle

History list length 48

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 281479701573184, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 281479701575896, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 281479701574088, not started

0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 92097, ACTIVE 4 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 71, OS thread handle 123145524420608, query id 7423 localhost 127.0.0.1 root updating

UPDATE upstair_send_spot SET floor='rfv', updated_at=now() WHERE upstair_send_spot.id = 1

------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 2027 page no 3 n bits 88 index PRIMARY of table `pi_talaris_test`.`upstair_send_spot` trx id 92097 lock_mode X locks rec but not gap waiting

Record lock, heap no 21 PHYSICAL RECORD: n_fields 13; compact format; info bits 0

 0: len 8; hex 8000000000000001; asc         ;;

 1: len 6; hex 0000000167a2; asc     g ;;

 2: len 7; hex 2300000198028b; asc #      ;;

 3: len 8; hex 8000000000000002; asc         ;;

 4: len 8; hex 8000000000000001; asc         ;;

 5: len 10; hex 63636262626161616161; asc ccbbbaaaaa;;

 6: len 19; hex 5b2261626364222c207522e794b5e6a2af225d; asc ["abcd", u"      "];;

 7: len 1; hex 81; asc  ;;

 8: len 0; hex ; asc ;;

 9: SQL NULL;

 10: len 5; hex 99a046eb04; asc   F  ;;

 11: len 4; hex 5b3b55a4; asc [;U ;;

 12: len 6; hex 5b3b55a41914; asc [;U   ;;

------------------

---TRANSACTION 92096, ACTIVE 61 sec

2 lock struct(s), heap size 1136, 1 row lock(s)

MySQL thread id 70, OS thread handle 123145525534720, query id 7420 localhost 127.0.0.1 root

在 2018年7月3日星期二 UTC+8下午7:43:23，jiffies写道：

[jiffies]

找到原因了，原来sqlalchemy会在很多地方调用flush，比如autoflush，或者commit->prepare->flush的时候,而sqlalchemy奇葩的地方在于flush会强行开启一个嵌套事物，所以如果用修改instance的方式更新，就会触发嵌套事物，两个事物竞争，update语句就在等待前一条的for update锁。解决方案是使用

with session.no_autoflush:
     spot = session.query(Spot).filter(Spot.id == 1).with_for_update().one()
     session.query(Spot).filter(Spot.id == 1).update({"name": '3456'}, synchronize_session=False)

避开flush的调用

在 2018年7月3日星期二 UTC+8下午7:43:23，jiffies写道：

 session = DBSession()