mysql 事务和锁

事务是DBMS得执行单位
开启事务:
set autocommit=0; -- 取消自动提交
或
begin; -- 手动开启一个事务


提交一个事务
commit;


回滚一个事务
rollback;


在mysql的innodb 引擎中，默认每个操作(INSERT,UPDATE,SELECT [FOR UPDATE | LOCK IN SHARE MODE],DELETE)都是一个单独的事务：
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    | 
+---------------+-------+
1 row in set (0.00 sec)
auttocommit=ON 表示把每一个查询当成一个单独的事务,并且自动提交; set auttocommit=0; 需要手动commit才会提交;

在事务中获得的锁都会等到事务提交或回滚时才会释放


测试：
CREATE TABLE `test_01` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) unsigned DEFAULT '0',
  `d` int(11) unsigned DEFAULT '0',
  `c` varchar(120) DEFAULT '0',
  `e` varchar(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `k_a_c` (`a`,`c`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;


mysql> select * from test_01;
+----+------+------+----------+-----------+
| id | a    | d    | c        | e         |
+----+------+------+----------+-----------+
|  1 |    0 |   11 | qq       | 中问    | 
|  2 |    1 | NULL | abc      | abc       | 
|  3 |    2 | NULL | bda      | abc       | 
|  4 |    1 | NULL | asd      | abc       | 
|  5 |    1 | NULL | 23adsf   | 中a文   | 
|  6 |    1 | NULL | asdfasdf | 中啊文 | 
+----+------+------+----------+-----------+


update test_01 set d=21 where id=1;


打开一个终端连接
connection 1;
事务1;


-- set autocommit=0;
begin;
update test_01 set d=d-10 where id=1 and d>20;
show variables like 'autocommit';
-- rollback; 回滚事务或提交事务释放事务中获取的所有资源，包括锁
-- commit;


打开另一个个终端连接
connection 2;
事务2;


-- set autocommit=0;
begin;
update test_01 set d=d-10 where id=1 and d>20; -- 这里会等待获取一把写锁,事务1提交后，这里会接着往下执行
show variables like 'autocommit';
-- rollback;

-- commit;

update 全表和insert

事务1

set autocommit=0;
begin;
update test_01 set a=1032;#锁表

--update test_01 set a=1032 where id=1;#行锁

事务2

insert into test_01 (a) values(7);#这里会因为事务1的update 全表而堵住

所以，为了提高mysql的写入并发度，优化mysql update的where来降低锁争用是有一定效果的，比如对主键或唯一索引更新，但如果是普通索引则会锁表

另外，insert的时候如果字段有要给唯一索引字段赋值，则感觉两个事务藐视不是透明，

CREATE TABLE `test_01` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT '0',
  `d` int(11) unsigned DEFAULT '0',
  `c` varchar(120) DEFAULT '0',
  `e` varchar(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_a` (`a`),
  KEY `k_d` (`d`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8

1，

set autocommit=0;
begin;
insert into test_01 (a,d) values(9,9);


2，
insert into test_01 (a,d) values(7,9);#这里不会会堵住

再来一组

1，

set autocommit=0;
begin;
insert into test_01 (a,d) values(7,8);

2，
insert into test_01 (a,d) values(7,9);#这里会堵住

所以数据在插入的时候，好像可以看到其他事务对数据的修改，进而减少不必要的锁