mysql小记--基础知识

一、事务

事务是由一组SQL语句组成的逻辑处理单元。

事务的特征ACID，即原子性、一致性、隔离性和持久性。

原子性(Atomicity)事务作为整体执行，操作要么全部执行、要么全部不执行。

一致性(Consistent)事务应该确保数据库状态从一个一致状态转变为另一个一致状态。

隔离性(Isolation)多个事务并发执行时，一个事务执行不影响其他事务执行（隔离级别可设置）。

持久性(Durable)事务提交后，对数据库的修改应该永久保存在数据库中。

 

二、事务的隔离级别

1、读未提交(Read Uncommited,RU)

这种隔离级别下，事务间完全不隔离，会产生脏读，可以读取未提交的记录，实际情况下不会使用。

读取未提交的数据，又称为“脏读”。

 

2、读提交(Read commited,RC)

仅能读取到已提交的记录，这种隔离级别下，会存在幻读现象，所谓幻读是指在同一个事务中，多次执行同一个查询，返回的记录不完全相同的现象。幻读产生的根本原因是，在RC隔离级别下，每条语句都会读取已提交事务的更新，若两次查询之间有其他事务提交，则会导致两次查询结果不一致。虽然如此，读提交隔离级别在生产环境中使用很广泛。

 

3、可重复读(Repeatable Read,RR)

可重复读解决了幻读问题。不是所有的数据库都实现了该隔离级别。

 

4、串行化(Serializable)

在串行化隔离模式下，消除了脏读、幻读，但事务并发度急剧下降，事务的隔离级别与事务的并发度成反比，隔离级别越高，事务的并发度越低。实际生产环境下，dba会在并发和满足业务需求之间做权衡，选择合适的隔离级别。

 

 

三、关于锁

1、MySQL常用存储引擎的锁机制：

 

   MyISAM和MEMORY采用表级锁(table-level locking)

   BDB采用页面锁(page-level locking)或表级锁，默认为页面锁

   InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

 

2、各种锁特点：

   表级锁：开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低

   行级锁：开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高

   页面锁：开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般

 

3、各种锁的适用场景:

  表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用

  行级锁则更适合于有大量按索引条件并发更新数据,同时又有并发查询的应用,如一些在线事务处理系统

 

4、死锁：

  是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。

  表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB.

 

5.1 InnoDB锁类型

    共享锁（S）：允许事务读一行数据

    排他锁（X）：允许事务删除或更新一行数据

5.2 InnoDB意向锁（表级别）

    意向共享锁（IS）：事务想要获得一个表中某几行的共享锁

    意向排他锁（IX）：事务想要获得一个表中某几行的排他锁

5.3、对读操作进行枷锁

select .... lock in share mode：加S锁

select ... for update：加X锁

5.4、InnoDB锁算法

Record Lock：单个行记录上的锁

Gap Lock：间隙锁，锁定一个范围，但不包括记录本身

Next-Key Lock：锁定一个范围的记录，并且包括记录本身（在Repeatable Read事务级别下，使用该算法，可避免幻读的产生）

 

6、InnoDB锁性能监控

 

mysql> show status like '%innodb_row_lock_%';

+-------------------------------+---------+

| Variable_name                 | Value   |

+-------------------------------+---------+

| Innodb_row_lock_current_waits | 0       |

| Innodb_row_lock_time          | 4517738 |

| Innodb_row_lock_time_avg      | 2673    |

| Innodb_row_lock_time_max      | 51386   |

| Innodb_row_lock_waits         | 1690    |

+-------------------------------+---------+

 

Innodb_row_lock_current_waits ：当前等待锁的数量

Innodb_row_lock_time          ：系统启动到现在锁定的总时间长度

Innodb_row_lock_time_avg      ：每次平均锁定的时间

Innodb_row_lock_time_max      ：最长一次锁定时间

Innodb_row_lock_waits         ：系统启动到现在总共锁定次数

 

7、案例1

创建表
CREATE TABLE `class_teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_name` varchar(100) DEFAULT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_teacher_id` (`teacher_id`)
) ENGINE=InnoDB;

插入数据
insert into class_teacher(class_name, teacher_id) values('class301', 5),('class201', 30);

根据二级索引进行更新(update或delete)，之后进行插入[数据库隔离级别：REPEATABLE-READ]
1）、
事务0： update class_teacher set class_name='class202' where teacher_id=31;

事务1：insert into class_teacher(class_name,teacher_id) values('class229', 29);
[插入OK]
事务2：insert into class_teacher(class_name,teacher_id) values('class230', 30);
[插入WAIT]
事务3：insert into class_teacher(class_name,teacher_id) values('class231', 31);
[插入WAIT]
事务4：insert into class_teacher(class_name,teacher_id) values('class232', 32);
[插入WAIT]
事务5：insert into class_teacher(class_name,teacher_id) values('class250', 50);
[插入WAIT]

结论：
锁定[30,无穷大)

2）、
事务0： update class_teacher set class_name='class202' where teacher_id=3;

事务1：insert into class_teacher(class_name,teacher_id) values('class210', 1);
[插入WAIT]
事务2：insert into class_teacher(class_name,teacher_id) values('class220', 2);
[插入WAIT]
事务3：insert into class_teacher(class_name,teacher_id) values('class230', 3);
[插入WAIT]
事务4：insert into class_teacher(class_name,teacher_id) values('class250', 5);
[插入OK]
事务5：insert into class_teacher(class_name,teacher_id) values('class260', 6);
[插入OK]

结论：
锁定(无穷小,5)

3）、
事务0： update class_teacher set class_name='class202' where teacher_id=5;

事务1：insert into class_teacher(class_name,teacher_id) values('class240', 4);
[插入WAIT]
事务2：insert into class_teacher(class_name,teacher_id) values('class250', 5);
[插入WAIT]
事务3：insert into class_teacher(class_name,teacher_id) values('class260', 6);
[插入WAIT]
事务4：insert into class_teacher(class_name,teacher_id) values('class215', 15);
[插入WAIT]
事务5：insert into class_teacher(class_name,teacher_id) values('class229', 29);
[插入WAIT]
事务6：insert into class_teacher(class_name,teacher_id) values('class230', 30);
[插入OK]
事务7：insert into class_teacher(class_name,teacher_id) values('class231', 31);
[插入OK]

结论：
锁定(无穷小,5]和[5,30)

4)、
事务0：update class_teacher set class_name='class202' where teacher_id=30;

事务1：insert into class_teacher(class_name,teacher_id) values('class240', 4);
[插入OK]
事务2：insert into class_teacher(class_name,teacher_id) values('class250', 5);
[插入WAIT]
事务3：insert into class_teacher(class_name,teacher_id) values('class260', 6);
[插入WAIT]
事务4：insert into class_teacher(class_name,teacher_id) values('class215', 15);
[插入WAIT]
事务5：insert into class_teacher(class_name,teacher_id) values('class229', 29);
[插入WAIT]
事务6：insert into class_teacher(class_name,teacher_id) values('class230', 30);
[插入WAIT]
事务7：insert into class_teacher(class_name,teacher_id) values('class231', 31);
[插入WAIT]
事务8：insert into class_teacher(class_name,teacher_id) values('class250', 50);
[插入WAIT]

结论：
锁定(5,30]和[30,无穷大)

5）、
事务0： update class_teacher set class_name='class202' where teacher_id=29;

事务1：insert into class_teacher(class_name,teacher_id) values('class240', 4);
[插入OK]
事务2：insert into class_teacher(class_name,teacher_id) values('class250', 5);
[插入WAIT]
事务3：insert into class_teacher(class_name,teacher_id) values('class260', 6);
[插入WAIT]
事务4：insert into class_teacher(class_name,teacher_id) values('class215', 15);
[插入WAIT]
事务5：insert into class_teacher(class_name,teacher_id) values('class229', 29);
[插入WAIT]
事务6：insert into class_teacher(class_name,teacher_id) values('class230', 30);
[插入OK]
事务7：insert into class_teacher(class_name,teacher_id) values('class231', 31);
[插入OK]

结论：
锁定[5,30)

　总结：当是根据二级索引进行更新，如update或delete时，在当前隔离级别下，其都会使用Next-Key锁[a,b)[行锁 + gap锁]前闭后开的，无法进行插入。

　备注：
　Repeatable Read，当使用非索引字段进行更新时，则会进行表锁。
　Read Uncommitted，数据库一般不用，且在其上的任何操作都不会加锁。
　Read Committed，有Record锁，没有Next-Key锁，即Next-Key锁变成了Record锁。