mysql 解除1000行显示限制

MySQL锁简介

MySQL的锁主要分为乐观锁和悲观锁，乐观锁一般是程序自己实现，可以根据版本号或者时间戳字段实现

MySQL表级锁

表级锁由MySQL Layer层实现

MySQL 实现的表级锁定的争用状态变量

show status like 'table%';

• table_locks_immediate:产生表级锁定的次数;
• table_locks_waited:出现表级锁定争用而发生等待的次数;

表锁

表锁主要有两种表现形式：

• 表共享读锁(Table Read Lock)
• 表独占写锁(Table Write Lock)

手动增加表锁

lock table 表名称 read(write),表名称2 read(write)，其他;

查看表锁情况

show open tables;

删除表锁

unlock tables;

表锁演示

--新建表
CREATE TABLE mylock (
id int(11) NOT NULL AUTO_INCREMENT,
NAME varchar(20) DEFAULT NULL,
PRIMARY KEY (id)
);
INSERT INTO mylock (id,NAME) VALUES (1, 'a'); INSERT INTO mylock (id,NAME) VALUES (2, 'b'); INSERT INTO mylock (id,NAME) VALUES (3, 'c'); INSERT INTO mylock (id,NAME) VALUES (4, 'd');

表读锁演示

开启2个命令行接口，按顺序运行如下SQL

1、session1: lock table mylock read; -- 给mylock表加读锁
2、session1: select * from mylock; -- 可以查询
3、session1:select * from t; --不能访问非锁定表
4、session2:select * from mylock; -- 可以查询 没有锁
5、session2:update mylock set name='x' where id=2; -- 修改阻塞,自动加行写锁 
6、session1:unlock tables; -- 释放表锁
7、session2:Rows matched: 1 Changed: 1 Warnings: 0 -- 修改执行完成 
8、session1:select * from t; --可以访问

在一个session中也就是一个连接上，对一个表加了读锁，只能读取此表的数据，不能访问其他锁的数据，直到释放锁

表写锁演示

开启2个命令行接口，按顺序运行如下SQL

1、session1: lock table mylock write; -- 给mylock表加写锁 
2、session1: select * from mylock; -- 可以查询 
3、session1:select * from tdep; --不能访问非锁定表 
4、session1:update mylock set name='y' where id=2; --可以执行 
5、session2:select * from mylock; -- 查询阻塞 
6、session1:unlock tables; -- 释放表锁
7、session2:4 rows in set (22.57 sec) -- 查询执行完成 
8、session1:select * from tdep; --可以访问

元数据锁

在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁;当要对表做结构变更操作的时候，加 MDL 写锁

元数据锁演示

1、session1: begin;--开启事务
select * from mylock;--加MDL读锁
2、session2: alter table mylock add f int; -- 修改阻塞 
3、session1:commit; --提交事务 或者 rollback 释放读锁 
4、session2:Query OK, 0 rows affected (38.67 sec) --修改完成
            Records: 0  Duplicates: 0  Warnings: 0

意向锁(InnoDB)

InnoDB实现了表级锁，也就是意向锁，意向锁是mysql内部使用的，不需要用户干预

• 意向共享锁(IS)
  事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
• 意向排他锁(IX)
  事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

意向锁的主要作用是为了【全表更新数据】时的性能提升。否则在全表更新数据时，需要先检索该表是否某些记录上面有行锁

行级锁

行级锁是由InnoDB存储引擎实现的
InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着:只有通过索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁!

行级锁分类

InnoDB的行级锁，按照锁定范围来说，分为三种:

• 记录锁(Record Locks):锁定索引中一条记录。 主键指定 where id=3
• 间隙锁(Gap Locks): 锁定记录前、记录中、记录后的行
• Next-Key 锁: 记录锁 + 间隙锁

按照功能来说，分为两种: 共享读锁(S)和排他写锁(X)

• 共享读锁(S)
  允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE -- 共享读锁 手动添加 
select * from table_name -- 无锁

• 排他写锁(X)
  允许获得排他写锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁**(不是读)**和排他写锁。

1. 自动加 DML 对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁(X);
2. 手动加

SELECT * FROM table_name WHERE ... FOR UPDATE

行级锁演示

查看行锁状态

show STATUS like 'innodb_row_lock%';

• Innodb_row_lock_current_waits:当前正在等待锁定的数量;
• Innodb_row_lock_time:从系统启动到现在锁定总时间长度;
• Innodb_row_lock_time_avg:每次等待所花平均时间;
• Innodb_row_lock_time_max:从系统启动到现在等待最常的一次所花的时间;
• Innodb_row_lock_waits:系统启动后到现在总共等待的次数;

行读锁演示

1、session1: begin;--开启事务未提交
select * from mylock where id=1 lock in share mode; --手动加id=1的行读锁,使用索引
2、session2:update mylock set name='y' where id=2; -- 未锁定该行可以修改 
3、session2:update mylock set name='y' where id=1; -- 锁定该行修改阻塞
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 
-- 锁定超时
4、session1: commit; --提交事务 或者 rollback 释放读锁 
5、session2:update mylock set name='y' where id=1; --修改成功
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

使用索引加行锁 ，未锁定的行可以访问

行读锁升级为表锁

未使用索引行锁升级为表锁

1、session1: begin;--开启事务未提交 --手动加name='c'的行读锁,未使用索引
select * from mylock where name='c' lock in share mode; 
2、session2:update mylock set name='y' where id=2; -- 修改阻塞 未用索引行锁升级为表锁
3、session1: commit; --提交事务 或者 rollback 释放读锁
4、session2:update mylock set name='y' where id=2; --修改成功
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

行写锁

主键索引产生记录锁

1、session1: begin;--开启事务未提交 --手动加id=1的行写锁,
             select * from  mylock  where id=1 for update;
2、session2:select * from mylock where id=2 ; -- 可以访问 
3、session2: select * from mylock where id=1 ; -- 可以读 不加锁
4、session2: select * from mylock where id=1 lock in share mode ; -- 加读锁 被阻塞
5、session1:commit; -- 提交事务 或者 rollback 释放写锁 
6、session2:执行成功

间隙锁

非主键索引产生间隙锁，主键范围产生间隙锁
主键索引产生的是记录锁

create table news (id int, number int,primary key (id));
insert into news values(1,2);
insert into news values(3,4);
insert into news values(6.5);
insert into news values(8,5);
insert into news values(10,5);
insert into news values(13,11);
--加非唯一索引
alter table news add index idx_num(number);

session 1:
start transaction ;
select * from news where number=4 for update ;
session 2:
start transaction ;
insert into news value(2,4);#(阻塞) 
insert into news value(2,2);#(阻塞) 
insert into news value(4,4);#(阻塞) 
insert into news value(4,5);#(阻塞) 
insert into news value(7,5);#(执行成功) 
insert into news value(9,5);#(执行成功) 
insert into news value(11,5);#(执行成功) ````
-- 注:id和number都在间隙内则阻塞。


session 1:
start transaction ;
select * from news where number=13 for update ; 
update news set xx where number=13 ;
(select * from news where id>1 and id < 4 for update) session 2:
start transaction ;
insert into news value(11,5);#(执行成功)
insert into news value(12,11);#(执行成功)
insert into news value(14,11);#(阻塞)
insert into news value(15,12);#(阻塞)

检索条件number=13,向左取得最靠近的值11作为左区间，向右由于没有记录因此取得无穷大作为右区间，因此，session 1的间隙锁的范围(11，无穷大)

死锁

两个 session 互相等等待对方的资源释放之后，才能释放自己的资源,造成了死锁

1、session1: begin;--开启事务未提交 --手动加行写锁 id=1 ，使用索引
update mylock set name='m' where id=1; 
2、session2:begin;--开启事务未提交
--手动加行写锁 id=2 ，使用索引
update mylock set name='m' where id=2;
3、session1: update mylock set name='nn' where id=2; -- 加写锁被阻塞 
4、session2:update mylock set name='nn' where id=1; -- 加写锁会死锁，不允许操作
     ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting
transaction

两阶段锁(2PL)

锁操作分为两个阶段

• 加锁阶段
  只加锁，不放锁
• 解锁阶段
  只放锁，不加锁
  加锁阶段与解锁阶段不相交