锁
1、查看表锁, Table_locks_waited较高表明锁争用比较严重
[sql] view plain copy
- show status like 'table%'
2、查看行锁,Innodb_row_lock_waits和InnoDB_row_lock_time_avg较高表明锁争用比较严重
[sql] view plain copy
- show status like 'innodb_row_lock%'
3、加写表锁(需手动unclock tables,用commit不会自动解锁)
[sql] view plain copy
- lock table user write;
4、加读表锁(需手动unclock tables,用commit不会自动解锁)
[sql] view plain copy
- lock table user read;
5、加读表锁(需手动unclock tables,用commit不会自动解锁),并支持另一个事务的insert操作
[sql] view plain copy
- lock table user read local;
6、加共享行锁(提交事务,自动解锁)
[sql] view plain copy
- select * from user where id=2 lock in share mode;
7、加排他行锁(提交事务,自动解锁)
[sql] view plain copy
- delete from user where id=2 for update;
8、其他说明
INSERT...SELECT...和 CREATE TABLE...SELECT...语句,会锁原表
[sql] view plain copy
- set global concurrent_insert=2; -- 设置是否开启并发insert,0=不开启,1=没有空洞的情况下,允许insert,2=不管有没有空洞,都允许插入
- select @@LOW_PRIORITY_UPDATES; -- 默认情况下,写锁比读锁优先级高,所以写锁会先得到锁,用这个命令可以降低写锁的级别
- select @@max_write_lock_count; -- 当写锁达到指定数量,会降低写锁优先级
事务
1、开启事务
[sql] view plain copy
- set autocommit=0; -- 先取消自动事务提交
- start transaction; -- 手动开启事务
2、提交事务(不会自动释放表锁)
[sql] view plain copy
- commit;
3、回滚事务
[sql] view plain copy
- rollback;
4、设置事务的隔离级别为“未提交读”(可能导致脏读)
[sql] view plain copy
- set session transaction isolation level read uncommitted;
5、设置事务的隔离级别为“已提交读读”(可能导致不可重复读)
[sql] view plain copy
- set session transaction isolation level read committed;
6、设置事务的隔离级别为“可重复读”(可能导致幻读)
[sql] view plain copy
- set session transaction isolation level repeatable read;
7、设置事务的隔离级别为“序列化”
[sql] view plain copy
- set session transaction isolation level serializable;
8、查看数据库的事务隔离级别
[sql] view plain copy
- select @@global.tx_isolation, @@session.tx_isolation;