一、mysql的锁机制
mysql的锁机制用来实现mysql的并发访问控制的,mysql的锁类型可以分为如下几种:
1、锁的操作模式
根据执行操作时施加锁的模式,可以将锁分为读锁和写锁。
读锁:用户在读的时候施加该锁,不允许其他用户和当前用户执行写操作,即当前用户和其他用户的写操作处于阻塞状态。但是仍然允许其他用户和当前执行读操作,因此,读锁也称为共享锁。
写锁:用户在执行写操作时施加该锁,不允许其他用户执行读和写操作,即其他用户的读写操作将会处于阻塞状态。当前用户也不可以执行读操作,即当前用户的读操作处于阻塞状态(可以在当前会发下执行读操作,全局级别不能执行读操作)。因此,写锁也称为独占锁或排它锁。
2、锁的粒度
根据锁的粒度,可以将锁分为表锁和行锁。
表锁:即对某个表施加锁,该锁可以为读锁和写锁。
行锁:即对某个行或某些行施加锁,该锁可以分为读锁和写锁。
需要说明的是,锁的粒度越大,其并发性越差,但是系统开销越小;反之,锁的粒度越细,其并发性越好,但是系统开销越大。
举例说明:当用户A在对表tb1执行写操作时,如果用户A对tb1整张表施加锁的话,那么其他用户就无法访问表tb1了,就更别谈修改表tb1中的某些字段了。但是如果用户A在对表tb1只施加了行锁(如只对自己要修改的行加上行锁),那么其他用户仍然可以访问这个表中的其他行(除了施加行锁的行除外),也可以修改其他行的数据,因此,在这种情况下,行锁的并发性比表锁的并发性要好的多,但是给系统带来的开销就比较大。
3、锁的实现位置
根据锁的实现位置可以将锁分为mysql锁和存储引擎锁。
mysql锁:即mysql服务器级别的锁,该锁可以手动使用,手动使用的锁称为显式锁。
存储引擎锁:即存储引擎操作时施加的锁,该锁自动施加的,因此该所也称为隐式锁。
需要说明的是,mysql的锁机制一般都是系统自动进行的,当然也可以手动施加锁。
手动施加锁的命令
加锁命令:LOCK TABLE tb_name {READ|WRIITE};
解锁命令:UNLOCK TABLES;
二、mysql中的事务机制
事务是DBMS中的执行单位,它是有限的数据库操作序列组成的。但是并不是所有的数据库操作序列都能成为事务。简单的说就是由一组查询语句组成,这些查询语句将组成一个不隔分隔的独立单元。
事务的ACID属性
一般说来,事务具有如下4个特性(ACID特性):
1、原子性(Automicity):该特性引起的数据库操作"要么全部执行,要么全部不执行"。
2、一致性(Consistency):该特性表示数据库操作之前和操作之后的最终状态是一致的。比如,两个用户a,b之间相互转账,但是最终两个用户的总金额是不变的。
3、隔离性(Isolation):一个事务所做的修改在提交前对其他事务是不可见的。
4、持久性(Durability):一旦事务提交执行成功,则系统保证在任何故障下,所做的修改永久生效。 事务的持久×××需要借助事务日志来完成。内存中的操作首先同步到事务日志中,再由后台进程将事务日志中的数据同步到磁盘数据文件中。由于事务日志的数据是顺序IO操作,而数据文件则是随机IO操作,因此有了事务日志性能更佳。
mysql中的脏读、不可重复读和幻读的概念
脏读:一个事务读取另一个事务未提交的数据。在Read-Uncommitted隔离级别下,当前事务中可以读到其他事务修改且还没有提交的数据。
不可重复读(non-repeatable reads):一个事务读取到另一个事务提交的更新数据。在某个事务进程下,多次读取另一个事务下的相同数据,由于在另一个事务下修改了改数据,导致在当前事务下多次读取该数据的结果不一样,这就是不可重复读现象。比如:事务A第一次读取表tb的第2行name字段为'hello',由于事务B修改了表tb的第2行name字段为'good',因此当事务B提交时,导致事务A第二次读取表tb的第2行name字段为'good'。注意:读取的一定要是和之前一样的字段,且该字段每次被读取时不一样,由于数据不一样,因此读取的数据当然是不重复的,这就是所谓的不可重复现象。
幻读:一个事务读取到另一个事务已经提交的新插入数据。
一个事务多次读取另一个事务下的数据的时,由于另一个事务新增了一行或是删除了一行,当另一个事务提交时,导致在第一个事务下每次读取的数据不一样,这种数据的不一样,主要体现在读取的数据新增了几行或者减少了几行,因此给人的感觉就是想幻像一样。比如:在事务A下第一次读取表tb的内容为5行,由于事务B删除了tb表中的一行,当事务B提交时,导致事务A第二次读取表tb的内容为4行。因此,给人的感觉就是刚刚明明是5行,现在怎么变成4行了,这种现象向幻像一样,因此叫做幻读。
不可重复读和幻读的区别
不可重复读强调的是每次读取的是相同位置的数据,且该数据在另一个事务下被修改。注重的是修改。这个位置指的是哪一行、哪一个字段的数据。
幻读强调的是第二次读比第一次读取时,内容多了或者少了几行,注重的是新增和删除。
mysql处理事务有2种方法
1、手动执行事务
mysql>begin; 或mysql>start transaction; ##表示启动事务
mysql>rollback; ##撤销之前所做的修改(回滚)
mysql>commit; ##提交事务
2、设置事务的自动提交模式
##查看当前事务是否是自动提交的。0或off表示关闭自动提交;1或on表示开启自动提交。默认自动提交功能开启。
mysql>select @@autocommit;
set {global|session} autocommit=0 ##关闭自动提交功能
set {global|session} autocommit=1 ##开启自动提交功能
建议:明确使用事务,关闭自动提交功能。
事务的执行状态:共有5种状态
1、active:表示当前事务正在进行当中
2、部分提交的:表示语句在执行过程中,由于某种原因(如宕机)导致只执行了一部分。因此事务就处于该状态下。
3、失败:表示事务没有执行成功。
4、终止:由于事务执行失败,因此系统会终止该事务
5、提交成功:表示提交后,事务执行成功。
三、mysql的隔离级别详解
mysql中隔离级别分为4种:
1、READ-UNCOMMITTED:读未提交。该隔离级别下的当前事务可以看到其他未提交事务的执行结果。在该级别下会出现脏读、不可重复读、幻读现象。如有两个事务进程A和B,在事务A和B都开启的情况下,当事务A修改了某些数据时,即使事务A没有被提交,则事务B仍然可以看到被修改的数据,因此会出现脏读现象。当事务A进行事务回滚时,则事务B此时看到的数据就和此前不一样的,两次看到的结果不一样,那么就会出现不可重复读现象。在READ-UNCOMMITTED隔离级别下,也会出现幻读的场景,即事务B在提交前和提交后,查看到的内容是不一致的。
下面用一个例子来说明一下READ-UNCOMMITTED隔离级别的特性:
首先设置隔离级别为READ-UNCOMMITTED,默认为REPEATABLE-READ。
mysql> set global tx_isoaltion="READ-UNCOMMITTED";
启动两个新的会话窗口(全局变量生效时间需要在新建会话上),并启动两个事务。这里及其后续所有的例子都以表teachers来说明,该表的内容如下:
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
启动事务A,并对表teachers进行修改操作
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
修改第4行的TID为100
mysql> update teachers set TID=100 where TID=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
启动事务B,并在事务B下查看teachers表的内容
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 100 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
从上面显示的内容可以看出,事务B可以看到事务A没有提交且被修改的数据,这就是所谓的脏读。
此时第4行的TID字段为100。
当事务A回滚时,此时事务A的内容为:
mysql> rollback;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
进行回滚操作时,即之前所有的修改都无效,此时表teachers的内容仍然不变。
在事务B进程下,再次查看表teachers的内容
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.01 sec)
因此,此时在事务B下,两次查看到第4行的TID是不一样的,因此会出现不可重复读的现象。
2、READ-COMMITED:读提交,只有事务在提交后才可以读到被修改的数据。READ-COMMITED解决了脏读的问题。在该隔离级别下,当前事务只能看到其他事务提交后的执行结果。该隔离级别支持不可重复读、幻读。大多数数据库的的默认隔离级别为Read-Committed,但是mysql不是的。
建议,当对事务不是特别严格的场景下,使用READ-COMMITTED这种隔离级别性能更好。
下面仍然以一个例子来说明READ-COMMITTED隔离级别的特性
首先仍然设置下隔离级别为READ-COMMITTED
mysql> set global tx_isolation='READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)
启动两个新的会话窗口,并启动两个事务。
启动事务A
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
修改表teachers中的第4行TID字段为200
mysql> update teachers set TID=200 where TID=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
修改后的结果
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 200 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
注意:此时事务A尚未提交,因此,在事务B中,是无法看到被修改的数据的。
启动事务B
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
在事务B进程下,查看表teachers的内容
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
结果显示的仍然是原来的内容,因为事务A还没有提交,因此在事务B下,看不到被修改的数据。
当事务A提交时
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
在事务B下,再次查看表teachers的内容
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 200 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
会发现,两次读到第4行TID的内容不一致,这就是所谓的不可重复现象。
3、Repeatable-Read:重复读。解决了不可重复读的问题,但是仍然会出现幻读现象。在该级别且当前事务没有提交的前提下,不管其他事务提交的数据如何被修改,每次查询的结果都是一样的。一旦当前事务提交,则查询的结果就是最新的结果。在该级别下,为了提高并发能力,需要借助MVCC(多版本并发控制)机制来完成。这是mysql的默认隔离级别。
下面以一个例子来说明REPEATABLE-READ隔离级别的特性
首选设置隔离级别为REPEATABLE-READ
mysql> SET GLOBAL TX_ISOLATION="REPEATABLE-READ";
Query OK, 0 rows affected (0.00 sec)
启动两个新的会话窗口,并启动两个事务。
启动事务A
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
修改表teachers中的第4行数据
mysql> update teachers set TID=300 where TID=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
修改后的内容为:
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 300 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
启动事务B
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)
查看表teacher的内容
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
此时显示仍然是表teachers的原有内容。
提交事务A
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
在事务B下,再次查看表teachers的内容
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
此时显示仍然还是表teachers的原有内容。
接着,提交事务B
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
最后,查看提交后teachers表中的数据
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 300 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.01 sec)
此处,当事务B提交后,就可以查看表teachers的最终数据了。
在REPEATABLE-READ隔离级别下,只有当相关联的所有事务提交后,才可以看到最终的数据。(这里的相关联的事务指的是跟该张表操作相关的事务)
4、SERIALIZABLE :可串行化,就是事务接着一个一个的串行执行,只有同一个实例下的其他事务结束后,才可以执行同一个实例下的另一个事务。这样就解决了幻读的问题。但是这种隔离级别的并发性不好,当某个事务没有提交时,其他事务仍然处于阻塞状态。SERIALIZABLE解决了幻读现象。
下面以一个例子来说明REPEATABLE-READ隔离级别的特性
首选设置隔离级别为SERIALIZABLE
mysql> SET GLOBAL TX_ISOLATION="SERIALIZABLE";
Query OK, 0 rows affected (0.00 sec)
启动两个新的会话窗口,并启动两个事务。
启动事务A
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
修改表teachers中的第4行数据
mysql> update teachers set TID=400 where TID=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
查看修改后的数据
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 400 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
此时事务A没有提交,那么如果在开启其他的事务时,其他事务是无法访问teachers表的。
启动事务B
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
查看表teachers的内容
mysql> select * from teachers;
执行查询操作后,你会发现此时事务B是处于阻塞状态的,只有当事务A提交后,则事务B才可以被执行。
提交事务A
mysql> commit;
Query OK, 0 rows affected (0.03 sec)
然后再在事务B下,查看表teachers的内容
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 400 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
当事务A提交后,此时事务B就可以发起查询操作了。由此看出,SERIALIZABLE隔离级别虽然隔离性能比较好,但是并发能力太差。
总结:mysql的四种默认隔离级别所出现的读取方式:
1、READ-COMMITTED:会出现脏读、不可重复读、幻读等现象。
2、READ-COMMITED:会出现不可重复现象、幻读。
3、ReEPEATABLE-READ:会出现幻读现象。
4、SERIALIZABLE:不会出现脏读、不可重复读、幻读等现象。
由此可见,隔离级别越高,所受到的干扰越小,消耗系统的资源越多。且最重要的是它们的并发性能越差。
mysql中事务隔离级别的设定
mysql>select @@tx_isolation; ##显示当前的隔离级别
mysql>set [global}session] tx_isolation='VALUE' ##用来设定隔离级别