mysql学习

mysql基础定义

mysql的锁：

乐观锁和悲观锁

乐观锁

乐观锁不是数据库自带的，需要我们自己去实现。
乐观锁是指 更新数据库时认为操作不会导致冲突，在操作数据时不加锁，而在进行更新后，再去判断是否有冲突了。
通常实现是这样的：在表中的数据进行更新时，先给数据表加一个版本(version)字段，每操作一次，将那条记录的版本号加1。
先查询出那条记录，获取出version字段。
如果要对那条记录进行更新操作，则先判断此刻version的值是否与刚刚查询出来时的version的值相等。
如果相等，则说明这段期间没有其他程序对其进行操作，则可以执行更新，将version字段的值加1。
如果version值与刚刚获取出来的version的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。
举例：下单操作包括3步骤：
1、查询出商品信息
select (status,status,version) from t_goods where id=#{id}
2、根据商品信息生成订单
3、修改商品status为2
update t_goods set status=2,version=version+1 where id=#{id} and version=#{version};
除了自己手动实现乐观锁之外，现在网上许多框架已经封装好了乐观锁的实现，如hibernate，需要时，可能自行搜索"hiberate 乐观锁"试试看。

悲观锁
悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。
悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。
悲观锁涉及到的另外两个锁：就是共享锁与排它锁。
共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。
共享锁与排他锁
数据库的增删改操作默认都会加排他锁，而查询不会加任何锁。

共享锁和排他锁

共享锁
悲观锁一般数据库已经实现了，共享锁也属于悲观锁的一种，那么共享锁在mysql中是通过什么命令来调用呢。
在执行语句后面加上 lock in share mode就代表对某些资源加上共享锁了。

排他锁
update、insert、delete 语句会自动加排它锁。
在执行的语句后面加上 for update
select * from table where id=1 for update

行锁与表锁

行锁
mysql锁机制分为表级锁和行级锁。mysql行级锁包括共享锁与排他锁：
共享锁又称为读锁，简称S锁
共享锁：多个事务都可以加共享锁读同⼀⾏数据，但是别的事务不能写这⾏数据。
一个事务获取了共享锁，其他事务也只能加共享锁或不加锁查询。
其他事务不能写。
其他事务加排他锁查不到，因为排他锁与共享锁不能存在同一数据上。
并发处理可以用读锁和写锁来控制
读锁是共享的，或者说是相互不阻塞的，多个客户在同一时刻可以读取同一个资源，而互不干扰，

排他锁又称为写锁，简称X锁
排他锁：⼀个事务可以读/写这⾏数据，别的事务只能读不能写。
一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁。
写锁则是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，这是出于安全策略的考虑，只有这样，才能确保在给定的时间里，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源
获取排他锁的事务可以对数据就行读取和修改。
其他事务可以通过select …from…查询数据，因为普通查询没有任何锁机制。
其他事务不能写。
mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型。
加排他锁可以使用select …for update语句
加共享锁可以使用select … lock in share mode语句

表锁

innodb的 表锁，分成意向共享锁 和意向排他锁，表锁是innodb引擎⾃动加的，不⽤你⾃⼰去加。
意向共享锁：加共享⾏锁时，必须先加共享表锁；
意向排他锁 ：给某⾏加排他锁时，必须先给表加排他锁。
mysql共享锁与排他锁 - java攻城狮 - 博客园
insert、update、delete，innodb会⾃动给那⼀⾏加⾏级排他锁。
select，innodb啥锁都不加。
innodb从来不会⾃⼰主动加个共享锁的，除⾮你⽤下⾯的语句⾃⼰⼿动加个锁：
⼿动加共享锁：select * from table where id=1 lock in share mode，那你就给那⼀⾏加了个共享锁，其他事务就不能来修改这⾏数据了。
⼿动加排他锁：select * from table where id=1 for update，那你就给那⼀⾏加了个排他锁，意思就是你准备修改，别的事务就别修改了，别的事务的修改会hang住。这个要慎⽤，⼀般我们线上系统不⽤这个，容易搞出问题来。
对⼀⾏数据，如果有⼈在修改会加个排他锁，然后你不能修改只能等着获取这把锁，但这时可以随便select，查询你的事务开始之前那⾏数据的某个版本。然后如果你修改某⾏数据，会同时拿这个表的排他锁，但是如果不同的事务修改不同的⾏，会拿不同⾏的⾏级排他锁，但⼤家都会拿⼀个表的排他锁，实际上 innodb 的表级排他锁可以随便拿，这个是没冲突的。
mysql innodb 存储引擎的默认锁模式，其实还挺不错的。相当于就是⼀⾏数据，同⼀个时刻只能⼀个⼈在修改，但是别⼈修改，你可以随便读，读是读某个版本的，⾛mvcc 机制。
其实innodb下的记录锁（也叫行锁），间隙锁，next-key锁统统属于排他锁。

Record Lock — 单个行记录上的锁；
Gap Lock — 间隙锁，锁定一个范围，不包括记录本身；
Next-Key Lock — 锁定一个范围，包括记录本身。

生活中的间隙锁
编程的思想源于生活，生活中的例子能帮助我们更好的理解一些编程中的思想。生活中排队的场景，小明，小红，小花三个人依次站成一排，此时，如何让新来的小刚不能站在小红旁边，这时候只要将小红和她前面的小明之间的空隙封锁，将小红和她后面的小花之间的空隙封锁，那么小刚就不能站到小红的旁边。这里的小红，小明，小花，小刚就是数据库的一条条记录。
他们之间的空隙也就是间隙，而封锁他们之间距离的锁，叫做间隙锁。
Mysql中的间隙锁
表中id为主键，number字段上有非唯一索引的二级索引，有什么方式可以让该表不能再插入number=5的记录？注意：此时按照二级索引建索引表，即插入时按照number顺序排序。
只要控制几个点，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）；
间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：
防止间隙内有新数据被插入。
防止已存在的数据，更新成间隙内的数据（例如防止numer=3的记录通过update变成number=5）
innodb自动使用间隙锁的条件：
必须在RR级别下
检索条件必须有索引（没有索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加）

MyISAM 操作数据都是使用的表锁，你更新一条记录就要锁整个表，导致性能较低，并发不高。当然同时它也不会存在死锁问题。
InnoDB 与 MyISAM 的最大不同有两点：
一是 InnoDB 支持事务；
二是 InnoDB 采用了行级锁。也就是你需要修改哪行，就可以只锁定哪行。
悲观锁实际使用了排他锁来实现（select **** for update）。文章开头说到，innodb加行锁的前提是：必须是通过索引条件来检索数据，否则会切换为表锁。
因此，悲观锁在未通过索引条件检索数据时，会锁定整张表。导致其他程序不允许“加锁的查询操作”，影响吞吐。故如果在查询居多的情况下，推荐使用乐观锁。
“加锁的查询操作”：加过排他锁的数据行在其他事务中是不能修改的，也不能通过for update或lock in share mode的加锁方式查询，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。
乐观锁更新有可能会失败，甚至是更新几次都失败，这是有风险的。所以如果写入居多，对吞吐要求不高，可使用悲观锁。
也就是一句话：读用乐观锁，写用悲观锁。
锁的弊端：
加锁也是需要消耗资源，锁的各种操作，包括获得锁和检查所是否已解除，释放锁等，都会增加系统的开销，如果消耗大量的时间来管理锁，而不是存取书籍，那么系统的性能可能会因此收到影响
有表锁和行级锁，行级锁可以最大程度的支持并发处理，同时也带来了最大的锁开销

mysqll的事务

事务
事务是一个最小的不可在分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务是一个最小的工作单元)
一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成。
事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同。

事务的四大特性：

原子性：
一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一个部分操作这就是事务的原子性。

一致性：
数据库总是从一个一致性的状态转换到另外一个一致性的状态，比如事务执行结束以后数据库的完整性约束没有被破坏，开始是1000元，后面转账结束了也是1000元。

隔离性：
一个事务所做的修改在最终提交以前，对其他事务是不可见的，并发的各个事务之间不能相互干扰。

持久性：
指事务一旦提交，则其所做的修改就会永久保存在数据库中，及时系统崩溃修改的数据也不会丢失。其他操作或者故障不应该对其造成影响。

mysql的四种隔离级别

READ UNCOMMITTED(未提交读)：
最低的事务隔离级别，事务中的修改，及时没有提交，对其他的事务也都是可见的，事务可以读取未提交的数据（可能造成脏读、不可重复读、幻读）

READ COMMITTED (提交读也叫不可重复读)：
换句话说，一个事务开始时只能看到已经提交的事务所做的修改，一个事务从开始到提交之前，所做的任何修改对其他事务都是不可见的，也就是允许读取并发事务已经提交的数据，可以避免脏读，但是可能造成不可重复、幻读，可重复读是mysql默认的事务隔离级别

REPEATABLE READ (可重复读)：
同一个事务中多次读取同样记录的结果都是一致的，对同一字段多次读取的结果都是一致的，除非本身事务修改，可以避免脏读和不可重复读，但是可能造成幻读。
可重复读的重点是修改，幻读的重点在于新增或者删除。

SERIALIZABLE(可串行化)：
是mysql最高的隔离级别，在通过强制事务串行执行，避免了幻读的问题，简单的来说，它会在每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题，实际应用中也很少用收到这个事务隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下才会考虑用该级别

幻读
所为幻读，就是指当某个事务在读取某个范围的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围内的记录时就会产生换行），innodb和xtradb存储引擎通过多版本并发控制MVCC解决了幻读的问题。

赃读
脏读是指事务读取到其他事务未提交的数据
例如：有事务A、B和一条记录：id为1，name为张三
B首先进行更新操作，将name的值由张三改为张老三，但还未提交事务
begin;
update stu set name = ‘张老三’ where id = 1
然后A进行查询操作，查询姓名为张老三
select name from stu where id = 1
然后B由于某种原因被回滚，name自然从张老三恢复到了张三
A就会出现一个问题，读取的是张老三，但数据库中实际存储的是张三，我到底是用哪一个呢？
这就是脏读，事务A读取到事务B未提交的数据

死锁：
俩个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象，当多个事务试图以不同的顺序锁定资源时就可能会产生死锁，锁客事务同时锁定同一个资源时，也会产生死锁。

innodb目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。大多数情况下只需要重新执行因死锁回滚的事务即可。

自动提交AUTOCOMMIT
SET AUTOCOMMIT = 1; 开启 0 是禁用；

多版本并发控制：
MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁的操作，因此开销更低，虽然实现的机制有所不同，但大都实现了非阻塞的读操作，写操作也只是锁定了必要的行，他只在可重复读和提交读俩个隔离级别下工作其他的俩个隔离级别怼他不兼容，因为READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行，而SERIALIZABLE则会对所有读取的行加锁。

grant all privileges on . to ‘root‘@‘%‘ identified by ‘123456‘ with grant optio
复制
原因： mysql的版本问题，这条语句适用于MySQL8.0之前

解决： mysql8.0及以上版本需要使用以下语句：

create user root@‘%’ identified by ‘123456’;

grant all privileges on . to root@‘%’ with grant option;

查看mysql是否开启：status mysqld.service
链接数据库：mysql -h127.0.0.1 -uroot -p password