mysql读锁脏读_顶MYSQL 表锁以及行锁以及脏读

INNODB以及MYISAM是MYSQL最常见的两种引擎,5.1的版本以后开启INNODB需要加上--with-plugins=innobase

INNODB以及MYISAM在锁的机制上,差别较为明显:

MYISAM全表锁,而INNODB在表有主键的情况下是行锁,没有主键任然是表锁.

其中 语句SELECT X FROM TABLE FOR UPDATE 这条语句对MYISAM无效,

如果要锁MYISAM的表,可以使用(新增锁会释放之前新增的锁)

#只读

LOCK TABLE READ #READ

#读写锁

LOCK TABLE WRITE #WRITE

#释放锁

UNLOCK TABLES;

行锁DEMO:

表结构,区别在于引擎,其他完全一致

CREATE TABLE `t` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`a` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=UTF8

数据如下:

mysql> select * from t;

+----+------+

| id | a |

+----+------+

| 1 | 123 |

| 2 | 2 |

| 3 | 3 |

+----+------+

3 rows in set (0.00 sec)

开启事务(T1):

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

#锁ID为1的数据

mysql> select * from t where id =1 for update;

+----+------+

| id | a |

+----+------+

| 1 | 123 |

+----+------+

1 row in set (0.00 sec)

#当前操作暂停

开启事务(T2):

mysql> select * from t;

+----+------+

| id | a    |

+----+------+

|  1 |  123 |

|  2 |    2 |

|  3 |    3 |

+----+------+

3 rows in set (0.01 sec)

mysql>

mysql> update t set a = 1 where id =1 ;

#处于当前状态时,等待T1 释放锁

#释放后的效果:

Query OK, 1 row affected (18.08 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t;

+----+------+

| id | a    |

+----+------+

|  1 |    1 |

|  2 |    2 |

|  3 |    3 |

+----+------+

3 rows in set (0.00 sec)

释放T1:

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

修改非锁定的行:

步骤任然是T1锁定ID=1的数据,而T2尝试修改ID=3的数据:

mysql> select * from t where id = 1 for update;

+----+------+

| id | a |

+----+------+

| 1 | 1 |

+----+------+

1 row in set (0.00 sec)

mysql> select now();

+---------------------+

| now() |

+---------------------+

| 2014-01-22 15:43:03 |

+---------------------+

1 row in set (0.00 sec)

#锁定ID 为1的数据

mysql> select * from t where id = 1 for update;

+----+------+

| id | a |

+----+------+

| 1 | 1 |

+----+------+

1 row in set (0.00 sec)

#切换到T2

mysql> update t set a=333 where id =2;

Query OK, 1 row affected (0.04 sec)

Rows matched: 1 Changed: 1 Warnings: 0

#数据没有被锁定

#切换到T1查看数据

#数据已经被修改

mysql> select * from t ;

+----+------+

| id | a |

+----+------+

| 1 | 1 |

| 2 | 333 |

| 3 | 3 |

+----+------+

3 rows in set (0.00 sec)

#Rollback 再看数据:

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;

+----+------+

| id | a |

+----+------+

| 1 | 1 |

| 2 | 333 |

| 3 | 3 |

+----+------+

3 rows in set (0.00 sec)

读锁和写锁:

#T1 锁定表

mysql> lock table t read;

Query OK, 0 rows affected (0.00 sec)

#T2尝试读取数据:

mysql> select * from t;

+----+------+

| id | a    |

+----+------+

|  1 |    1 |

|  2 |  333 |

|  3 |    3 |

+----+------+

3 rows in set (0.00 sec)

#没有问题,

#尝试修改数据:

#解锁之前的情况:

mysql> select now();update t set a=22 where id =2;select now(); select * from t;

+---------------------+

| now()               |

+---------------------+

| 2014-01-22 15:55:19 |

+---------------------+

1 row in set (0.00 sec)

#回到T1解锁:

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

#回到T2看看完整的信息:

mysql> select now();update t set a=22 where id =2;select now(); select * from t;

+---------------------+

| now()               |

+---------------------+

| 2014-01-22 15:55:19 |

+---------------------+

1 row in set (0.00 sec)

#看这里,整整锁了28S

Query OK, 1 row affected (28.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

+---------------------+

| now()               |

+---------------------+

| 2014-01-22 15:55:47 |

+---------------------+

1 row in set (0.00 sec)

+----+------+

| id | a    |

+----+------+

|  1 |    1 |

|  2 |   22 |

|  3 |    3 |

+----+------+

3 rows in set (0.00 sec)

#数据被更新

写锁:

写锁和读锁的区别在于,当表被锁定之后,除非解锁,后面的会话进程(SESSION),什么事情也做不了,只能够等待解锁.

未完,待续!