用sql语句,怎么解决mysql数据库死锁

用sql语句,怎么解决mysql数据库死锁,第1张

MySQL死锁问题的相关知识是本文我们主要要介绍的内容,接下来我们就来一一介绍这部分内容,希望能够对您有所帮助。

1、MySQL常用存储引擎的锁机制

MyISAM和MEMORY采用表级锁(table-level locking)

BDB采用页面锁(page-level locking)或表级锁,默认为页面锁

InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

2、各种锁特点

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

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

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

3、各种锁的适用场景

表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用

行级锁则更适合于有大量按索引条件并发更新数据,同时又有并发查询的应用,如一些在线事务处理系统

4、死锁

是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。

表级锁不会产生死锁。所以解决死锁主要还是针对于最常用的InnoDB。

5、死锁举例分析

在MySQL中,行级锁并不是直接锁记录,而是锁索引。索引分为主键索引和非主键索引两种,如果一条sql语句 *** 作了主键索引,MySQL就会锁定这条主键索引;如果一条语句 *** 作了非主键索引,MySQL会先锁定该非主键索引,再锁定相关的主键索引。

在UPDATE、DELETE *** 作时,MySQL不仅锁定WHERE条件扫描过的所有索引记录,而且会锁定相邻的键值,即所谓的next-key locking。

例如,一个表db。tab_test,结构如下:

id:主键;

state:状态;

time:时间;

索引:idx_1(state,time)

出现死锁日志如下:

(1) TRANSACTION:

TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OSthread id 278546 starting index read

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 320

MySQL thread id 83, query id 162348740 dcnet03 dcnet Searching rows for update

update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute) (任务1的sql语句)

(1) WAITING FOR THIS LOCK TO BE GRANTED: (任务1等待的索引记录)

RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833455 _mode X locks rec but not gap waiting

Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0

0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc xxxcom/;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;

(2) TRANSACTION:

TRANSACTION 0 677833454, ACTIVE 0 sec, process no 11397, OS thread id 344086 updating or deleting, thread declared inside InnoDB 499

mysql tables in use 1, locked 1

3 lock struct(s), heap size 320, undo log entries 1

MySQL thread id 84, query id 162348739 dcnet03 dcnet Updating update tab_test set state=1067,time=now () where id in (9921180) (任务2的sql语句)

(2) HOLDS THE LOCK(S): (任务2已获得的锁)

RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833454 lock_mode X locks rec but not gap

Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0

0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc uploadfirecom/handphp;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;

(2) WAITING FOR THIS LOCK TO BE GRANTED: (任务2等待的锁)

RECORD LOCKS space id 0 page no 843102 n bits 600 index `idx_1` of table `db/tab_test` trx id 0 677833454 lock_mode X locks rec but not gap waiting

Record lock, heap no 395 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 8; hex 8000000000000425; asc %;; 1: len 8; hex 800012412c66d29c; asc A,f ;; 2: len 8; hex 800000000097629c; asc b ;;

WE ROLL BACK TRANSACTION (1)

(回滚了任务1,以解除死锁)

原因分析:

当“update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute)”执行时,MySQL会使用idx_1索引,因此首先锁定相关的索引记录,因为idx_1是非主键索引,为执行该语句,MySQL还会锁定主键索引。

假设“update tab_test set state=1067,time=now () where id in (9921180)”几乎同时执行时,本语句首先锁定主键索引,由于需要更新state的值,所以还需要锁定idx_1的某些索引记录。

这样第一条语句锁定了idx_1的记录,等待主键索引,而第二条语句则锁定了主键索引记录,而等待idx_1的记录,这样死锁就产生了。

6、解决办法

拆分第一条sql,先查出符合条件的主键值,再按照主键更新记录:

select id from tab_test where state=1061 and time < date_sub(now(), INTERVAL 30 minute);

update tab_test state=1064,time=now() where id in();

(1)

HOLDLOCK:

在该表上保持共享锁,直到整个事务结束,而不是在语句执行完立即释放所添加的锁。

(2)

NOLOCK:不添加共享锁和排它锁,当这个选项生效后,可能读到未提交读的数据或“脏数据”,这个选项仅仅应用于SELECT语句。

(3)

PAGLOCK:指定添加页锁(否则通常可能添加表锁)。

(4)

READCOMMITTED用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况下,SQL

Server

2000

在此隔离级别上 *** 作。

(5)

READPAST:

跳过已经加锁的数据行,这个选项将使事务读取数据时跳过那些已经被其他事务锁定的数据行,而不是阻塞直到其他事务释放锁,

READPAST仅仅应用于READ

COMMITTED隔离性级别下事务 *** 作中的SELECT语句 *** 作。

(6)

READUNCOMMITTED:等同于NOLOCK。

(7)

REPEATABLEREAD:设置事务为可重复读隔离性级别。

(8)

ROWLOCK:使用行级锁,而不使用粒度更粗的页级锁和表级锁。

(9)

SERIALIZABLE:用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。等同于

HOLDLOCK。

(10)

TABLOCK:指定使用表级锁,而不是使用行级或页面级的锁,SQL

Server在该语句执行完后释放这个锁,而如果同时指定了(1)

HOLDLOCK:

在该表上保持共享锁,直到整个事务结束,而不是在语句执行完立即释放所添加的锁。

(2)

NOLOCK:不添加共享锁和排它锁,当这个选项生效后,可能读到未提交读的数据或“脏数据”,这个选项仅仅应用于SELECT语句。

(3)

PAGLOCK:指定添加页锁(否则通常可能添加表锁)。

(4)

READCOMMITTED用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况下,SQL

Server

2000

在此隔离级别上 *** 作。

(5)

READPAST:

跳过已经加锁的数据行,这个选项将使事务读取数据时跳过那些已经被其他事务锁定的数据行,而不是阻塞直到其他事务释放锁,

READPAST仅仅应用于READ

COMMITTED隔离性级别下事务 *** 作中的SELECT语句 *** 作。

(6)

READUNCOMMITTED:等同于NOLOCK。

(7)

REPEATABLEREAD:设置事务为可重复读隔离性级别。

(8)

ROWLOCK:使用行级锁,而不使用粒度更粗的页级锁和表级锁。

(9)

SERIALIZABLE:用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。等同于

HOLDLOCK。

(10)

TABLOCK:指定使用表级锁,而不是使用行级或页面级的锁,SQL

Server在该语句执行完后释放这个锁,而如果同时指定了HOLDLOCK,该锁一直保持到这个事务结束。

(11)

TABLOCKX:指定在表上使用排它锁,这个锁可以阻止其他事务读或更新这个表的数据,直到这个语句或整个事务结束。

(12)

UPDLOCK

:指定在

读表中数据时设置更新

锁(update

lock)而不是设置共享锁,该锁一直保持到这个语句或整个事务结束,使用UPDLOCK的作用是允许用户先读取数据(而且不阻塞其他用户读数据),并且保证在后来再更新数据时,这一段时间内这些数据没有被其他用户修改。

ix是意向锁。

意向锁与其说是锁,倒不如说更像一个指示器。在SQL Server中,资源是有层次的,一个表中可以包含N个页,而一个页中可以包含N个行。当我们在某一个行中加了锁时。可以理解成包含这个行的页,和表的一部分已经被锁定。当另一个查询需要锁定页或是表时,再一行行去看这个页和表中所包含的数据是否被锁定就有点太痛苦了。因此SQL Server锁定一个粒度比较低的资源时,会在其父资源上加上意向锁,告诉其他查询这个资源的某一部分已经上锁。比如,当我们更新一个表中的某一行时,其所在的页和表都会获得意向排他锁,如图所示。

详细步骤如下:

1、点击新建查询按钮,打开SQL命令编辑框,对数据库表的 *** 作以及维护都可以通过编辑SQL命令实现。

2、在编辑框内编辑创建数据库表的代码,确认代码无误后,单击执行按钮,创建数据表。

3、创建数据表的源代码如下:

use test go

if exists(select name from systables where name='Student')

drop table Student go

create table Student

(sname nchar(10) primary key,

sex nchar(2) not null,

bir datetime)

数据库管理系统,database management system,简称dbms,是一种 *** 纵和管理数据库的大型软件,用于建立、使用和维护数据库。用户通过dbms访问数据库中的数据,数据库管理员也通过dbms进行数据库的维护工作。它可使多个应用程序和用户用不同的方法在同时或不同时刻去建立,修改和询问数据库。

提供数据定义语言(ddl)。用它书写的数据库模式被翻译为内部表示。数据库的逻辑结构、完整性约束和物理储,存结构保存在内部的数据字典中。数据库的各种数据 *** 作(如查找、修改、插入和删除等)和数据库的维护管理都是以数据库模式为依据的。

它所锁定的资源,其他事务不能读取也不能修改。独占锁不能和其他锁兼容。(4) 架构锁结构锁分为结构修改锁(Sch-M)和结构稳定锁(Sch-S)。执行表定义语言 *** 作时,SQL Server采用Sch-M锁,编译查询时,SQL Server采用Sch-S锁。 (5) 意向锁意向锁说明SQL Server有在资源的低层获得共享锁或独占锁的意向。(6) 批量修改锁批量复制数据时使用批量修改锁134 SQL Server锁类型 (1) HOLDLOCK: 在该表上保持共享锁,直到整个事务结束,而不是在语句执行完立即释放所添加的锁。 (2) NOLOCK:不添加共享锁和排它锁,当这个选项生效后,可能读到未提交读的数据或“脏数据”,这个选项仅仅应用于SELECT语句。 (3) PAGLOCK:指定添加页锁(否则通常可能添加表锁)。 (4) READCOMMITTED用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况下,SQL Server 2000 在此隔离级别上 *** 作。(5) READPAST: 跳过已经加锁的数据行,这个选项将使事务读取数据时跳过那些已经被其他事务锁定的数据行,而不是阻塞直到其他事务释放锁, READPAST仅仅应用于READ COMMITTED隔离性级别下事务 *** 作中的SELECT语句 *** 作。 (6) READUNCOMMITTED:等同于NOLOCK。 (7) REPEATABLEREAD:设置事务为可重复读隔离性级别。 (8) ROWLOCK:使用行级锁,而不使用粒度更粗的页级锁和表级锁。

以上就是关于用sql语句,怎么解决mysql数据库死锁全部的内容,包括:用sql语句,怎么解决mysql数据库死锁、如何解除sql server数据库数据被锁定、sql数据库里锁是什么等相关内容解答,如果想了解更多相关内容,可以关注我们,你们的支持是我们更新的动力!

欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/sjk/10199263.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023-05-06
下一篇 2023-05-06

发表评论

登录后才能评论

评论列表(0条)

保存