mysql 默认带锁update 语句 mysql默认行锁

InnoDB引擎的行锁和表锁

 

mysql常用引擎有MYISAM和InnoDB，而InnoDB是mysql默认的引擎。MYISAM不支持行锁，而InnoDB支持行锁和表锁。

1.行锁和表锁

2.行锁的类型

3.行锁的实现

1.行锁和表锁

在mysql 的 InnoDB引擎支持行锁，与Oracle不同，mysql的行锁是通过索引加载的，即是行锁是加在索引响应的行上的，要是对应的SQL语句没有走索引，则会全表扫描，

行锁则无法实现，取而代之的是表锁。

表锁：不会出现死锁，发生锁冲突几率高，并发低。

行锁：会出现死锁，发生锁冲突几率低，并发高。

锁冲突：例如说事务A将某几行上锁后，事务B又对其上锁，锁不能共存否则会出现锁冲突。（但是共享锁可以共存，共享锁和排它锁不能共存，排它锁和排他锁也不可以）

死锁：例如说两个事务，事务A锁住了1~5行，同时事务B锁住了6~10行，此时事务A请求锁住6~10行，就会阻塞直到事务B施放6~10行的锁，而随后事务B又请求锁住1~5行，事务B也阻塞直到事务A释放1~5行的锁。死锁发生时，会产生Deadlock错误。

锁是对表操作的，所以自然锁住全表的表锁就不会出现死锁。

2.行锁的类型

行锁分 共享锁 和 排它锁。

共享锁又称：读锁。当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。

排它锁又称：写锁。当一个事务对某几个上写锁时，不允许其他事务写，但允许读。更不允许其他事务给这几行上任何锁。包括写锁。

上共享锁的写法：lock in share mode

例如：

上排它锁的写法：for update

例如：select math from zje where math >60 for update；

3.行锁的实现

注意几点：

1.行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。

2.两个事务不能锁同一个索引，例如：

1）先将自动提交关闭

mysql[db]>show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.11 sec)
mysql[db]>set autocommit=off;
Query OK, 0 rows affected (0.01 sec)
mysql[db]>show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

2）创建db库的t1表

mysql[(none)]>create database db;
mysql[(none)]>use db;
mysql[db]>create table t1(id int ,name varchar(20) ) charset=utf8;
Query OK, 0 rows affected (0.06 sec)
mysql[db]>insert into db.t1 set id=1,name=’a’;
mysql[db]>insert into db.t1 set id=2,name=’b’;
mysql[db]>select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)

实例1：当表没有索引列时，客户端A修改一条记录时，B客户端的修改操作需要等待A客户端提交之后，才能提交，实现表锁。

Mysql[(db)]>update db.t1 set name=c where id=2;

同一时间，在客户端B上修改id=1的记录时，就会发现修改语句需要等客户端A执行commit提交之后，才能进行提交。

mysql[(none)]>update db.t1 set name=d where id=1;

实例2：给db.t1表创建索引，A客户端根据索引列修改记录，B客户端根据索引列修改数据，可以两个客户端提交修改，实现行锁。

客户端A:

mysql[db]>create index index_v1 on db.t1 (id);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql[db]>update t1 set name='d' where id=2 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql[db]>select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | d    |
+------+------+
2 rows in set (0.00 sec)

客户端B：

mysql[(none)]>update db.t1 set name='c' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql[(none)]>select * from db.t1;
+------+------+
| id   | name |
+------+------+
|    1 | c    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)

3. insert ，delete ， update在事务中都会自动默认加上排它锁。

客户端A：

mysql[(db)]>update db.t1 set name='c' where id=1;

客户端B：

mysql[(none)]>update db.t1 set name='c' where id=1;

等待中。。。。（排它锁）