1.登录mysql
mysql -uroot -proot123
2.启动事务
use employees;
start transaction;
select * from departments for update;
3.再开个会话，查询
use employees;
update departments set dept_name='Development1' where dept_no='d005';
4.增加innodb_lock_wait_timeout为1小时
show variables like 'innodb_lock_wait_timeout'\G
set global innodb_lock_wait_timeout=3600; #lock_wait_timeout,获取 metadata locks锁的超时时间
interactive_timeout
wait_timeout
update departments set dept_name='Development1' where dept_no='d005';
5.查进程
show processlist;
6.查会话信息
select * from sys.session;
7.查锁等待信息
select waiting_trx_id,waiting_pid,waiting_query,blocking_trx_id,blocking_pid,blocking_query
from sys.innodb_lock_waits;
8.杀线程
mysqladmin -uroot -p kill 25 #show processlist.id 或sys.innodb_lock_waits.blocking_pid
9.更新成功
update departments set dept_name='Development1' where dept_no='d005';
10.查看slap脚本(mysql自带性能压力测试工具)
cat /apps/sh/slap-test-updates.sh
mysqlslap --user=root --password=root123 -concurrency=5 \
--iterations=100 --number-char-cols=4 --number-int-cols=7 \
--auto-generate-sql --number-of-queries=10000 \
--auto-generate-sql-load-type=update
11.执行脚本
sh slap-test-updates.sh
12.显示processlist
show processlist;
13..查会话信息
select * from sys.session;
14.查锁等待信息
select waiting_trx_id,waiting_pid,waiting_query,blocking_trx_id,blocking_pid,blocking_query
from sys.innodb_lock_waits;
15.用ctr+c kill 掉kill /apps/sh/slap-test-updates.sh
