mysql锁与锁调度

本文介绍了MySQL中的表锁类型,包括表锁、行锁和页锁,重点讲解了MyISAM存储引擎的表级锁,如读锁和写锁的兼容性和调度策略。此外,还讨论了如何调节MyISAM的锁行为以减少锁冲突,并对比了InnoDB的行级锁和意向锁。最后,提到了何时考虑使用表锁及其注意事项。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

MySQL

· 表锁:开销小,加锁快;不会出现死锁;锁定力度大,发生锁冲突概率高,并发度最低

· 行锁:开销大,加锁慢;会出现死锁;锁定粒度小,发生锁冲突的概率低,并发度高

· 页锁:开销和加锁速度介于表锁和行锁之间;会出现死锁;锁定粒度介于表锁和行锁之间,并发度一般

MyISAM表锁

MyISAM存储引擎只支持表锁,

查询表级锁争用情况

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺:

[html] view plain copy

1. mysql> show status like 'table%';  

2. +-----------------------+-------+  

3. | Variable_name         | Value |  

4. +-----------------------+-------+  

5. | Table_locks_immediate | 2979  |  

6. | Table_locks_waited    | 0     |  

7. +-----------------------+-------+  

8. 2 rows in set (0.00 sec))  

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

MySQL表级锁的锁模式

MySQL的表级锁有两种模式:表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)。锁模式的兼容性如下表所示。

                                          MySQL中的表锁兼容性                

请求锁模式

         是否兼容

当前锁模式

None

读锁

写锁

读锁

写锁

可见,对MyISAM表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;对 MyISAM表的写操作,则会阻塞其他用户对同一表的读和写操作;MyISAM表的读操作与写操作之间,以及写操作之间是串行的!根据如下表所示的例子可以知道,当一个线程获得对一个表的写锁后,只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待,直到锁被释放为止。

                        MyISAM存储引擎的写阻塞读例子

session_1

session_2

获得表film_text的WRITE锁定

mysql> lock table film_text write;

Query OK, 0 rows affected (0.00 sec)

 

当前session对锁定表的查询、更新、插入操作都可以执行:

mysql> select film_id,title from film_text where film_id = 1001;

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

| film_id | title       |

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

| 1001    | Update Test |

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

1 row in set (0.00 sec)

mysql> insert into film_text (film_id,title) values(1003,'Test');

Query OK, 1 row affected (0.00 sec)

mysql> update film_text set title = 'Test' where film_id = 1001;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

其他session对锁定表的查询被阻塞,需要等待锁被释放:

mysql> select film_id,title from film_text where film_id = 1001;

等待

释放锁:

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

等待

 

Session2获得锁,查询返回:

mysql> select film_id,title from film_text where film_id = 1001;

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

| film_id | title |

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

| 1001    | Test  |

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

1 row in set (57.59 sec)

 

 

MyISAM的锁调度

前面讲过,MyISAM存储引擎的读锁和写锁是互斥的,读写操作是串行的。那么,一个进程请求某个 MyISAM表的读锁,同时另一个进程也请求同一表的写锁,MySQL如何处理呢?答案是写进程先获得锁。不仅如此,即使读请求先到锁等待队列,写请求后到,写锁也会插到读锁请求之前!这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因,因为,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。这种情况有时可能会变得非常糟糕!幸好我们可以通过一些设置来调节MyISAM 的调度行为。

· 通过指定启动参数low-priority-updates,使MyISAM引擎默认给予读请求以优先的权利。

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

· 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性,降低该语句的优先级。

虽然上面3种方法都是要么更新优先,要么查询优先的方法,但还是可以用其来解决查询相对重要的应用(如用户登录系统)中,读锁等待严重的问题。

另外,MySQL也提供了一种折中的办法来调节读写冲突,即给系统参数max_write_lock_count设置一个合适的值,当一个表的读锁达到这个值后,MySQL就暂时将写请求的优先级降低,给读进程一定获得锁的机会。

上面已经讨论了写优先调度机制带来的问题和解决办法。这里还要强调一点:一些需要长时间运行的查询操作,也会使写进程“饿死”!因此,应用中应尽量避免出现长时间运行的查询操作,不要总想用一条SELECT语句来解决问题,因为这种看似巧妙的SQL语句,往往比较复杂,执行时间较长,在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”,使每一步查询都能在较短时间完成,从而减少锁冲突。如果复杂查询不可避免,应尽量安排在数据库空闲时段执行,比如一些定期统计可以安排在夜间执行。

 

 

InnoDB锁问题

InnoDB与MyISAM的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁。行级锁与表级锁本来就有许多不同之处,另外,事务的引入也带来了一些新问题。

获取InnoDB行锁争用情况    

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况:

如果发现锁争用比较严重,如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高,还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等,并分析锁争用的原因。

nnoDB实现了以下两种类型的行锁。

· 共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。

· 排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。另外,为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁。

· 意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。

· 意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。

上述锁模式的兼容情况具体如下表所示。

                                         InnoDB行锁模式兼容性列表

请求锁模式

   是否兼容

当前锁模式

X

IX

S

IS

X

冲突

冲突

冲突

冲突

IX

冲突

兼容

冲突

兼容

S

冲突

冲突

兼容

兼容

IS

冲突

兼容

兼容

兼容

如果一个事务请求的锁模式与当前的锁兼容,InnoDB就将请求的锁授予该事务;反之,如果两者不兼容,该事务就要等待锁释放。

意向锁是InnoDB自动加的,不需用户干预。对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);对于普通SELECT语句,InnoDB不会加任何锁;事务可以通过以下语句显示给记录集加共享锁或排他锁。

· 共享锁(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表的理由。但在个别特殊事务中,也可以考虑使用表级锁。

· 第一种情况是:事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,不仅这个事务执行效率低,而且可能造成其他事务长时间锁等待和锁冲突,这种情况下可以考虑使用表锁来提高该事务的执行速度。

· 第二种情况是:事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表,从而避免死锁、减少数据库因事务回滚带来的开销。

当然,应用中这两种事务不能太多,否则,就应该考虑使用MyISAM表了。

InnoDB下,使用表锁要注意以下两点。

1)使用LOCK TABLES虽然可以给InnoDB加表级锁,但必须说明的是,表锁不是由InnoDB存储引擎层管理的,而是由其上一层──MySQL Server负责的,仅当autocommit=0、innodb_table_locks=1(默认设置)时,InnoDB层才能知道MySQL加的表锁,MySQL Server也才能感知InnoDB加的行锁,这种情况下,InnoDB才能自动识别涉及表级锁的死锁;否则,InnoDB将无法自动检测并处理这种死锁。有关死锁,下一小节还会继续讨论。

2)在用 LOCK TABLES对InnoDB表加锁时要注意,要将AUTOCOMMIT设为0,否则MySQL不会给表加锁;事务结束前,不要用UNLOCK TABLES释放表锁,因为UNLOCK TABLES会隐含地提交事务;COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁,必须用UNLOCK TABLES释放表锁


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值