mysql写锁的原理？ mysql锁表严重？

一、mysql写锁的原理？

一、锁的概念

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

二、MySQL中的锁

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

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

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

三、MyISAM的写锁

1.lock table testmysam WRITE

在同一个session中

insert testmysam value(3);

delete from testmysam where id = 3

select * from testmysam

2.对不同的表操作（报错）

select s.* from testmysam s

insert into testdemo value(2,'2','3');

3.在其他session中 （等待）

select * from testmysam

二、mysql锁表严重？

导致锁表的原因

1、锁表发生在insert  update 、delete 中；  

2、锁表的原理是 数据库使用独占式封锁机制，当执行上面的语句时，对表进行锁住，直到发生commite 或者 回滚 或者退出数据库用户； 

3、A程序执行了对 tableA 的 insert ，并还未 commite时，B程序也对tableA 进行insert 则此时会发生资源正忙的异常 就是锁表；

 4、锁表常发生于并发而不是并行（并行时，一个线程操作数据库时，另一个线程是不能操作数据库的，cpu 和i/o 分配原则）。

三、mysql查询分类汇总的怎么写？

mysql 分类汇总select code,sum(num) total from 表名 group by code order by c

四、mysql事务与锁的关系？

事务和锁

事务的定义

简而言之： 事务（Transaction）是并发控制的基本单位。

所谓的事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

事务的特点 ACID

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

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

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

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

事务对应的语句

BEGIN TRANSACTION 开始事务

COMMIT TRANSACTION 提交事务

ROLLBACK TRANSACTION 回滚事务

事务并发控制

事务不考虑隔离性引发的问题

脏读：此种异常时因为一个事务读取了另一个事务修改了但是未提交的数据，当修改的事务进行回滚操作时将造成读取事务异常。

不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。（一个事务读取到了另外一个事务提交的数据）

幻读（虚读）：指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。例如读整个表，即表的行数，例如第一次读某个表有3条记录，第二次读该表又有4条记录 （和不可重复读的不同：不可重复读针对的是数据的值，幻读针对的是数据的数量）

数据库事务隔离级别（SQL标准定义）

READ UNCOMMITTED（未提交读）：事务中的修改，即使没有提交，其他事务也可以看得到。很容易导致脏读等众多问题，如无必要，很少使用

READ COMMITTED（提交读）：大多数数据库系统默认的隔离级别（除Mysql等）。这种隔离级别就是一个事务的开始，只能看到已经完成的事务的结果，正在执行的，是无法被其他事务看到的。这种级别会出现读取旧数据的现象，即不可重复读的问题。

REPEATABLE READ（可重复读）：解决了脏读的问题，该级别保证了每行的记录的结果是一致的，也就是上面说的读了旧数据的问题，但是却无法解决另一个问题，幻行，顾名思义就是突然蹦出来的行数据。指的就是某个事务在读取某个范围的数据，但是另一个事务又向这个范围的数据去插入数据，导致多次读取的时候，数据的行数不一致。即幻读。–MYSQL默认隔离级别

SERIALIZABLE（可串行化）：最高的隔离级别，它通过强制事务串行执行（注意是串行），避免了前面的幻读情况，由于他大量加上锁，导致大量的请求超时，因此性能会比较底下，在特别需要数据一致性且并发量不需要那么大的时候才可能考虑这个隔离级别

数据库锁

数据库锁的基本类型：

X锁：exclusive 用于写操作

- 某数据对象在没有加任何锁的情况下，一个事务可以对其加X锁，而其他事务就不能对其再加任何锁

S锁：share 用于读操作

- 一个事务对某数据对象加了S 锁后，其他事务就不能对其加X锁，但可以加S锁

U锁：update

- 事务要更新数据对象时，先申请该对象的U 锁。对象加了U锁，允许其他事务对它加S锁。在最后写入时，再申请将U锁升级为X锁。不必在全过程中加X

不同级别的加锁协议

一级封锁协议（脏数据、不可重复读）

任一事务在写某数据前，必须对其加上X锁，该事务结束后才释放。不采用S锁，读数据不用加锁。

事务结束包括正常结束（COMMIT）和非正常结束（ROLLBACK）。

二级封锁协议（不可重复读）

满足一级封锁协议，且任一事务在读取某数据前，必须对其加上S锁，读完后 就释放

三级封锁协议（）

满足一级封锁协议，且任一事务在读取某数据前，必须对其加上S锁，事务结束后 释放锁

其他加锁协议

两阶段加锁协议：

整个事务分为两个阶段，前一个阶段为加锁，后一个阶段为解锁。在加锁阶段，事务只能加锁，也可以操作数据，但不能解锁，直到事务释放第一个锁，就进入解锁阶段，此过程中事务只能解锁，也可以操作数据，不能再加锁。两阶段锁协议使得事务具有较高的并发度，因为解锁不必发生在事务结尾。它的不足是没有解决死锁的问题，因为它在加锁阶段没有顺序要求。如两个事务分别申请了A, B锁，接着又申请对方的锁，此时进入死锁状态。

定理：若所有事务均遵守两段锁协议，则这些事务的所有交叉调度都是可串行化的。

多粒度加锁协议

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。只在存储引擎层实现

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

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

五、关于MySQL中的表锁和行锁？

1. 程序中非数据库交互操作导致事务挂起

将接口调用或者文件操作等这一类非数据库交互操作嵌入在 SQL 事务代码之中，那么整个事务很有可能因此挂起（接口不通等待超时或是上传下载大附件）。

2. 事务中包含性能较差的查询 SQL

事务中存在慢查询，导致同一个事务中的其他 DML 无法及时释放占用的行锁，引起行锁等待。

3. 单个事务中包含大量 SQL

通常是由于在事务代码中加入 for 循环导致，虽然单个 SQL 运行很快，但是 SQL 数量一大，事务就会很慢。

4. 级联更新 SQL 执行时间较久

这类 SQL 容易让人产生错觉，例如：update A set ... where ...in (select B) 这类级联更新，不仅会占用 A 表上的行锁，也会占用 B 表上的行锁，当 SQL 执行较久时，很容易引起 B 表上的行锁等待。

5. 磁盘问题导致的事务挂起

极少出现的情形，比如存储突然离线，SQL 执行会卡在内核调用磁盘的步骤上，一直等待，事务无法提交。

综上可以看出，如果事务长时间未提交，且事务中包含了 DML 操作，那么就有可能产生行锁等待，引起报错。

六、mysql读写锁表区别？

MySQL读写锁表的区别：

读写锁。

读锁：是一种共享锁，一个事务持有读锁时，不会阻塞其它的读锁，其他事务都可以对该数据进行读取；

写锁：是一种排他锁，一个锁持有写锁会阻塞其他的写锁和读锁，从而保证了一个只有一个事务进行写操作，并且防止其他事务读取正在写入资源，避免了脏读；

七、mysql插入锁等待超时？

等待加锁线程完毕后再执行。 以MyISAM表的表级写锁为例，MySql5.0说得很清楚的： 当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

八、mysql锁表产生原因？

在mysql中，锁表的原因是一个程序执行了对表的insert、update或者delete操作还未commite时，另一个程序也对同一个表进行相同的操作，则此时会发生资源正忙的异常，也就是锁表

九、mysql间隙锁实现原理？

我们都知道Mysql,Oracle PostgreSQL 可以利用MVCC来处理事务，防止加锁，来提高访问效率

MVCC只是工作在两种事务级别底下：(a) Read Committed (b) Repeatable Read;

因为其他两种：

(c)READ UNCOMMITTED==》总是读取最新的数据，不符合当前事务版本的数据行，

(d)Serializable则会对所有的行加锁。

这两种都不需要MVCC；

参考：Mysql 的InnoDB事务方面的 多版本并发控制如何实现 MVCC

这样说来 Mysql 也跟其他的数据库一样，当 Repeatable Read的时候会出现幻读的情况，其实不然，Mysql还有一种机制可以保证即使在Repeatable Read级别下面也不会出现幻读；

这就是间隙锁：

间隙锁跟MVCC一起工作。实现事务处理：

Repeatable Read隔离级别： 采用Next-key Lock(间隙锁) 来解决幻读问题.因此 Mysql 在Repeatable下面 幻读，可重复读，脏读 三者都不会发生

read committed隔离级别：采用Record锁,不会出现脏读，但是会产生"幻读"问题. 也会出现可重复读

(我查了很久，这个read committed模式下也会出现可重复读的问题参考：MySQL中Innodb的事务隔离级别和锁的关系的讲解教程)

间隙锁简介：

MySQL InnoDB支持三种行锁定方式：InnoDB的默认加锁方式是next-key 锁。

l 行锁（Record Lock）:锁直接加在索引记录上面，锁住的是key。

l 间隙锁（Gap Lock）:锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。

l Next-Key Lock ：行锁和间隙锁组合起来就叫Next-Key Lock。

默认情况下，InnoDB工作在可重复读(Repeatable Read)隔离级别下，并且会以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。Next-Key Lock是行锁和间隙锁的组合，当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。加上间隙锁之后，其他事务就不能在这个间隙修改或者插入记录。 read committed隔离级别下

Gap Lock在InnoDB的唯一作用就是防止其他事务的插入操作，以此防止幻读的发生。

Innodb自动使用间隙锁的条件：（1）必须在Repeatable Read级别下（2）检索条件必须有索引（没有索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加）

行锁（Record Lock）记录锁其实很好理解，对表中的记录加锁，叫做记录锁，简称行锁。

生活中的间隙锁（Gap Lock）编程的思想源于生活，生活中的例子能帮助我们更好的理解一些编程中的思想。生活中排队的场景，小明，小红，小花三个人依次站成一排，此时，如何让新来的小刚不能站在小红旁边，这时候只要将小红和她前面的小明之间的空隙封锁，将小红和她后面的小花之间的空隙封锁，那么小刚就不能站到小红的旁边。这里的小红，小明，小花，小刚就是数据库的一条条记录。他们之间的空隙也就是间隙，而封锁他们之间距离的锁，叫做间隙锁。

Mysql中的间隙锁下表中（见图一），id为主键，number字段上有非唯一索引的二级索引，有什么方式可以让该表不能再插入number=5的记录？

图一

根据上面生活中的例子，我们自然而然可以想到，只要控制几个点，number=5之前不能插入记录，number=5现有的记录之间不能再插入新的记录，number=5之后不能插入新的记录，那么新的number=5的记录将不能被插入进来。

那么，mysql是如何控制number=5之前，之中，之后不能有新的记录插入呢（防止幻读）？答案是用间隙锁，在RR级别下，mysql通过间隙锁可以实现锁定number=5之前的间隙，number=5记录之间的间隙，number=5之后的间隙，从而使的新的记录无法被插入进来。

间隙是怎么划分的？

注：为了方面理解，我们规定（id=A,number=B）代表一条字段id=A,字段number=B的记录，（C，D）代表一个区间，代表C-D这个区间范围。

图一中，根据number列，我们可以分为几个区间：（无穷小，2），（2，4），（4，5），（5，5），（5,11），（11，无穷大）。只要这些区间对应的两个临界记录中间可以插入记录，就认为区间对应的记录之间有间隙。例如：区间（2，4）分别对应的临界记录是（id=1,number=2），（id=3，number=4），这两条记录中间可以插入（id=2,number=3）等记录，那么就认为（id=1,number=2）与（id=3，number=4）之间存在间隙。

很多人会问，那记录（id=6，number=5）与（id=8，number=5）之间有间隙吗？答案是有的，（id=6，number=5）与（id=8，number=5）之间可以插入记录（id=7，number=5），因此（id=6,number=5）与（id=8,number=5）之间有间隙的，

间隙锁锁定的区域根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）。图一中，where number=5的话，那么间隙锁的区间范围为（4,11）；

间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：（1）防止间隙内有新数据被插入（2）防止已存在的数据，更新成间隙内的数据（例如防止numer=3的记录通过update变成number=5）

间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁。 默认情况下，InnoDB工作在Repeatable Read隔离级别下，并且以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。Next-Key Lock是行锁与间隙锁的组合，当对数据进行条件，范围检索时，对其范围内也许并存在的值进行加锁！当查询的索引含有唯一属性（唯一索引，主键索引）时，Innodb存储引擎会对next-key lock进行优化，将其降为record lock,即仅锁住索引本身，而不是范围！若是普通辅助索引，则会使用传统的next-key lock进行范围锁定！

要禁止间隙锁的话，可以把隔离级别降为Read Committed，或者开启参数innodb_locks_unsafe_for_binlog。

对于快照读来说，幻读的解决是依赖mvcc解决。而对于当前读则依赖于gap-lock解决。

深层次的原理分析：

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。

快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。

当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？以MySQL InnoDB为例：

快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)select * from table where ?;

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。select * from table where ? lock in share mode;select * from table where ? for update;insert into table values (…);update table set ? where ?;delete from table where ?;所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

MySQL/InnoDB定义的4种隔离级别：

Read Uncommited可以读取未提交记录。此隔离级别，不会使用，忽略。

Read Committed (RC)快照读忽略，本文不考虑。针对当前读，RC隔离级别保证对读取到的记录加锁 (record lock)，存在幻读现象。

Repeatable Read (RR)快照读忽略，本文不考虑。针对当前读，RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。

Serializable从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读，所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用。

十、mysql如何避免表锁？

1、基于要操作的表创建一个临时表，执行要修改的操作，比如add column或者drop column。

2、把表内容导出到文件(注意不要用intsert into table_copy select * from table，因为这样也很慢，也会锁表)

3、把文件导入到临时表

同上(最后括号里面的是字段名，可以不加，不加的前提是两张表结构一样)。

4、对换临时表和正式表的表名。