mysql锁特性_MySQL（八）之锁和事务特性

最新推荐文章于 2024-05-25 09:21:30 发布

weixin_39932947

最新推荐文章于 2024-05-25 09:21:30 发布

阅读量288

点赞数

文章标签： mysql锁特性

本文链接：https://blog.csdn.net/weixin_39932947/article/details/113457443

版权

一、MySQL锁

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking)；BDB存储引擎采用的是页面锁(page-level locking)，但也支持表级锁；InnoDB存储引擎既支持行级锁(row-level locking)，也支持表级锁，但默认情况下是采用行级锁。

1、MySQL这3种锁的特性可大致归纳如下。

开销、加锁速度、死锁、粒度、并发性能表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理(OLTP)系统。

2、执行操作时施加的锁的模式

读锁：用户在读的时候施加的锁，为防止别人修改，但是用户可以读，还被称为共享锁。

写锁：也称为独占锁，排它锁。其他用户不能读，不能写。

3、锁的实现位置

MySQL锁：可以手动使用，可以使用显示锁。

存储引擎锁：自动进行的(隐式锁)。

4、显示锁(只能使用在表级锁)

锁添加于解除的语句：

LOCK TABLES ：施加锁

UNLOCK TABLES：解锁

语法： LOCK TABLES

tbl_name lock_type

[, tbl_name lock_type] ...

READ | WRITE：锁的类型

读锁举例：MariaDB [hellodb]> LOCK TABLES classes READ;

Query OK, 0 rows affected (0.00 sec)

在打开一个终端，可以在classes表中进行查询，但若想写入数据需要手动释放锁。MariaDB [hellodb]> SELECT * FROM classes;

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

| ClassID | Class | NumOfStu |

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

| 1 | Shaolin Pai | 10 |

| 2 | Emei Pai | 7 |

| 3 | QingCheng Pai | 11 |

| 4 | Wudang Pai | 12 |

| 5 | Riyue Shenjiao | 31 |

| 6 | Lianshan Pai | 27 |

| 7 | Ming Jiao | 27 |

| 8 | Xiaoyao Pai | 15 |

| 9 | Jiuyin Zhenjing | 22 |

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

9 rows in set (0.00 sec)

MariaDB [hellodb]> INSERT INTO classes VALUE(10,'Jiuyang Shengong',23);

Query OK, 1 row affected (28.72 sec)

上面插入数据的操作在执行时会阻塞掉，除非在执行解锁才能执行插入数据。MariaDB [hellodb]> UNLOCK TABLES;

Query OK, 0 rows affected (0.00 sec)

写锁举例：MariaDB [hellodb]> LOCK TABLES classes WRITE;

Query OK, 0 rows affected (0.00 sec)

在打开一个终端，发现既不能执行写操作有不能执行查询操作，除非解锁。MariaDB [hellodb]> SELECT * FROM classes;

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

| ClassID | Class | NumOfStu |

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

| 1 | Shaolin Pai | 10 |

| 2 | Emei Pai | 7 |

| 3 | QingCheng Pai | 11 |

| 4 | Wudang Pai | 12 |

| 5 | Riyue Shenjiao | 31 |

| 6 | Lianshan Pai | 27 |

| 7 | Ming Jiao | 27 |

| 8 | Xiaoyao Pai | 15 |

| 9 | Jiuyin Zhenjing | 22 |

| 10 | Jiuyang Shengong | 23 |

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

10 rows in set (5.66 sec)

MariaDB [hellodb]> UNLOCK TABLES;

Query OK, 0 rows affected (0.00 sec)

注意：我们在日常进行做备份操作时，才需要手动施加读锁，避免出现数据不一致情况。一般情况是不会手动执行锁操作的。

5、行级锁

InnoDB存储引擎也支持另外一种显示锁(锁定挑选出的部分行，支持行级锁)

语法：SELECT ....LOCK IN SHARE MODE; //读锁

SELECT .... FOR UPDATE; //写锁

6、事务：Transaction

概念：事务就是一组原子性的查询语句，也即将多个查询当作一个独立的工作单元。

ACID测试：能够满足ACID测试就表示其支持事务，或兼容事务。A：Atomicity，原子性，都执行或者都不执行。

C：Consistency，一致性，从一个一致性状态转到另外一个一致性状态。

I：Isolaction，隔离性，一个事务的所有修改操作在提交前对其他事务时不可见的。

D: Durability, 持久性，一旦事务得到提交，其所做的修改会永久有效。

7、隔离级别

在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别。我们的数据库锁，也是为了构建这些隔离级别存在的。

未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据，数据库一般都不会用。

提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)。

可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读。

串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。

跟事务相关的常用命令：MariaDB [hellodb]>start transaction：启动事务

MariaDB [hellodb]>commit：事务提交

MariaDB [hellodb]>rollback：事务回滚

MariaDB [hellodb]>SAVEPOINT identifier：控制回滚的位置

MariaDB [hellodb]>ROLLBACK [WORK] TO [SAVEPOINT] identifier

注意：MyISAM存储引擎不支持事务。

应用举例：MariaDB [hellodb]> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> SELECT * FROM classes;

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

| ClassID | Class | NumOfStu |

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

| 1 | Shaolin Pai | 10 |

| 2 | Emei Pai | 7 |

| 3 | QingCheng Pai | 11 |

| 4 | Wudang Pai | 12 |

| 5 | Riyue Shenjiao | 31 |

| 6 | Lianshan Pai | 27 |

| 7 | Ming Jiao | 27 |

| 8 | Xiaoyao Pai | 15 |

| 9 | Jiuyin Zhenjing | 22 |

| 10 | Jiuyang Shengong | 23 |

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

10 rows in set (0.00 sec)

MariaDB [hellodb]> DELETE FROM classes WHERE ClassID=10;

Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> SELECT * FROM classes;

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

| ClassID | Class | NumOfStu |

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

| 1 | Shaolin Pai | 10 |

| 2 | Emei Pai | 7 |

| 3 | QingCheng Pai | 11 |

| 4 | Wudang Pai | 12 |

| 5 | Riyue Shenjiao | 31 |

| 6 | Lianshan Pai | 27 |

| 7 | Ming Jiao | 27 |

| 8 | Xiaoyao Pai | 15 |

| 9 | Jiuyin Zhenjing | 22 |

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

9 rows in set (0.00 sec)

MariaDB [hellodb]> ROLLBACK;

Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> SELECT * FROM classes;

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

| ClassID | Class | NumOfStu |

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

| 1 | Shaolin Pai | 10 |

| 2 | Emei Pai | 7 |

| 3 | QingCheng Pai | 11 |

| 4 | Wudang Pai | 12 |

| 5 | Riyue Shenjiao | 31 |

| 6 | Lianshan Pai | 27 |

| 7 | Ming Jiao | 27 |

| 8 | Xiaoyao Pai | 15 |

| 9 | Jiuyin Zhenjing | 22 |

| 10 | Jiuyang Shengong | 23 |

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

10 rows in set (0.00 sec)

通过上面可以发现一旦事务没有提交就能执行回滚，而删除并提交后回滚就不能起作用了：MariaDB [hellodb]> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> DELETE FROM classes WHERE ClassID=10;

Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> COMMIT;

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> SELECT * FROM classes;

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

| ClassID | Class | NumOfStu |

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

| 1 | Shaolin Pai | 10 |

| 2 | Emei Pai | 7 |

| 3 | QingCheng Pai | 11 |

| 4 | Wudang Pai | 12 |

| 5 | Riyue Shenjiao | 31 |

| 6 | Lianshan Pai | 27 |

| 7 | Ming Jiao | 27 |

| 8 | Xiaoyao Pai | 15 |

| 9 | Jiuyin Zhenjing | 22 |

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

9 rows in set (0.00 sec)

而使用COMMIT命令是回滚所有的，而若想回滚到指定位置需要使用SAVEPOINT命令：MariaDB [hellodb]> START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> DELETE FROM classes WHERE ClassID=9;

Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> SAVEPOINT a;

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> DELETE FROM classes WHERE ClassID=5;

Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> SAVEPOINT b;

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> DELETE FROM classes WHERE ClassID=6;

Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> SAVEPOINT c;

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> SELECT * FROM classes;

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

| ClassID | Class | NumOfStu |

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

| 1 | Shaolin Pai | 10 |

| 2 | Emei Pai | 7 |

| 3 | QingCheng Pai | 11 |

| 4 | Wudang Pai | 12 |

| 7 | Ming Jiao | 27 |

| 8 | Xiaoyao Pai | 15 |

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

6 rows in set (0.00 sec)

MariaDB [hellodb]> ROLLBACK TO b;

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> SELECT * FROM classes;

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

| ClassID | Class | NumOfStu |

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

| 1 | Shaolin Pai | 10 |

| 2 | Emei Pai | 7 |

| 3 | QingCheng Pai | 11 |

| 4 | Wudang Pai | 12 |

| 6 | Lianshan Pai | 27 |

| 7 | Ming Jiao | 27 |

| 8 | Xiaoyao Pai | 15 |

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

7 rows in set (0.00 sec)

8、关于事务的一些系统参数

如果没有显式启动事务，每个语句都会当作一个默认的事务，其执行完成会被自动提交。影响一定的IO性能。

查看自动提交功能是否启用：MariaDB [hellodb]> SELECT @@GLOBAL.autocommit;

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

| @@GLOBAL.autocommit |

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

| 1 |

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

1 row in set (0.00 sec)

关闭此功能：MariaDB [hellodb]> SET GLOBAL autocommit = 0;

Query OK, 0 rows affected (0.00 sec)

注意：关闭自动提交，请记得一定要手动启动事务，还要得手动提交事务！

9、mysql的事务隔离级别

查看mysql的事务隔离级别：MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE '%iso%';

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

| Variable_name | Value |

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

| tx_isolation | REPEATABLE-READ |

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

1 row in set (0.00 sec)

还可以使用下面的语句：

MariaDB [hellodb]> SELECT @@GLOBAL.tx_isolation;

建议：对事物要求不特别严格的场景下，可以使用读提交，其性能比可重复读好点。

weixin_39932947

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql锁特性_MySQL（八）之锁和事务特性

一、MySQL锁相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking)；BDB存储引擎采用的是页面锁(page-level locking)，但也支持表级锁；InnoDB存储引擎既支持行级锁(row-level locking)，也支持表级锁，但默认情况下是采用...
复制链接

扫一扫