MySQL 11：MySQL锁

锁是一种机制，计算机通过这种机制协调多个进程或线程对资源的并发访问（以避免争用）。在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争夺外，数据也是一种被众多用户共享的资源。如何保证并发访问数据的一致性和有效性是所有数据库必须解决的问题，而锁冲突也是影响数据库并发访问性能的重要因素。从这个角度来说，锁对于数据库来说尤为重要，也更加复杂。

从数据操作的粒度：

1）表锁：在运行过程中，整个表都会被锁住。

2) 行锁：在操作过程中，当前操作的行会被锁住。

从数据操作的类型来看：

1）读锁（共享锁）：对于同一条数据，可以同时进行多个读操作，互不影响。

2）写锁（排他锁）：在当前操作完成之前，会阻塞其他的写锁和读锁。

与其他数据库相比，MySQL的锁机制比较简单，其最显着的特点是不同的存储引擎支持不同的锁机制。 下表列出了各个存储引擎对锁的支持：

MySQL锁的特性可大致归纳如下 ：

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web 应用；

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

MyISAM

MyISAM 存储引擎只支持表锁。

在执行一条查询语句（SELECT）之前，MyISAM会自动对涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动对涉及的表加写锁 . 这个过程不需要用户干预，因此用户一般不需要直接用LOCK TABLE命令显式锁定MyISAM表。

--加读锁
lock table table_name read; 

--加写锁
lock table table_name write;

特点

1）对MyISAM表的读操作不会阻塞其他用户对同一张表的读请求，但会阻塞对同一张表的写请求；

2）对MyISAM表的写操作会阻塞其他用户对同一张表的读写操作；

简而言之，读锁会阻塞写，但不会阻塞读。另一方面，写锁会阻止读取和写入。另外MyISAM的读写锁调度是写优先的，这也是为什么MyISAM不适合作为面向写的表的存储引擎。 因为写完锁后，其他线程不能做任何操作，大量的更新会使查询很难获得锁，造成永久阻塞。

-- MySQL的锁机制
drop database if exists  mydb14_lock;
create database mydb14_lock ;
 
use mydb14_lock;
  
create table `tb_book` (
  `id` int(11) auto_increment,
  `name` varchar(50) default null,
  `publish_time` date default null,
  `status` char(1) default null,
  primary key (`id`)
) engine=myisam default charset=utf8 ;
 
insert into tb_book (id, name, publish_time, status) values(null,'java编程思想','2088-08-01','1');
insert into tb_book (id, name, publish_time, status) values(null,'solr编程思想','2088-08-08','0');
 
create table `tb_user` (
  `id` int(11) auto_increment,
  `name` varchar(50) default null,
  primary key (`id`)
) engine=myisam default charset=utf8 ;
 
insert into tb_user (id, name) values(null,'令狐冲');
insert into tb_user (id, name) values(null,'田伯光');

InnoDB

特点：

行锁偏向 InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

InnoDB 与 MyISAM 的最大不同有两点：一是支持事务；二是采用了行级锁。

InnoDB 实现了以下两种类型的行锁。

共享锁（S）：又称读锁，简称S锁，共享锁是指多个事务可以对同一个数据共享一个锁，都可以访问该数据，但只能读，不能被修改。

独占锁（X）：又称写锁，简称X锁，独占锁不能与其他锁共存。 例如，如果一个事务获取了数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取了排他锁的事务可以读取和修改数据。

对于 UPDATE、DELETE 和 INSERT 语句，InnoDB 会自动给涉及的数据集加一个排它锁（X）；

对于普通的 SELECT 语句，InnoDB 不加任何锁；

--共享锁（S）
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;
--排他锁（X) 
SELECT * FROM table_name WHERE ... FOR UPDATE;

例如：

-- 行锁 
drop table if exists test_innodb_lock;
create table test_innodb_lock(
    id int(11),
    name varchar(16),
    sex varchar(1)
)engine = innodb ;
 
insert into test_innodb_lock values(1,'100','1');
insert into test_innodb_lock values(3,'3','1');
insert into test_innodb_lock values(4,'400','0');
insert into test_innodb_lock values(5,'500','1');
insert into test_innodb_lock values(6,'600','0');
insert into test_innodb_lock values(7,'700','0');
insert into test_innodb_lock values(8,'800','1');
insert into test_innodb_lock values(9,'900','1');
insert into test_innodb_lock values(1,'200','0');
 
create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);