Mysql帐户锁定策略_MySQL的锁策略

最新推荐文章于 2024-07-29 04:02:23 发布

Jezzy WANG

最新推荐文章于 2024-07-29 04:02:23 发布

阅读量771

点赞数

文章标签： Mysql帐户锁定策略

本文链接：https://blog.csdn.net/weixin_31758889/article/details/113589853

版权

锁用来保证数据并发访问的一致性、有效性

MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking)

BDB存储引擎采用的是页面锁(page-level locking)，但也支持表级锁

InnoDB存储引擎既支持行级锁(row-level locking)，也支持表级锁

MySQL这3种锁的特性可大致归纳如下：表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低

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

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

MYISAM存储引擎的锁机制

MyISAM存储引擎只支持表锁

查询表级锁争用情况

show status like 'table%';

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

| Variable_name | Value |

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

| Table_locks_immediate | 2979 |

| Table_locks_waited | 0 |

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

如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况

表级锁的锁模式

MySQL的表级锁有两种模式：表共享读锁(Table Read Lock)

表独占写锁(Table Write Lock)MyISAM在执行查询语句(SELECT)前，会自动给涉及的所有表加读锁，在执行更新操作(UPDATE、DELETE、INSERT等)前，会自动给涉及的表加写锁

简单来说就是：读锁可以被读操作共享,不影响读操作,但会影响写操作

写锁会影响所有的读写操作

并发插入

MyISAM表的读和写是串行的，但这是就总体而言的。在一定条件下，MyISAM表也支持查询和插入操作的并发进行 MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。当 concurrent_insert 设置为0时，不允许并发插入。

当 concurrent_insert 设置为1时，如果MyISAM表中没有空洞(即表的中间没有被删除的行)，MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。

当 concurrent_insert 设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录a. 可以利用MyISAM存储引擎的并发插入特性，来解决应用中对同一表查询和插入的锁争用。例如，将concurrent_insert系统变量设为2，总是允许并发插入； b. 同时，通过定期在系统空闲时段执行 OPTIMIZE TABLE语句来整理空间碎片，收回因删除记录而产生的中间空洞。

主动加表锁

Lock tables orders read local, order_detail read local;

Select sum(total) from orders;

Select sum(subtotal) from order_detail;

Unlock tables;上面的例子在LOCK TABLES时加了“local”选项，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录，

在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表

同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作

在自动加锁的情况下也基本如此，MyISAM总是一次获得SQL语句所需要的全部锁

这也正是MyISAM表不会出现死锁(Deadlock Free)的原因。

MyISAM的锁调度

由于MySQL认为写请求一般比读请求要重要，所以如果有读写请求同时进行的话，MYSQL将会优先执行写操作。这样MyISAM表在进行大量的更新操作时(特别是更新的字段中存在索引的情况下)，会造成查询操作很难获得读锁，从而导致查询阻塞。

我们可以通过一些设置来调节MyISAM的调度行为：通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利

通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。

INNODB存储引擎的锁机制

InnoDB与MyISAM的最大不同有两点：一是支持事务(TRANSACTION)；

二是采用了行级锁。

获取InnoDB行锁争用情况

mysql> show status like 'innodb_row_lock%';

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

| Variable_name | Value |

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

| InnoDB_row_lock_current_waits | 0 |

| InnoDB_row_lock_time | 0 |

| InnoDB_row_lock_time_avg | 0 |

| InnoDB_row_lock_time_max | 0 |

| InnoDB_row_lock_waits | 0 |

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

如果InnoDB_row_lock_waits 和 InnoDB_row_lock_time_avg的值比较高,说明锁争用比较严重

InnoDB行锁模式

行锁不影响读操作,只影响写操作update ,delete 会自动给涉及到的数据集加上排他锁

普通的select则不加任何锁

insert 会加什么锁呢? MySQL5.1.22之后的版本，出现了一个新的配置选项：innodb_autoinc_lock_mode，它是专门用来在使用auto_increment的情况下调整锁策略的，目前有三种选择：

innodb_autoinc_lock_mode = 0 (“traditional” lock mode) # 仍然是表锁

innodb_autoinc_lock_mode = 1 (“consecutive” lock mode) #默认方式, 只锁分配A_I的过程,可一次分配多个

innodb_autoinc_lock_mode = 2 (“interleaved” lock mode) # 只锁分配A_I的过程,来一个分配一个

主动加行锁

事务可以通过以下语句显式给记录集加共享锁或排他锁。共享锁(S)：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

排他锁(X)：SELECT * FROM table_name WHERE ... FOR UPDATE用SELECT … IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁

InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。 InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

乐观锁与悲观锁

在多用户环境中，在同一时间可能会有多个用户更新相同的记录，这会产生冲突。这就是著名的并发性问题。

典型的冲突有：丢失更新：一个事务的更新覆盖了其它事务的更新结果，就是所谓的更新丢失。例如：用户A把值从6改为2，用户B把值从2改为6，则用户A丢失了他的更新。

脏读：当一个事务读取其它完成一半事务的记录时，就会发生脏读取。例如：用户A,B看到的值都是6，用户B把值改为2，用户A读到的值仍为6。

为了解决这些并发带来的问题。我们需要引入并发控制机制。

并发控制机制悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。乐观锁不能解决脏读的问题。

乐观锁应用使用自增长的整数表示数据版本号。更新时检查版本号是否一致，比如数据库中数据版本为6，更新提交时version=6+1,使用该version值(=7)与数据库version+1(=7)作比较，如果相等，则可以更新，如果不等则有可能其他程序已更新该记录，所以返回错误。

使用时间戳来实现.基本思路与版本号一样

悲观锁应用