mysql各种锁总结

mysql全局锁

读锁（共享锁）

阻止其他用户更新，但允许他们读取数据。

写锁（排他锁）

阻止其他用户读取和更新数据。
全局锁场景：进行数据库备份

数据库备份

背景：备份数据肯定要保证数据一致性，比如你备份数据的时候插入或者修改了数据，肯定造成数据错乱。如果是那种订单表和套餐表。原来套餐表没有数据，如果备份过程中没有加锁，写入了一条98超值双人套餐记录

备份方式一

表结构

DROP TABLE IF EXISTS `bk_book`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `bk_book` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,`title` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '标题',`short_description` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '简短介绍',`description` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '简介',`abstract` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '摘要信息',`is_finished` smallint(3) DEFAULT '1' COMMENT '是否完结 1是 其他否',`release_time` datetime DEFAULT NULL COMMENT '上架时间',`published_day` date DEFAULT NULL COMMENT '出版日期',`isbn` char(15) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'ISBN',`main_source_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '主站ID,主要用来标记信息体主要来源',`state` smallint(11) DEFAULT '1' COMMENT '状态 1可用 其他不可用',`is_indexed` tinyint(2) NOT NULL DEFAULT '0' COMMENT '标记是否已经更新到bk_index, 1是 0否',`created_at` datetime DEFAULT NULL COMMENT '创建时间',`updated_at` datetime DEFAULT NULL,`is_manual` int(4) NOT NULL DEFAULT '0' COMMENT '是否人工录入 1:是 0:否',`rating_label` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',`word_count` int(11) NOT NULL DEFAULT '0' COMMENT '书籍字数,审核后台排序使用',`title_alias` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '标题别名',`type` int(1) NOT NULL DEFAULT '1' COMMENT '图书的类型：1-txt,2-epub,3-pdf',`sub_type` int(4) NOT NULL DEFAULT '1' COMMENT '图书的类型：1-txt,2-epub,3-pdf,40-条漫,41-页面',`add_by` tinyint(4) NOT NULL DEFAULT '1' COMMENT '图书内容接入方式：1-api.2-spider,3-sdk',`url` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'epub/pdf图书的下载地址',`off_reason` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '下架理由',`content_type` int(1) NOT NULL DEFAULT '1' COMMENT '图书内容类型：1-cp,2-非cp',`repeat_book_id` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '重复的图书id',PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1676792430644794443 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;--
-- Dumping data for table `bk_book`
--
INSERT INTO `bk_book` VALUES (1530436864545149217,'绝世剑神','','天灵大陆，武道为尊！强者可以遨游天地，威震山河，弹指日月变色，翻手苍穹覆灭！神州大地年轻的武学宗师林辰，意外带着一尊神秘小鼎来到这个世界，成为雁南域天极宗的外门弟子。自此，海阔凭鱼跃，天高任鸟飞！且看林辰如何在这异界一步步踏上巅峰，凌九霄，破万劫，终成一代绝世剑神','',0,'1900-01-01 00:00:00','1900-01-01','',70,1,1,'2022-05-28 14:32:37','2023-01-03 14:20:47',1,'N',15329571,'[]',1,0,1,'http://api.yuedu.163.com/source/31762008','合约到期自动下线',1,'')
/*!40000 ALTER TABLE `bk_book` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

1、设置表只读模式 flush tables with read lock;
2、新的终端执行更新UPDATE base_book.bk_bookSETstate= 0 WHEREid = 1530436864545149217;发现一只在转圈就是被锁住了
3、备份数据库 mysqldump -uroot -p3306 base_book bk_book >bk_book.sql
4、备份完毕后执行unlock tables; 释放锁
最后发现锁解除就能成功更新sql语句了

备份方式二

利用mysql 的MVCC多版本并发控制，他可以保证数据一致性，能够保证数据库备份时保证。
–single-transaction:使用 --single-transaction 可以避免在备份大型数据库时出现长时间的锁定或阻塞现象，对生产环境的业务操作影响较小。但需要注意的是，备份的数据库引擎必须支持事务（如 InnoDB 引擎），如果使用的是 MyISAM 等不支持事务的引擎，该选项将被忽略。
mysqldump --single-transaction -uroot -p3306 base_book bk_book > bk_book.sql同样达到数据一致性效果并且不用加锁。

表级锁

发生情况：
1、alter table
修改表：比如添加列、删除列、改变列类型。会造成整个表被锁住
2、drop table 删除整个表和truncate table 删除表所有数据。
会造成整个表被锁住
3、lock tables：加一个或者多个表的锁。lock tables t1 write,t2 read 会给t1 表加写锁，给t2表加读锁。

行锁

行锁只在事务中生效，在事务开启和提交或回滚前，才能对数据锁定。如果在非事务环境中执行slq语句，那么innodb在语句执行后就会立即释放所有锁。

共享锁（S锁）即读锁

排他锁（X锁）即写锁

导致行锁的情况

1、select … for update 悲观锁：这种查询会对选定的行添加一个排他锁(X锁写锁)，意味着其他事务不能修改这些行，也不能对这些行添加共享锁（读锁）。
2、select … lock in share mode 这种会对选定的行添加一个共享锁(S锁)，意味着其他事务不能修改这些行，但是可以添加共享锁（读锁）。
3、insert：插入操作会对新添加的行添加一个排他锁-X锁-写锁
4、update：更新操作会对被更新的行添加一个排他锁-X锁-写锁
5、delete：删除操作会对被删除的行添加一个排他锁-X锁-写锁

这些锁是在事务中进行的，在事务开启和提交或回滚前，才能对数据锁定。如果在非事务环境中执行slq语句，那么innodb在语句执行后就会立即释放所有锁。