爱站程序员基地事务基本要数（ACID)
1 原子性（ATOMICITY)
事务开始所有操作要么全部做完，要么全部不做，不可能停留在中间环节。事务执行过程中出错要回滚到事务开始前的状态。
2 一致性（CONSISTENCY)
事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账不可能A扣了钱，B却没有收到
3 隔离性（ISOLATION)
同一时间，只容许一个事务请求同一数据，不同事务之间彼此没有任何干扰。
4 持久性 （DURABILITY)
事务并发带来隔离问题
MySQL的众多存储引擎中，只有InnoDB支持事务，所有这里说的事务隔离级别指的是InnoDB下的事务隔离级别。
读未提交：
一个事务可以读取到另一个事务未提交的修改。
这会带来脏读、幻读、不可重复读问题。（基本没用）
读已提交：
一个事务只能读取另一个事务已经提交的修改。
其避免了脏读，但仍然存在不可重复读和幻读问题。
可重复读：
同一个事务中多次读取相同的数据返回的结果是一样的。
其避免了脏读和不可重复读问题，但幻读依然存在。
串行化：
事务串行执行。避免了以上所有问题。
以上是SQL-92标准中定义的四种隔离级别。在MySQL中，默认的隔离级别是REPEATABLE-READ（可重复读），并且解决了幻读问题。
简单的来说，Mysql的默认隔离级别解决了脏读、幻读、不可重复读问题。
MYSQL 实现事务隔离并且解决并发带来各种问题,依靠的是锁这个东西
INNODB 锁: 行锁和表锁
行锁有:
1 共享锁(S) 排它锁(X)行共享锁是当读取一条记录时防止别人修改而加的锁SELECT FOR SHARE; SELECT .. LOCK IN SHARE MODE行排它锁是当修改一条记录时防止其他事务同时修改而加的锁SELECT .. FOR UPDATE2 记录锁(Record Lock)
记录锁是锁住记录，锁住索引记录，而不是真正的数据记录,为此应该称谓索引记录锁
锁是非主键索引，会在索引记录上加锁后，在去主键索引上加锁
表上没有索引，会在隐藏的主键索引上加锁
如果要锁的列没有索引，进行全表记录加锁
3 间隙锁(Gap Lock)锁定 索引记录之间的间隙,或第一条或最后一条之前的间隙。这不难理解，比如说如下语句。
SELECT *FROM TWHRE AGE > 10 AND AGE < 24FOR UPDATE
那么它会在10到24之间加锁，插入15就被阻塞4 下个关键字锁(NEXT-KEY Lock)是 间隙锁+记录锁的组合5 插入意向锁插入的时候会获取插入行的排它锁，同时会添加插入意向锁到值的间隙。 原本有值4,7，欲插入5,6 那么在4,7之间加意向锁。
表锁: 1 意向锁 2 自增锁
SELECT … FOR SHARE：添加表的意向共享锁（IS）获取行共享之前
SELECT … FOR UDPATE ：添加表的意向排它锁（IX）获取行排它之前
自增锁处理表自增ID的并发，主要参数是INNODB_AUTOINC_LOCK_MODE
锁模式：
加锁测试
mysql> select * from books;+----+------------------------+-------+---------------------+| id | title | price | publishDate |+----+------------------------+-------+---------------------+| 1 | Java编程思想 | 98.50 | 2005-01-02 00:00:00 || 2 | HeadFirst设计模式 | 55.70 | 2010-11-09 00:00:00 || 3 | 第一行Android代码 | 69.90 | 2015-06-23 00:00:00 || 4 | C++编程思想 | 88.50 | 2004-01-09 00:00:00 || 5 | HeadFirst Java | 55.70 | 2013-12-17 00:00:00 || 6 | 疯狂Android | 19.50 | 2014-07-31 00:00:00 |+----+------------------------+-------+---------------------+6 rows in set (0.01 sec)
查看建表语句
mysql> show create table books;+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| books | CREATE TABLE `books` (`id` int NOT NULL AUTO_INCREMENT COMMENT \'编号\',`title` varchar(100) NOT NULL COMMENT \'书名\',`price` decimal(10,2) DEFAULT NULL COMMENT \'价格\',`publishDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT \'出版日期\',PRIMARY KEY (`id`),UNIQUE KEY `title` (`title`),KEY `ix_book_price` (`price`) USING BTREE /*!80000 INVISIBLE */,KEY `ix_book_pubdate` (`publishDate`) /*!80000 INVISIBLE */) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
有主键ID,唯一索引TITLE,两个普通索引.
取消自动事务
mysql> set autocommit= off;
对表加锁
mysql> select * from books for update;+----+------------------------+-------+---------------------+| id | title | price | publishDate |+----+------------------------+-------+---------------------+| 1 | Java编程思想 | 98.50 | 2005-01-02 00:00:00 || 2 | HeadFirst设计模式 | 55.70 | 2010-11-09 00:00:00 || 3 | 第一行Android代码 | 69.90 | 2015-06-23 00:00:00 || 4 | C++编程思想 | 88.50 | 2004-01-09 00:00:00 || 5 | HeadFirst Java | 55.70 | 2013-12-17 00:00:00 || 6 | 疯狂Android | 19.50 | 2014-07-31 00:00:00 |+----+------------------------+-------+---------------------+6 rows in set (0.01 sec)
查看锁信息(MYSQL8.0)
SELECTengine_lock_id,engine_transaction_id,thread_id,object_schema,object_name,index_name,lock_type,lock_mode,lock_status,lock_dataFROMperformance_schema.data_locks;+---------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+-----------+-------------+------------------------+| engine_lock_id | engine_transaction_id | thread_id | object_schema | object_name | index_name | lock_type | lock_mode | lock_status | lock_data |+---------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+-----------+-------------+------------------------+| 140093717142640:1060:140093646015904 | 10253 | 51 | bookstore | books | NULL | TABLE | IX | GRANTED | NULL || 140093717142640:3:4:1:140093646012928 | 10253 | 51 | bookstore | books | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record || 140093717142640:3:4:2:140093646012928 | 10253 | 51 | bookstore | books | PRIMARY | RECORD | X | GRANTED | 1 || 140093717142640:3:4:3:140093646012928 | 10253 | 51 | bookstore | books | PRIMARY | RECORD | X | GRANTED | 2 || 140093717142640:3:4:4:140093646012928 | 10253 | 51 | bookstore | books | PRIMARY | RECORD | X | GRANTED | 3 || 140093717142640:3:4:5:140093646012928 | 10253 | 51 | bookstore | books | PRIMARY | RECORD | X | GRANTED | 4 || 140093717142640:3:4:6:140093646012928 | 10253 | 51 | bookstore | books | PRIMARY | RECORD | X | GRANTED | 5 || 140093717142640:3:4:7:140093646012928 | 10253 | 51 | bookstore | books | PRIMARY | RECORD | X | GRANTED | 6 |+---------------------------------------+-----------------------+-----------+---------------+-------------+------------+-----------+-----------+-------------+------------------------+8 rows in set (0.00 sec)
为了阅读方便
mysql> SELECT-> object_name,-> index_name,-> lock_type,-> lock_mode,-> lock_status,-> lock_data-> FROM-> performance_schema.data_locks;+-------------+------------+-----------+-----------+-------------+------------------------+| object_name | index_name | lock_type | lock_mode | lock_status | lock_data |+-------------+------------+-----------+-----------+-------------+------------------------+| books | NULL | TABLE | IX | GRANTED | NULL || books | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record || books | PRIMARY | RECORD | X | GRANTED | 1 || books | PRIMARY | RECORD | X | GRANTED | 2 || books | PRIMARY | RECORD | X | GRANTED | 3 || books | PRIMARY | RECORD | X | GRANTED | 4 || books | PRIMARY | RECORD | X | GRANTED | 5 || books | PRIMARY | RECORD | X | GRANTED | 6 |+-------------+------------+-----------+-----------+-------------+------------------------+8 rows in set (0.01 sec)
1 对表加了IX 锁;
2 对记录加了NEXT-KEY X锁
3 SUPREMUM 最大索引值之后加入NEXT-KEY锁.
RR 级别
1 WHREE 主键
mysql> select * from books where id = 1 for update;+----+------------------+-------+---------------------+| id | title | price | publishDate |+----+------------------+-------+---------------------+| 1 | Java编程思想 | 98.50 | 2005-01-02 00:00:00 |+----+------------------+-------+---------------------+1 row in set (0.00 sec)+-------------+------------+-----------+---------------+-------------+-----------+| object_name | index_name | lock_type | lock_mode | lock_status | lock_data |+-------------+------------+-----------+---------------+-------------+-----------+| books | NULL | TABLE | IX | GRANTED | NULL || books | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 |+-------------+------------+-----------+---------------+-------------+-----------+2 rows in set (0.00 sec)
通过主键加了行锁 X型 索引记录.
2 WHERE 普通索引
mysql> select * from books where price = 88.50 for update;+----+-----------------+-------+---------------------+| id | title | price | publishDate |+----+-----------------+-------+---------------------+| 4 | C++编程思想 | 88.50 | 2004-01-09 00:00:00 |+----+-----------------+-------+---------------------+1 row in set (0.01 sec)+-------------+------------+-----------+-----------+-------------+------------------------+| object_name | index_name | lock_type | lock_mode | lock_status | lock_data |+-------------+------------+-----------+-----------+-------------+------------------------+| books | NULL | TABLE | IX | GRANTED | NULL || books | PRIMARY | RECORD | X | GRANTED | supremum pseudo-record || books | PRIMARY | RECORD | X | GRANTED | 1 || books | PRIMARY | RECORD | X | GRANTED | 2 || books | PRIMARY | RECORD | X | GRANTED | 3 || books | PRIMARY | RECORD | X | GRANTED | 4 || books | PRIMARY | RECORD | X | GRANTED | 5 || books | PRIMARY | RECORD | X | GRANTED | 6 |+-------------+------------+-----------+-----------+-------------+------------------------+8 rows in set (0.01 sec)
居然是全表加锁 ! 丢
3 WHERE 唯一索引
mysql> select * from books where title=\'C++编程思想\' for update;+----+-----------------+-------+---------------------+| id | title | price | publishDate |+----+-----------------+-------+---------------------+| 4 | C++编程思想 | 88.50 | 2004-01-09 00:00:00 |+----+-----------------+-------+---------------------+1 row in set (0.01 sec)+-------------+------------+-----------+---------------+-------------+----------------------+| object_name | index_name | lock_type | lock_mode | lock_status | lock_data |+-------------+------------+-----------+---------------+-------------+----------------------+| books | NULL | TABLE | IX | GRANTED | NULL || books | title | RECORD | X,REC_NOT_GAP | GRANTED | \'C++编程思想\', 4 || books | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 4 |+-------------+------------+-----------+---------------+-------------+----------------------+3 rows in set (0.01 sec)
这就两个行锁了,两个索引记录锁. 简单没GAP
这是规范的表要求,有些没有主键的表情况就更加糟糕了。从这里我们看出根据普通索引，非唯一的话进行DML操作基本上是锁全表。
这比ORACLE 就差多了，非唯一索引的话，找出一个ROWID，就锁一个行，更新一行，接着再找下一个ROWID，直到索引找完，然后提交。相对来说ORACLE比较精准打击，MYSQL是范围杀伤！
有些DBA建议把MYSQL 设置成RC级别。 RC级别的MYSQL无法完成事务的隔离机制，会出现幻读，不可重复读的毛病。
//查看当前事物级别：SELECT @@tx_isolation;select @@transaction_isolation;//设置read uncommitted级别：set session transaction isolation level read uncommitted;//设置read committed级别：set session transaction isolation level read committed;//设置repeatable read级别：set session transaction isolation level repeatable read;//设置serializable级别：set session transaction isolation level serializable;mysql> select * from books where publishDate=\'2004-01-09 00:00:00\' for upate;+----+-----------------+-------+---------------------+| id | title | price | publishDate |+----+-----------------+-------+---------------------+| 4 | C++编程思想 | 88.50 | 2004-01-09 00:00:00 |+----+-----------------+-------+---------------------+1 row in set (0.00 sec)+-------------+------------+-----------+---------------+-------------+-----------+| object_name | index_name | lock_type | lock_mode | lock_status | lock_data |+-------------+------------+-----------+---------------+-------------+-----------+| books | NULL | TABLE | IX | GRANTED | NULL || books | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 4 |+-------------+------------+-----------+---------------+-------------+-----------+2 rows in set (0.01 sec)mysql> select * from books where price = 55.70 for update;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
第一个普通时间索引 RC下只加了个索引记录锁,
第二个SQL 要加锁就被阻塞了,虽然使用普通价格索引.
最后阻塞时查看锁类型
+-------------+------------+-----------+---------------+-------------+-----------+| object_name | index_name | lock_type | lock_mode | lock_status | lock_data |+-------------+------------+-----------+---------------+-------------+-----------+| books | NULL | TABLE | IX | GRANTED | NULL || books | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 2 || books | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 4 || books | NULL | TABLE | IX | GRANTED | NULL || books | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 4 |+-------------+------------+-----------+---------------+-------------+-----------+5 rows in set (0.00 sec)
这就奇怪了第二个语句只有第2行和第5行啊,为啥要加第4行锁?
mysql> select * from books;+----+------------------------+-------+---------------------+| id | title | price | publishDate |+----+------------------------+-------+---------------------+| 1 | Java编程思想 | 98.50 | 2005-01-02 00:00:00 || 2 | HeadFirst设计模式 | 55.70 | 2010-11-09 00:00:00 || 3 | 第一行Android代码 | 69.90 | 2015-06-23 00:00:00 || 4 | C++编程思想 | 88.50 | 2004-01-09 00:00:00 || 5 | HeadFirst Java | 55.70 | 2013-12-17 00:00:00 || 6 | 疯狂Android | 19.50 | 2014-07-31 00:00:00 |+----+------------------------+-------+---------------------+6 rows in set (0.01 sec)
加最后一行也被阻塞
select * from books where price =19.50 for update;+-------------+------------+-----------+---------------+-------------+-----------+| object_name | index_name | lock_type | lock_mode | lock_status | lock_data |+-------------+------------+-----------+---------------+-------------+-----------+| books | NULL | TABLE | IX | GRANTED | NULL || books | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 4 || books | NULL | TABLE | IX | GRANTED | NULL || books | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 4 |+-------------+------------+-----------+---------------+-------------+-----------+4 rows in set (0.01 sec)
加第一行也如此
select * from books where price =98.50 for update;+-------------+------------+-----------+---------------+-------------+-----------+| object_name | index_name | lock_type | lock_mode | lock_status | lock_data |+-------------+------------+-----------+---------------+-------------+-----------+| books | NULL | TABLE | IX | GRANTED | NULL || books | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 1 || books | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 4 || books | NULL | TABLE | IX | GRANTED | NULL || books | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 4 |+-------------+------------+-----------+---------------+-------------+-----------+5 rows in set (0.00 sec)
这样说来RC级别加锁的范围减小了,没有NEXT-KEY 锁. 使用普通索引也不会全表记录加锁了.如果不设置自动提交的话也容易造成阻塞,一个表并发能力只有1个事务才能操作.
