/*====MySQL===========================================================*/ select * from information_schema.PROCESSLIST p ; select * from information_schema.INNODB_TRX it ; --未提交的事务 select * from performance_schema.threads t where t.`TYPE` != 'BACKGROUND'; select * from performance_schema.data_locks; --数据库锁的持有情况 select * from performance_schema.data_lock_waits; --锁等待情况(5.7对应的有可能是information_schema.innodb_lock_waits) select * from performance_schema.events_statements_current; select * from performance_schema.events_transactions_current etc ; select version(), user(), connection_id(); -- 所有会话及SQL执行情况 select t.PROCESSLIST_ID, t.THREAD_ID, t.PROCESSLIST_STATE, t.PROCESSLIST_COMMAND , block.BLOCKING_THREAD_ID, block.BLOCKING_EVENT_ID , t.PROCESSLIST_TIME, sc.NESTING_EVENT_TYPE, sc.SQL_TEXT , t.*, sc.* from performance_schema.threads t join performance_schema.events_statements_current sc on t.THREAD_ID = sc.THREAD_ID left join performance_schema.data_lock_waits block on t.THREAD_ID = block.REQUESTING_THREAD_ID where t.`TYPE` != 'BACKGROUND'; -- SQL阻塞情况 select dlw.REQUESTING_THREAD_ID, ts.PROCESSLIST_INFO as requesting_processlist_info , dlw.BLOCKING_THREAD_ID, tt.PROCESSLIST_INFO as blocking_processlist_info , dlw.* from performance_schema.data_lock_waits dlw join performance_schema.threads ts on dlw.REQUESTING_THREAD_ID = ts.THREAD_ID join performance_schema.threads tt on dlw.BLOCKING_THREAD_ID = tt.THREAD_ID ; -- 根据阻塞线程id,查找历史执行的所有SQL select * from performance_schema.events_statements_history sh where sh.THREAD_ID = @blk_thread_id; -- 直接查找root block thread对应的SQL with src as ( select * from performance_schema.data_lock_waits dlw where not exists ( select * from performance_schema.data_lock_waits dlw2 where dlw.BLOCKING_THREAD_ID = dlw2.REQUESTING_THREAD_ID ) ) select * from performance_schema.events_statements_history esh where esh.THREAD_ID in (select src.BLOCKING_THREAD_ID from src) order by esh.THREAD_ID, esh.TIMER_START ; -- 所有未提交事务及对应会话的当前SQL select t.PROCESSLIST_ID, t.THREAD_ID, t.PROCESSLIST_STATE, it.trx_id, sc.SQL_TEXT, it.*, t.*, sc.* from information_schema.INNODB_TRX it join performance_schema.threads t on it.trx_mysql_thread_id = t.PROCESSLIST_ID join performance_schema.events_statements_current sc on t.THREAD_ID = sc.THREAD_ID ; // 使用悲观锁 模拟事务 set autocommit=0; //设置完autocommit后,我们就可以执行我们的正常业务了。具体如下: //0.开始事务 begin;/begin work;/start transaction; (三者选一就可以) //1.查询出商品信息 select status from t_goods where id=1 for update; //2.根据商品信息生成订单 insert into t_orders (id,goods_id) values (null,1); //3.修改商品status为2 update t_goods set status=2; //4.提交事务 commit;/commit work;