今天执行show processlist 发现大量的commit;查看锁信息,发现基本上都是
+----------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| waiting_trx_id | waiting_thread | waiting_query
| blocking_trx_id | blocking_thread | blocking_query
+----------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------+-----------------+------------------------------------------------------------------------------------------------------------
| 1131726E | 501885 | update `a`set `a`.`money` = `a`.`money` -NAME_CONST('pay_money',6)where `a`.`id` =NAME_CONST('in_user_id',6106041) and a.money >=NAME_CONST('pay_money',6)| 113159E3|
501939 | update `a`set `a`.`money` = `a`.`money` -NAME_CONST('pay_money',6)where `a`.`id` =NAME_CONST('in_user_id',6106041) and a.money >=NAME_CONST('pay_money',6)
| 1131726E | 501885 | update `a`set `a`.`money` = `a`.`money` -NAME_CONST('pay_money',6)where `a`.`id` =NAME_CONST('in_user_id',6106041) and a.money >=NAME_CONST('pay_money',6)| 1131432D|
500960 | commit
| 11317266 | 501631 | update `a`set `a`.`money` = `a`.`money` -NAME_CONST('pay_money',15)where `a`.`id` =NAME_CONST('in_user_id',6232465) and a.money >= NAME_CONST('pay_money',15) | 11316F66|
501485 | update `a`set `a`.`money` = `a`.`money` -NAME_CONST('pay_money',15)where `a`.`id` =NAME_CONST('in_user_id',6232465) and a.money >=NAME_CONST('pay_money',15)
| 11317266 | 501631 | update `a`set `a`.`money` = `a`.`money` -NAME_CONST('pay_money',15)where `a`.`id` =NAME_CONST('in_user_id',6232465) and a.money >= NAME_CONST('pay_money',15) | 11316237|
501852 | commit
| 1131720C | 501785 | update `a`set `a`.`money` = `a`.`money` -NAME_CONST('pay_money',6)where `a`.`id` =NAME_CONST('in_user_id',5214183) and a.money >=NAME_CONST('pay_money',6)| 1131624D|
501699 | update `a`set `a`.`money` = `a`.`money` -NAME_CONST('pay_money',6)where `a`.`id` =NAME_CONST('in_user_id',5214183) and a.money >=NAME_CONST('pay_money',6)
| 1131720C | 501785 | update `a`set `a`.`money` = `a`.`money` -NAME_CONST('pay_money',6)where `a`.`id` =NAME_CONST('in_user_id',5214183) and a.money >=NAME_CONST('pay_money',6)| 113146D2|
501710 | update `a`set `a`.`money` = `a`.`money` -NAME_CONST('pay_money',6)where `a`.`id` =NAME_CONST('in_user_id',5214183) and a.money >=NAME_CONST('pay_money',6)
| 1131720C | 501785 | update `a`set `a`.`money` = `a`.`money` -NAME_CONST('pay_money',6)where `a`.`id` =NAME_CONST('in_user_id',5214183) and a.money >=NAME_CONST('pay_money',6)| 11313088|
502124 | commit
| 113171EA | 501711 | update `a`set `a`.`money` = `a`.`money` -NAME_CONST('pay_money',9)where `a`.`id` =NAME_CONST('in_user_id',5889087) and a.money >=NAME_CONST('pay_money',9)| 11316383|
501887 | update `a`set `a`.`money` = `a`.`money` -NAME_CONST('pay_money',9)where `a`.`id` =NAME_CONST('in_user_id',5889087) and a.money >=NAME_CONST('pay_money',9)
| 113171EA | 501711 | update `a`set `a`.`money` = `a`.`money` -NAME_CONST('pay_money',9)where `a`.`id` =NAME_CONST('in_user_id',5889087) and a.money >=NAME_CONST('pay_money',9)| 1131607B|
501045 | update `a`set `a`.`money` = `a`.`money` -NAME_CONST('pay_money',9)where `a`.`id` =NAME_CONST('in_user_id',5889087) and a.money >=NAME_CONST('pay_money',9)
| 113171EA | 501711 | update `a`set `a`.`money` = `a`.`money` -NAME_CONST('pay_money',9)where `a`.`id` =NAME_CONST('in_user_id',5889087) and a.money >=NAME_CONST('pay_money',9)| 11315087|
500378 | update `a`set `a`.`money` = `a`.`money` -NAME_CONST('pay_money',9)where `a`.`id` =NAME_CONST('in_user_id',5889087) and a.money >=NAME_CONST('pay_money',9)
|
| 113171EA | 501711 | update `a`set `a`.`money` = `a`.`money` -NAME_CONST('pay_money',9)where `a`.`id` =NAME_CONST('in_user_id',5889087) and a.money >=NAME_CONST('pay_money',9)| 11314AFC|
501849 | commit
|
| 113171DA | 501941 | update `a`set `a`.`money` = `a`.`money` -NAME_CONST('pay_money',9)where `a`.`id` =NAME_CONST('in_user_id',6389794) and a.money >=NAME_CONST('pay_money',9)| 11316230|
501547 | commit
好多都是和commit锁等待.
这不是由于DB commit比较频繁造成的?批量提交可以解决这个问题吗?
|