【二】锁+事务

目录

Mysql锁

MyISAM锁

​编辑

InnoDB锁

物理结构修改

Mysql事务

事务的特性：

隔离性级别

事务语法

---

Mysql锁

锁的概念：

锁是计算机协调多个进程或线程并发访问某一资源的机制

在数据中，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素

锁对数据库而言显得尤其重要，也更加复杂

mysql的锁机制比较简单

其最显著的特点是不同的存储引擎支持不同的锁机制

比如：

MyISAM和Memory存储引擎采用的是表级锁（table-level locking）

InnoDB存储引擎支持行级锁（row-level locking），也支持表级锁，但默认情况下使用行级锁

Mysql中的锁

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

行级锁：开销大，加锁慢；会出现死锁，锁定流利度最小，发生所冲突的概率最低，并发度最高

页面锁：开销和加锁时间介于表级锁和航急速之间；会出现死锁；锁定粒度介于表锁和行锁之间；会出现死锁；锁粒度介于表锁和行锁之间，并发度一般。

仅从锁的角度来说：

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

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

MyISAM锁

读锁和读锁可以共存，读锁和写锁无法共存，写锁和写锁无法共存

MyISAM表共享读锁（table read lock）

给表加锁：

lock table 表名 read

lock table 表名 as 别名 read

解锁：unlock tables

在同一个session中，对同一张表进行修改，会报错，在不同的session会等待。

在同一个session中，对同一张表可以查询，但是使用别名报错，对其他表进行操作都会报错。

在另一个session中，对其他表操作会成功。

MyISAM表独占写锁（table write lock）

给表加锁：

lock table 表名 write

lock table 表名 as 别名 write

解锁：unlock tables

在同一个session中，可以对该表进行操作

在同一个session中，对其他表操作会报错

在另一个session中，查询该表会等待

MyISAM锁调度

InnoDB锁

InnoDB行锁：

共享锁又称：读锁。当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事物给这几行上排他锁，但允许上读锁

行读锁上锁：

select * from 表 where 条件 lock in share mode

排他锁又称：写锁。当一个事务对某几行上锁时，不允许其他事物写，但允许读。更不允许其他事物给这几行上任何锁，包括写锁。

行写锁上锁：

select * from 表 where 条件 for update

InnoDB表锁

和MyISAM差别不大

注意：开启一个事务时会释放表锁

物理结构修改

面试题：系统运行一段时间，数据量已经恩达，这时候系统升级，有张表A需要增加个字段，并发量白天晚上都很大，请问怎么修改表结构？

考点：修改表结构会导致表锁，数据量大修改数据时间很长，大量用户阻塞无法访问

物理结构修改工具 pt-online-schema-change

• 下载安装perl环境 http:www.perl..org/get.html
• 下载percona-toolkit.工具集合 https:/wwww.percona.oom/doc/percona-toolkit
• ppm install DBI依赖
• ppm install DBD:mysql安装mysql驱动依赖:

pt-online-schema-change h=127.0.0.1,u=root,D=mysqldemo,t=product_info --alter "modify product_name varchar(150)not null default "" --execute

行锁升级为表锁，上锁时，where非索引字段时，行锁会升级为表锁

Mysql事务

支持事务的存储引擎：InnoDB

查看数据是否支持事务

show engines

查看mysql当前默认的存储引擎

show variables '%strorage_engine%'

查看某张表的存储引擎

show create table 表名

对于表的存储结构的修改

alter table 表名 type = InnoDB

事务的特性：

事务具有4个属性：原子性、一致性、隔离性、持久性。这4个特性通常称为ACID特性

原子性（atomicity）：一个事务是一个不可分割的工作单位，事务中包括的诸多操作要么都成功，要么都不成功

一致性（consistency）：事务必须是使数据库从一个一致性状态变到另一个一致性状态，一致性与原子性是密切相关的

隔离性（isolation）：一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰

持久性（durability）：持久性也称永久性，指一个事务一旦提交，它对数据库中的数据改变就是永久性的，接下来的其他操作或故障不应该对其有任何影响

隔离性级别

mysql默认的事务隔离级别为可重复读

事务隔离级别查询：show variables like '%tx_isolation%'

未提交读：脏读 READ UNCOMMITTED

事务A读取了事务B更新的数据，然后B回滚操作，那么A读到的数据就是脏数据。

修改事务隔离级别为未提交读：

set SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

已提交读：不可重复读 READ COMMITED

事务A多次读取同一条数据，事务B在事务A多次读取的过程中，对数据做了更新并提交，导

致事务A多次读取统一数据时，结果不一致。

修改隔离级别：

set SESSION TRANSACTION ISOLATION LEVEL READ COMMITED;

可重复读：

事务A提交了事务修改了一条数，在另一个session中查到的这条数据是事务A提交前的数据。

如果有主键时，以索引列为条件更新数据，会存在间隙锁、行锁、页锁的问题，从而锁住一些行；如果没有索引（索引失效），更新数据时会锁住这整张表。

幻读：

系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B

这时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过

来，就好像发生了幻觉一样，就叫做幻读

可串行化（序列化）：可以解决幻读问题

读写数据都会锁表

修改隔离级别：

set SESSION TRANSACTION ISOLATION LEVEL READ serializable

不可重复读和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需要锁住满足条件的行，解决幻读需要锁表。

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也大，对于多数应用程序，可以考虑优先把数据隔离级别设为读已提交，它能够避免脏读，也拥有较好的并发性。

事务语法

开启事务

begin

start transaction

begin work

事务回滚 rollback

事务提交 commit

还原点 savepoint

rollback savepoint s2