如何对MySQL数据库表进行锁定

for

服务器由两种表的锁定方法：

mysql查看锁 mysql查看锁表语句

在我们系统中有一张表它的查询概率非常高。 近有个需求，需要对这个表增加一个字段 ，然而在增加字段的时候发现系统中有多个业务出现了超时作，那么这个是什么原因导致的呢？经过查阅资料发现是数据库的 MDL锁+事务导致 的。

1.内部锁定

语法：

锁定表：LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},…]

LOCK TABLES为当前线程锁定表。UNLOCK TABLES释放被当前线程持有的任何锁。当线程发出另外一个LOCK TABLES时，或当服务器的连接被关闭时，当前线程锁定的所有表自动被解锁。

如果一个线程获得在一个表上的一个READ锁，该线程(和所有其他线程)只能从表中读。如果一个线程获得一个表上的一个WRITE锁，那么只有持锁的线程READ或WRITE表，其他线程被阻止。

每个线程等待(没有超时)直到它获得它请求的所有锁。

WRITE锁通常比READ锁有更高的优先级，以确保更改尽快被处理。这意味着，如果一个线程获得READ锁，并且然后另外一个线程请求一个WRITE锁, 随后的READ锁请求将等待直到WRITE线程得到了锁并且释放了它。

显然对于检查，你只需要获得读锁。再者钟情跨下，只能读取表，但不能修改它，因此他也允许其它客户机读取表。对于修复，你必须获得些所以防止任何客户机在你对表进行作时修改它。

服务器还可以使用外部锁定（文件级锁）来防止其它程序在服务器使用表时修改文件。通常，在表的检查作中服务器将外部锁定与myisamchk或isamchk作合使用。但是，外部锁定在某些系统中是禁用的，因为他不能可靠的进行工作。对运行myisamchk或isamchk所选择的过程取决于服务器是否能使用外部锁定。如果不使用，则必修使用内部锁定协议。

如果服务器用--skip-locking选项运行，则外部锁定禁用。该选项在某些系统中是缺省的，如Linux。可以通过运行mysqladmin variables命令确定服务器是否能够使用外部锁定。检查skip_locking变量的值并按以下方法进行：

◆如果skip_locking为off，则外部锁定有效您可以继续并运行人和一个实用程序来检查表。服务器和实用程序将合作对表进行访问。但是，运行任何一个实用程序之前，应该使用mysqladmin

flush-tables。为了修复表，应该使用表的修复锁定协议。

◆如果skip_locaking为on，则禁用外部锁定，所以在myisamchk或isamchk检查修复表示服务器并不知道，关闭服务器。如果坚持是服务器保持开启状态，月确保在您使用此表示没有客户机来访问它。必须使用卡的锁定协议告诉服务器是该表不被其他客户机访问。

检查表的锁定协议

本节只介绍如果使用表的内部锁定。对于检查表的锁定协议，此过程只针对表的检查，不针对表的修复。

1.调用mysql发布下列语句：

$mysql –u root –p db_namemysql>LOCK TABLE tbl_name READ;mysql>FLUSH TABLES;

该锁防止其它客户机在检查时写入该表和修改该表。FLUSH语句导致服务器关闭表的文件，它将刷新仍在告诉缓存中的任何为写入的改变。

2.执行检查过程

$myisamchk tbl_name$ isamchk tbl_name

mysql>UNLOCK TABLES;

如如果先执行事务二，在执行事务三，则是可以成功的，因为alter数据ddl语句，和事物无关。果myisamchk或isamchk指出发现该表的问题，将需要执行表的修复。

修复表的锁定协议

这里只介绍如果使用表的内部锁定。修复表的锁定过程类似于检查表的锁定过程，但有两个区别。，你必须得到写锁而非读锁。由于你需要修改表，因此根本不允许客户机对其进行访问。第二，必须在执行修复之后发布FLUSH

TABLE语句，因为myisamchk和isamchk建立的新的索引文件，除非再次刷新改表的高速缓存，否则服务器不会注意到这个改变。本例同样适合优化表的过程。

1.调用mysql发布下列语句：

$mysql –u root –p db_namemysql>LOCK TABLE tbl_name WRITE;mysql>FLUSH TABLES;

2.做数据表的拷贝，然后运行myisamchk和isamchk：

$cp tbl_name. /some/other/dir$myisamchk --recover tbl_name$ isamchk --recover tbl_name

--recover选项只是针对安装而设置的。这些特殊选项的选择将取决与你执行修复的类型。

3.再次刷新高速缓存，并释放表锁：

mysql>FLUSH TABLES;mysql>UNLOCK TABLES;

mysql数据库锁MDL锁的解释

$myisamchk tbl_name

MDL锁属于表级别的元数据锁。 表级别锁分为数据锁和元数据锁，通常我们说的加锁一般指的是加的数据锁。跟数据锁一样，元数据锁也分读锁和读写锁。

MDL不需要显示使用，在进行表作时会自动加上 。当对表进行增删改查时，会自动加上MDL读锁；当要对表进行加减字段的结构修改时，会自动加上MDL写锁。

MDL锁的存在，其实是为了保证数据的一致性。 想象一下，假如没有MDL锁，一个查询在遍历表数据的过程中，另外一个线程执行了ALTER TABLE t DELETE COLUMN 'col_1'把col_1这一列删掉了，那查询结果就乱了，结果中是否应该有这一列数据？

：如上图所示，此时的update 语句可以执行成功，并没有被阻塞。说明select 和update 是不冲突的，他们的锁是兼容的。表示正常往下执行

解释：

步骤 1 2 正常执行。执行步骤2 时，会申请表customer的MDL的 SHARED_READ 锁。

步骤3 会卡住， 因为此时会申请表customer的MDL的EXCLUSIVE锁，但是事物一的事物没有提交，此时是无法相关文章：申请到EXCLUSIVE锁，因为它们是互斥的。

步骤4 也会卡住，因为EXCLUSIVE锁和SHARE_READ锁是互斥的， 且EXCLUSIVE锁的优先级更高 ，所以步骤4 也会卡住。

步骤5 事物提交，释放表的SHARE_READ锁，之后就可以执行6 和7 的作了。

因此在我们开发的过程中，需要避免大的事务作，防止占有锁的时间过长。

怎样MySQL数据库表进行锁定

通过上图我们可以发现，course 表加上了shared_read锁。

锁定表的方法 防止客户机的内部锁定可以避免客户机的请求相互干扰——例如，避免客户机的SELECT查询被另一个客户机的UPDATE查询所干扰。也可以利用内部锁定机制防止服务器在利用myisamchk或isaiostat -x 2mchk检查或修复表时对表的访问。请求互相干扰或者服务器与维护程序相互干扰的方法主要有多种。如果你关闭数据库，就可以保证服务器和myisamchk和 isamchk之间没有交互作用。但是停止服务器的运行并不是一个好注意，因为这样做会使得没有故障的数据库和表也不可用

如何对MySQL数据库表进行锁定

3.释放表锁

相关文章： 如何进行MySQL数据库表的故障检测 如何修复MySQL数据库表 锁定表的方法 防止客户机的请求互相干扰或者服务器与维护程序相互干扰的方法主要有多种。如果你关闭数据库，就可以保证服务器和myisamchk和isamchk之间没有交互作用。但是停止服务器的运

如何进行MySQL数据库表的故障检测

如何修复MySQL数据库表

锁定表的方法

防止客户机的请求互相干扰或者服务器与维护程序相互干扰的方法主要有多种。如果你关闭数据库，就可以保证服务器和myisamchk和isamchk之间没有交互作用。但是停止服务器的运行并不是一个好注意，因为这样做会使得没有故障的数据库和表也不可用。本节主要讨论的过程，是避免服务器和myisamchk或isamchk之间的交互作用。实现这种功能的方法是对表进行锁定。

服务器由两种表的锁定方法：

1.内部锁定

语法：

锁定表：LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},?]

LOCK TABLES为当前线程锁定表。UNLOCK TABLES释放被当前线程持有的任何锁。当线程发出另外一个LOCK TABLES时，或当服务器的连接被关闭时，当前线程锁定的所有表自动被解锁。

如果一个线程获得在一个表上的一个READ锁，该线程(和所有其他线程)只能从表中读。如果一个线程获得一个表上的一个WRITE锁，那么只有持锁的线程READ或WRITE表，其他线程被阻止。

每个线程等待(没有超时)直到它获得它请求的所有锁。

WRITE锁通常比READ锁有更高的优先级，以确保更改尽快被处理。这意味着，如果一个线程获得READ锁，并且然后另外一个线程请求一个WRITE锁, 随后的READ锁请求将等待直到WRITE线程得到了锁并且释放了它。

显然对于检查，你只需要获得读锁。再者钟情跨下，只能读取表，但不能修改它，因此他也允许其它客户机读取表。对于修复，你必须获得些所以防止任何客户机在你对表进行作时修改它。

服务器还可以使用外部锁定（文件级锁）来防止其它程序在服务器使用表时修改文件。通常，在表的检查作中服务器将外部锁定与myisamchk或isamchk作合使用。但是，外部锁定在某些系统中是禁用的，因为他不能可靠的进行工作。对运行myisamchk或isamchk所选择的过程取决于服务器是否能使用外部锁定。如果不使用，则必修使用内部锁定协议。

如果服务器用--skip-locking选项运行，则外部锁定禁用。该选项在某些系统中是缺省的，如Linux。可以通过运行mysqladmin variables命令确定服务器是否能够使用外部锁定。检查skip_locking变量的值name='Mouse'并按以下方法进行：

◆ 如果skip_locking为off，则外部锁定有效您可以继续并运行人和一个实用程序来检查表。服务器和实用程序将合作对表进行访问。但是，运行任何一个实用程序之前，应该使用mysqladmin flush-tables。为了修复表，应该使用表的修复锁定协议。

◆ 如果skip_locaking为on，则禁用外部锁定，所以在myisamchk或isamchk检查修复表示服务器并不知道，关闭服务器。如果坚持是服务器保持开启状态，月确保在您使用此表示没有客户机来访问它。必须使用卡的锁定协议告诉服务器是该表不被其他客户机访问。

检查表的锁定协议

本节只介绍如果使用表的内部锁定。对于检查表的锁定协议，此过程只针对表的检查，不针对表的修复。

1.调用mysql发布下列语句：

$mysql _u root _p db_name

mysql>LOCK TABLE tbl_name READ;

该锁防止其它客户机在检查时写入该表和修改该表。FLUSH语句导致服务器关闭表的文件，它将刷新仍在告诉缓存中的任何为写入的改变。

2.执行检查过程

$ isamchk tbl_name

mysql>UNLOCK TABLES;

如果myisamchk或isamchk指出发现该表的问题，将需要执行表的修复。

修复表的锁定协议

这里只介绍如果使用表的内部锁定。修复表的锁定过程类似于检查表的锁定过程，但有两个区别。，你必须得到写锁而非读锁。由于你需要修改表，因此根本不允许客户机对其进行访问。第二，必须在执行修复之后发布FLUSH TABLE语句，因为myisamchk和isamchk建立的新的索引文件，除非再次刷新改表的高速缓存，否则服务器不会注意到这个改变。本例同样适合优化表的过程。

1.调用mysql发布下列语句：

$mysql _u root _p db_name

mysql>LOCK TABLE tbl_name WRITE;

2.做数据表的拷贝，然后运行myisamchk和isamchk：

$cp tbl_name. /some/other/dir

$myisamchk --recover tbl_name

$ isamchk --recover tbl_name

--recover选项只是针对安装而设置的Ruser real user name。这些特殊选项的选择将取决与你执行修复的类型。

3.再次刷新高速缓存，并释放表锁：

mysql>UNLOCK TABLES;

如何定位mysql性能问题_MySQL

ProgramName = 首先利用系统工具，定位问题[ top | nmon | dstat -dcnml | orzdba ]：

top命令查看各个进程的资源占用情况，各个指标依次解读如下【top f|1 --help】 ：

行：当前系统时间，系统运行时长，当前登录用户数，系统负载（任务队列的平均长度，三个数值分别为1,5,15分钟）【系统负载不要超过cpu的核数】

第二行：进程总数，正在运行的进程数据，睡眠的进程数，停止的进程数，僵尸进程数

第三行：用户空间占用cpu百分比，内核空间占用cpu百分比，用户进程空间改变过优先级的进程占cpu的百分比，空闲cpu百分比，等待输入输出cpu百分比，，，

第四行：物理内存总数，使用的物理内存总数，空闲内存总量，用作内核缓存的内存量 【70%】

第五行：交换区总量，使用的交换区总量，空闲交换区总量，缓冲的交换区总量 【不要使用交换分区】

pid 进程id

ppid 父进程id

uid 进程所有者用户id

user进程所有者用户名

group 进程所有者组名

tty 启动进程的终端

pr 优先级

NI 优先级（负值表示优先级高，正值表示优先级低）

p 使用的cpu，仅在cpu多的情况下

%CPU 上次更新到现在，cpu占用百分比

TIME 系统使用的cpu时间总计（秒）

TIME+ ~（1/100秒）

%MEM 进程使用的物理内存百分比

VIRT 进程使用的虚拟内存总量 VIRT=SWAP+RES

SWAP 进程使用的虚拟内存总量 VIRT=SWAP+RES

RES 进程使用的虚拟内存中，被换出的大小 进程使用的、未被换出的物理内存大小RES=CODE+DATA

CODE可执行代码占用物理内存大小

DATA 可执行代码以外的部分(数据段+栈)占用的物理内存大小

SHR 共享内存大小

nFLT 页面错误次数

nDRT 一次写入到现在，被修改过的页面数

S 进程状态 D=不可中断的睡眠状态 R=运行 S=睡眠 T=跟踪/停止 Z=僵尸进程

COMMAND 命令行，命令

WCHAN 若该进程在睡眠，则显示睡眠中的，系统函数名

FLAGS 任务标志

2.查看网络问题

netstat -i 1 是否存在丢包，网络错误问题

iftop 【D S】查看进程流量流向 按S切换是否显示本机的端口信息;按D切换是否显示远端目标主机的端口信息;

3.查看io流向

iotop 查(明确指定主键，并且有此笔资料，row看进程io走向

dd if=/dev/zero of=/data1/test bs=64k count=16k conv=fsync 目前生产环境EMC正常写入速度 565 MB/秒

dd if=/data1/test bs=64k |dd of=/dev/null 目前生产环境EMC正常读取速度 675 MB/秒

4.. query压力情况

mysqladmin -uroot -P3309 ext -ri 1 |grep -i queries

5.查看是否有锁等待

select r.trx_id waiting_trx_id,

r.trx_mysql_thread_id waiting_thread,

r.trx_query waiting_query,

b.trx_id blocking_trx_id,

b.trx_mysql_thread_id blocking_thread,

b.trx_query blocking_query

from information_schema.innodb_lock_waits w

INNER JOIN information_schema.innodb_trx b

ON b.trx_id = w.blocking_trx_id

INNER JOIN information_schema.innodblock_trx r

ON r.trx_id = w.requesting_trx_id/G;6.查看innodb状态

show engine innodb status/G;

7.从监控工具中查看,历史记录

mysql的行锁与表锁(select .... FOR UPDATE)

解锁表：UNLOCK TABLES

update的必须针对InnoDb，并且是在一个事务中，才能起作用。

，我们再启动第三个客户端，并且启动 第三个事务，执行alter语句,在course 表中加入一个字段hello 如下 。

select的条件不一样，采用的是行级锁还是表级锁也不一样。

由于

InnoDB

预设是

Row-Level

Lock，所以只有「明确」的指定主键，MySQL

才会执行

(只锁住被选取的资料例)

，否则

MySQL

将会执行

Table

Lock

(将整个资料表单给锁住)。

举个例子:

假设有个表单

products

，里面有

id

跟name

二个栏位，id

是主键。

例1:

locmysql中使用selectk)

SELECT

FROM

products

WHERE

id='3'

FOR

UPDATE;

例2:

(明确指定主键，若查无此笔资料，无

lock)

SELECT

FROM

products

WHERE

id='-1'

FOR

UPDATE;

例2:

(无主键，table

lock)

SELECT

FROM

products

WHERE

FOR

UPDATE;

例3:

(主键不明确，table

lock)

SELECT

FROM

products

WHERE

id<>'3'

FOR

UPDATE;

例4:

(主键不明确，table

lock)

SELECT

FROM

products

WHERE

id

LIKE

'3'

FOR

UPDATE;

注1:

FOR

UPDATE

仅适用于

InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。

注2:

要测试锁定的状况，可以利用

MySQL

的Command

Mode

，开二个视窗来做测试。

MySQL 原理与优化：原数据锁的应用

Row

MySQL 中原数据锁是系统自动控制添加的，对于用户来说无需显示调用，当我们使用一张表的时候就会加上原数据锁。

：表示卡住了，即无法往下执行。

原数据锁的作用是为了保护表原数据的一致性，如果在表上有活动事务的时候，不可以对元数据进行写入作。也就是为了避免DML 和DDL 之间的冲突，保证读写的正确性。

说白了就是， 在对数据表进行读写作的时候，不能进行修改表结构的作 。

如上图所示，在执行select 作的时候，MySQL 会自动加上shared_read 锁，在insert，update， delete 以及 select for update 作的时候会加上shared_write 锁，这两类锁是兼容的。

在执行alter table 作的时候，会加上 exclusive 锁，这个锁与shared_read 和 shared_write 锁 是互斥的，换句话说在 做查询和更新表数据的时候，是不能够修改表结构 的。

来看个例子

首先开启事务，使用select 语句会针对表加上shared_read的共享锁

begin;

select from course;

此时查看原数据锁的信息

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

接着，开启另外一个事务，记住刚才的事务不要commit

begin;

update course set name = 'Jason' where id =2;

再次查看原数据锁

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

从上面的截图可以看出，此时原数据锁的表中记录了两条记录分别是针对course 表的shared_read 和 shared_write 锁，也刚好对应我们执行的select 和update 作。

begin;

alter table course add column hello int;

由于之前的事务没有提交所以修改表的作会被阻塞， 因为shared_read 以及 shared_writeDBName = convert(CHAR(20),d.name), 这两个锁 与 exclusive之间是互斥的，所以会阻塞 。

此时，回到开始的两个客户端，对两个事务进行commit 作，再返回到第三个事务执行的alter 语句出，发现语句顺利执行。

如何查询锁表的SQL

2.外部锁定

用下边的语句查询，如果想结束直接kill

mysql>FLUSH TABLES;

SELECT SPID=p.spid,

锁定表的方法防止客户机的请求互相干扰或者服务器与维护程序相互干扰的方法主要有多种。如果你关闭数据库，就可以保证服务器和myisamchk和 isamchk之间没有交互作用。但是停止服务器的运行并不是一个好注意，因为这样做会使得没有故障的数据库和表也不可用。本节主要讨论的过程，是避免服务器和myisamchk或isamchk之间的交互作用。实现这种功能的方法是对表进行锁定。服务器由两种表的锁定方法：1.内部锁定内部锁定可以避免客户机的请求相互干扰——例如，避免客户机的SELECT查询被另一个客户机的UPDATE查询所干扰。也可以利用内部锁定机制防止服务器在利用myisamchk或isamchk检查或修复表时对表的访问。语法：锁定表：LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},?]解锁表：UNLOCK TABLESLOCK TABLES为当前线程锁定表。UNLOCK TABLES释放被当前线程持有的任何锁。当线程发出另外一个LOCK TABLES时，或当服务器的连接被关闭时，当前线程锁定的所有表自动被解锁。如果一个线程获得在一个表上的一个READ锁，该线程(和所有其他线程)只能从表中读。如果一个线程获得一个表上的一个WRITE锁，那么只有持锁的线程READ或WRITE表，其他线程被阻止。每个线程等待(没有超时)直到它获得它请求的所有锁。WRITE锁通常比READ锁有更高的优先级，以确保更改尽快被处理。这意味着，如果一个线程获得READ锁，并且然后另外一个线程请求一个WRITE锁, 随后的READ锁请求将等待直到WRITE线程得到了锁并且释放了它。显然对于检查，你只需要获得读锁。再者钟情跨下，只能读取表，但不能修改它，因此他也允许其它客户机读取表。对于修复，你必须获得些所以防止任何客户机在你对表进行作时修改它。2.外部锁定服务器还可以使用外部锁定（文件级锁）来防止其它程序在服务器使用表时修改文件。通常，在表的检查作中服务器将外部锁定与myisamchk或 isamchk作合使用。但是，外部锁定在某些系统中是禁用的，因为他不能可靠的进行工作。对运行myisamchk或isamchk所选择的过程取决于服务器是否能使用外部锁定。如果不使用，则必修使用内部锁定协议。如果服务器用--skip-locking选项运行，则外部锁定禁用。该选项在某些系统中是缺省的，如Linux。可以通过运行mysqladmin variables命令确定服务器是否能够使用外部锁定。检查skip_locking变量的值并按以下方法进行：◆ 如果skip_locking为off，则外部锁定有效您可以继续并运行人和一个实用程序来检查表。服务器和实用程序将合作对表进行访问。但是，运行任何一个实用程序之前，应该使用mysqladmin flush-tables。为了修复表，应该使用表的修复锁定协议。◆ 如果skip_locaking为on，则禁用外部锁定，所以在myisamchk或isamchk检查修复表示服务器并不知道，，关闭服务器。如果坚持是服务器保持开启状态，月确保在您使用此表示没有客户机来访问它。必须使用卡的锁定协议告诉服务器是该表不被其他客户机访问。检查表的锁定协议本节只介绍如果使用表的内部锁定。对于检查表的锁定协议，此过程只针对表的检查，不针对表的修复。1.调用mysql发布下列语句：$mysql _u root _p db_namemysql>LOCK TABLE tbl_name READ;mysql>FLUSH TABLES;该锁防止其它客户机在检查时写入该表和修改该表。FLUSH语句导致服务器关闭表的文件，它将刷新仍在告诉缓存中的任何为写入的改变。2.执行检查过程$myisamchk tbl_name$ isamchk tbl_name3.释放表锁mysql>UNLOCK TABLES;如果myisamchk或isamchk指出发现该表的问题，将需要执行表的修复。修复表的锁定协议这里只介绍如果使用表的内部锁定。修复表的锁定过程类似于检查表的锁定过程，但有两个区别。，你必须得到写锁而非读锁。由于你需要修改表，因此根本不允许客户机对其进行访问。第二，必须在执行修复之后发布FLUSH TABLE语句，因为myisamchk和isamchk建立的新的索引文件，除非再次刷新改表的高速缓存，否则服务器不会注意到这个改变。本例同样适合优化表的过程。1.调用mysql发布下列语句：$mysql _u root _p db_namemysql>LOCK TABLE tbl_name WRITE;mysql>FLUSH TABLES;2.做数据表的拷贝，然后运行myisamchk和isamchk：$cp tbl_name. /some/other/dir$myisamchk --recover tbl_name$ isamchk --recover tbl_name--recover选项只是针对安装而设置的。这些特殊选项的选择将取决与你执行修复的类型。()3.再次刷新高速缓存，并释放表锁：mysql>FLUSH TABLES;mysql>UNLOCK TABLES;

LoginName = convert(CHAR(20),l.name),

HostName = convert(CHAR(20),hostname),

Status = p.status,

BlockedBy = p.blocked,

LoginTime = login_time,

QUERY = CAST(t.TEXT AS VARCHAR(MAX))

FROM MASTER.dbo.sysprocesses p

INNER JOIN MASTER.dbo.sysdatabases d

ON p.dbid = d.dbid

INNER JOIN MASTER.dbo.syslogins l

ON p.sid = l.sid

CROSS APPLY sys.dm_exec_sql_text(sql_handle) t

WHERE p.blocked = 0

AND EXISTS (SELECT 1

FROM MASTER.dbo.sysprocesses p1

WHERE p1.blocked = p.spid)