mysql 行级锁

前言

锁，是执行多线程时用于限制资源访问的一种同步机制。

行级锁

数据库锁根据锁的粒度可分为行级锁、表级锁、页级锁。 行级锁是MySQL中粒度最小的一种锁，表示只对当前操作的数据行进行加锁。粒度越小，实现的成本也就越高。 INNODB的行级锁有共享锁（S锁）和排他锁（X 锁）两种。共享锁允许所有事务读取某些数据，不允许任何事务修改这些数据。排他锁允许当前事物删除或更新或读取某些数据，其他事务不能删除或更新或读取这些数据。另外，MYISAM引擎只支持表级锁，而INNODB引擎能够支持行级锁。

实现原理

InnoDB行锁是通过给索引项加锁来实现的，这一点mysql和oracle不同。InnoDB这种行级锁决定，只有通过索引条件来检索数据，才能使用行级锁，否则，直接使用表级锁。所以，使用行级锁一定要使用索引。

 共享锁：
    用法： SELECT ... LOCK IN SHARE MODE;

作用：共享锁会作用于查询结果集中的每行。所有事务只能读数据，不能修改数据。

 排他锁：

    用法： SELECT ... FOR UPDATE;

    作用：排他锁会作用于查询结果集中的每行。当前事务可读可写数据，其它事务不可读不可写数据。

事务自动提交

每条单独的查询语句都是一个事务（增删改查）。在默认的自动事务提交设置下 ，select 同Update、Insert、Delete一样都会启动一个隐式的事务。

事务中锁释放时机

1）断开数据库客户端连接

2）事务提交(commit)

3）事务回滚(rollback)

示例

创建表结构

CREATE TABLE `developerinfo` (

 `userID` bigint(20) NOT NULL,

 `name` varchar(255) DEFAULT NULL,

 `passWord` varchar(255) DEFAULT NULL,

 PRIMARY KEY (`userID`),

 KEY `PASSWORD_INDEX` (`passWord`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

INSERT INTO `developerinfo` VALUES ('1', 'liujie', '123456');

INSERT INTO `developerinfo` VALUES ('2', 'yitong', '123');

INSERT INTO `developerinfo` VALUES ('3', 'tong', '123456');

通过主键索引字段查询数据-使用排它锁

打开两个查询窗口进行测试

set autocommit = 0;

select * from developerinfo where userid = '1' for update;

结果：一个事务中，通过主键索引字段userID排他查询数据，且使用排它锁。另一个事务中的排他查询查询这些数据会处于阻塞，查询其它数据不会阻塞。

通过非索引字段查询数据-使用排它锁

打开两个查询窗口进行测试

结果：一个事务中，通过非索引字段name排他查询数据，且使用排它锁。另一个事务中的排他查询查询整个表的数据都处于阻塞。

结论：MySQL InnoDB 默认使用行级锁,行级锁都是基于索引的，或者说都是基于索引的sql的。如果SQL 语句中未包含使用索引的字段，是不会使用行级锁的，而会使用表级锁，从而把整个表的数据锁住。

通过非唯一索引字段查询数据-使用排它锁

结果：一个事务中，通过非唯一索引字段password排他查询数据，且使用排它锁。另一个事务中的排他查询查询这些数据会处于阻塞，查询其它数据不会阻塞。

事务提交模式

默认提交模式为自动模式，可手动设置set autocommit=0, 表示当前session禁用自动提交事物,自此句执行以后,每个SQL语句或者语句块所在的事务都需要显示"commit"才能完成事务提交。