存储引擎和数据库锁

一.存储引擎

1.MySQL体系结构

• 数据库存储引擎是数据库最核心的部分，不同的存储引擎提供不同的存储机制、索引技巧等。也就是说不同的存储引擎底层对数据的组织方式不一样，从而导致性能和特点也不一样。

2.MySQL 当中常用的存储引擎：

1. InnoDB : 支持事务, MySQL5.5版本之后, 默认的存储引擎
2. MyISAM : 不支持事务
3. Memory : 速度快,最不安全。小量快速访问的数据

3.存储引擎的操作

-- 1. 创建数据库 db10 如果不存在则创建
CREATE DATABASE IF NOT EXISTS db10;

USE db10;

-- 2. 创建数据表的时候,指定存储引擎 MYISAM
CREATE  TABLE  engine_test (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10)
)ENGINE = MYISAM ;

-- 3. 查询存储引擎
SHOW  TABLE  STATUS  FROM  db10;

-- 4. 修改数据表当中的存储引擎
ALTER  TABLE  engine_test  ENGINE = InnoDB;

-- 5. 查询存储引擎
SHOW  TABLE  STATUS  FROM  db10;

4.存储引擎的选择

MyISAM

特点：不支持事务和外键操作。读取速度快，节约资源。

使用场景：以查询操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高！

InnoDB

特点：MySQL 的默认存储引擎，支持事务和外键操作。

使用场景：对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁的操作！

MEMORY

特点：将所有数据保存在内存中，在需要快速定位记录和其他类似数据环境下，可以提供更快的访问。

使用场景：通常用于更新不太频繁的小表，用来快速得到访问的结果！

总结：针对不同的需求场景，来选择最适合的存储引擎即可！如果不确定、则使用数据库默认的存储引擎！

二.数据库锁

1.锁的介绍和作用

• 概念:数据库为了保证数据的一致性，在共享的资源被并发访问时变得安全所设计的一种规则。
• 作用:锁机制类似多线程中的同步，作用就是可以保证数据的一致性和安全性

2.锁的分类

按操作分类

共享锁：也叫读锁。针对同一份数据，多个事务读取操作可以同时加锁而不互相影响 ，但是不能修改数据。

排他锁：也叫写锁。当前的操作没有完成前，会阻断其他操作的读取和写入。

按粒度分类

表级锁：会锁定整个表。开销小，加锁快。锁定力度大，发生锁冲突概率高，并发度低。不会出现死锁情况。

行级锁：会锁定当前行。开销大，加锁慢。锁定粒度小，发生锁冲突概率低，并发度高。会出现死锁情况。

按使用方式分类

悲观锁：每次查询数据时都认为别人会修改，很悲观，所以查询时加锁[InnoDB 中的共享锁、排他锁、表级锁、行级锁都是悲观锁]。

乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有去更新这个数据。

不同存储引擎支持的锁

2.锁的语法

【注：所有锁的操作需要要在两个新连接中操作【如两台电脑，获取一台电脑连接数据库两次】，不是一个连接中的两个查询窗口】
<1>InnoDB 共享锁
共享锁说明: 数据可以被多个事务查询（DQL）, 但是不能修改（DML）
也称读锁

-- 使用数据库
USE db8;

-- 开启事务
START TRANSACTION;

-- 查询id为1数据,(普通查询没问题)
SELECT * FROM student WHERE id=1;

-- 查询id为1数据,也加入共享锁(共享锁和共享锁是兼容)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 修改id为1数据，姓名改成张三三(修改失败。会出现锁的情况。只有窗口1提交事务后才能修改成功)
UPDATE student SET NAME='张三三' WHERE id=1;

-- 修改id为2数据，姓名改成李四四(修改成功，InnoDB引擎默认加的是行锁)
UPDATE student SET NAME='李四四' WHERE id=2;

-- 修改id为3数据，姓名改成王五五(修改失败，InnoDB引擎如果不采用带索引的列加锁，加的就是表锁)
UPDATE student SET NAME='王五五' WHERE id=3;

-- 提交事务
COMMIT;

InnoDB 共享锁与行锁和表锁

-- 使用数据库
USE db8;

-- 开启事务
START TRANSACTION;

-- 查询id为1数据，并加入共享锁，给索引列加的默认是行锁
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 查询分数为99的数据，并加入共享锁，给非索引列加的就是表锁
SELECT * FROM student WHERE score=99 LOCK IN SHARE MODE;

-- 提交事务
COMMIT;

结论：
1、多个事务中可以对某行数据同时加共享锁，因为共享锁和共享锁兼容。
2、InnoDB引擎默认加的是行锁，所以某一行如果被加锁之后就不能进行修改，但是可以修改其他行数据。
3、InnoDB引擎如果不采用带索引的列加锁，加的就是表锁，那么表中的所有数据都不能被修改。

<2>InnoDB 排他锁演示
排他锁说明: 加锁的数据, 不可以被其他事务加锁查询，可以被普通查询，不支持 增删改。
也称写锁

-- 使用数据库
USE db8;

-- 开启事务
START TRANSACTION;

-- 查询id为1数据(普通查询没问题)
SELECT * FROM student WHERE id=1;

-- 查询id为1数据,并加入共享锁(排他锁和共享锁是不兼容的)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;

-- 查询id为1数据，并加入排他锁(排他锁和排他锁是不兼容的)
SELECT * FROM student WHERE id=1 FOR UPDATE;

-- 修改id为1数据，将姓名改成张三(修改失败，会出现锁的情况。只有窗口1提交事务后才能修改成功)
UPDATE student SET NAME='张三' WHERE id=1;

-- 提交事务
COMMIT;

结论：
1、如果某个事务中对某行数据加了排他锁，那么在其他事务中就不能对这行数据加任意锁查询，但是可以不加锁查询。
2、如果在某个事务某行被加了排他锁，在其他事务中同样不能修改该行数据。

<3>MyISAM写锁演示
写锁说明: 其他连接不能 查询 和 修改 数据

LOCK  TABLE  表名称  WRITE;   	-- 加锁
UNLOCK  TABLES;				  -- 解锁（将当前会话所有的表,进行解锁）

-- 使用数据库
USE db9;

-- 为product表添加写锁
LOCK TABLE product WRITE;

-- 查询
SELECT * FROM product;

-- 修改
UPDATE product SET price=1999 WHERE id=2;

-- 解锁
UNLOCK TABLES;