mysql中的锁都有哪些(mysql锁类型)

MySQL中有哪些锁？

数据库中锁的设计初衷处理并发问题，作为多用户共享资源，当出现并发访问的时候，数据库需要合理控制资源访问规则。锁就是实现这些访问规则中的重要数据。

锁的分类

根据加锁范围，MySQL里面的锁可以分成全局锁、表级锁、行锁三类。

全局锁

全局锁，就是对整个数据库实例加锁，MySQL提供了一个加全局读锁的方法，命令是:

Flushtableswithreadlock(FTWRL)

当需要整个库只读状态的时候，可以使用这个命令，之后其他线程的：数据更新语句（增删改），数据定义语句（建表，修改表结构）和更新事务的提交语句将会被阻塞。

全局锁的使用场景

全局锁的定型使用场景，做全库逻辑备份。也就是把整个库每个表都Select出来，然后存成文本。

如何整个库都只读，会有什么问题？如果你在主库上备份，那么在备份期间都不能执行更想，业务就基本上停摆。如果在从库上备份，那么备份期间从库不能执行主库同步过来的binlog,会导致从延迟。既然要全库只读，为什么不使用setglobalreadonly=true的方式呢

readonly方式也可以让全库进入只读状态,但我还是会建议你用FTWRL方式，主要有两个原因：

一是，在有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改global变量的方式影响面更大，我不建议你使用。二是，在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高表级别锁

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（metadatalok,MDL）。表锁的语法是：

locktablesread/write

与FTWRL类似,可以使用unlocktables主动释放锁，也可以在客户端断开的时候自动释放。需要注意的是，locktables语法除了会限制别的线程的读写外，也限定了本线程接下来的 *** 作对象。

MDL表级锁

MDL不需要显示使用，在访问一个表的时候自动加上，MDL保证读写的正确性，也就是说在查询数据时，不允许有其他线程对这个表结构做变更。

什么 *** 作会加MDL锁？

在MySQL55版本中引入了MDL，当对一个表做增删改查 *** 作的时候，加MDL读锁；当要对表做结构变更 *** 作的时候，加MDL写锁。

读锁之间不互斥，因此可以有多个线程同时对一张表增删改查。读写之间、写锁之间是互斥的，用来保证变更表结构 *** 作的安全性，如果有两个线程要同时给一个表加字段，其中一个要等另外一个执行完才能执行。更改表结构要注意哪些？

给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。在对大表 *** 作的时候，你肯定会特别小心，以免对线上服务造成影响。而实际上，即使是小表， *** 作不慎也会出问题，导致整个库的线程爆满。

举个例子

我们来看一下下面的 *** 作序列，假设表t是一个小表。

image

sessionA先启动，这时候会对表t加一个MDL读锁。由于sessionB需要的也是MDL读锁，因此可以正常执行。sessionC会被blocked，是因为sessionA的MDL读锁还没有释放，而sessionC需要MDL写锁，因此只能被阻塞，读写锁互斥。如果只有sessionC自己被阻塞还没什么关系，但是之后所有要在表t上新申请MDL读锁的请求也会被sessionC阻塞。前面我们说了，所有对表的增删改查 *** 作都需要先申请MDL读锁，就都被锁住，等于这个表现在完全不可读写了。

如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新session再请求的话，这个库的线程很快就会爆满。事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

怎么解决这个更改表结构问题

比较理想的机制是，在altertable语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。

ALTERTABLEtbl_nameNOWAITaddcolumnALTERTABLEtbl_nameWAITNaddcolumn

1 先通过top命令查看产用资源较多的spid号

2查询当前耗时的会话ID，用户名，sqlID等：

select sid,serial#,machine,username,program,sql_hash_value,sql_id,

      to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') as login_time from v$session

where paddr in (select addr from v$process where spid in ('5648612','256523'));

3 如果上一步sql_id或者 hash_value不为空，则可用v$sqlarea查出当前正在使用的sql

select sql_text

  from v$sqltext_with_newlines

  where hash_value = &hash_value

order by piece;

也可直接使用:

select a,bSQL_TEXT from (

select sid,serial#,machine,username,program,sql_hash_value,sql_id,

      to_char(logon_time,'yyyy/mm/dd hh24:mi:ss') as login_time from v$session

where paddr in (select addr from v$process where spid in ('23226'))

) a,v$sql b

where asql_id  = bSQL_ID(+)

4kill占用大资源的session

Alter system kill session 'SID,SERIAL#'
解锁：
1查询哪些对象被锁：

    select object_name,machine,ssid,sserial#

    from v$locked_object l,dba_objects o ,v$session s

    where lobject_id　=　oobject_id and lsession_id=ssid;

2下面的语句用来杀死一个进程：

    alter system kill session '524,1095'; (其中24,111分别是上面查询出的sid,serial#)

3再一次查询目前锁定的对象，若发现以上方法不能解除锁定的表，则用以下方法：

  31 执行下面的语句获得进程（线程）号：

    select spid, osuser, sprogram

    from v$session s,v$process p

    where spaddr=paddr and ssid=524 （524是上面的sid）

1创建测试表，如下图。

createtabletest_lock(idnumber,valuevarchar2(200));

2执行append语句；并且不做提交，insert/+append/intotest_lockvalues(1,1)如下图。

3再次执行清表语句，truncatetabletest_lock;报锁表错误，如下图。

4查看锁表语句，发现被锁表

selectbobject_name,t

fromv$locked_objectt,user_objectsb

wheretobject_id=bobject_id

注意事项：

简化数据:可以将复杂的查询创建为其他人可以使用的视图，而不必了解复杂的业务或逻辑关系。这简化并掩盖了视图用户数据的复杂性。

表结构设计的补充:在设计的系统才刚刚开始,大部分的程序直接访问数据表结构,但是随着业务的变化,系统更新,等等,引起了一些表结构不适用,这次修改系统的表结构太大,开发成本较高的影响。

这个时候可以创建一个视图来补充表结构设计,降低开发成本。程序可以通过查询视图直接获得它想要的数据。

添加安全性:视图可以向用户显示表中的指定字段，而不是向用户显示表中的所有字段。在实际开发中，视图通常作为提供数据的一种方式提供，并将只读权限提供给第三方以供查询使用。

---