数据库锁介绍

数据库锁

锁是计算机协调多个进程或纯线程并发访问某一资源的机制。
在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说,锁对数据库而言显得尤其重要，也更加复杂。

数据库锁分为两类，一个是悲观锁，一个是乐观锁，悲观锁一般就是我们通常说的数据库锁机制，乐观锁一般是指用户自己实现的一种锁机制，比如hibernate实现的乐观锁甚至编程语言也有乐观锁的思想的应用。

每个数据库对锁的设计和实现各不相同，以下只针对以下共性和概念进行简单描述。

悲观锁

悲观锁：对于数据被外界修改持悲观态度，认为数据随时会修改，所以整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系数据库提供的锁机制，事实上关系数据库中的行锁，表锁不论是读写锁都是悲观锁。

悲观锁按照使用性质划分：

共享锁（share locks）

又称读锁、s锁，当一个事务为某个对象添加共享锁后，其他事务只能对该对象加共享锁，多个事务可以同时读，但不能有写操作，直到该对象释放所有共享锁。

排它锁（exclusive locks）

又称写锁，x锁，但一个事务为某个对象添加排它锁后，其他事务不能对该对象加任何锁，只有当前事务可以读写该对象，直到当前事务释放该对象的排它锁。

更新锁（ update locks）

又称u锁，用来预定要对此对象施加排它锁，它允许其他事务读，但不允许再施加更新锁或排它锁，当被读取的对象将要被更新时，则升级为排它锁。
主要是用来防止死锁的。因为使用共享锁时，修改数据的操作分为两步，首先获得一个共享锁，读取数据，然后将共享锁升级为排它锁，然后再执行修改操作。这样如果同时有两个或多个事务同时对一个对象申请了共享锁，在修改数据的时候，这些事务都要将共享锁升级为排它锁。这些事务都不会释放共享锁而是一直等待对方释放，这样就造成了死锁。如果一个数据在修改前直接申请更新锁，在数据修改的时候再升级为排它锁，就可以避免死锁。

悲观锁按照作用范围（锁的对象）划分：

行锁

锁的作用范围是行级别，数据库能够确定那些行需要锁的情况下使用行锁，如果不知道会影响哪些行的时候就会使用表锁。
如，一个用户表user，有主键id和用户生日birthday当你使用update … where id=?这样的语句数据库明确知道会影响哪一行，它就会使用行锁，当你使用update … where birthday=?这样的的语句的时候因为事先不知道会影响哪些行就可能会使用表锁。

表锁

锁的作用范围是整张表。

乐观锁

乐观锁：对于数据被外界修改持乐观态度，认为每次自己操作数据的时候没有人回来修改它，所以不去加锁，但是在更新的时候会去判断在此期间数据有没有被修改，需要用户自己去实现。既然都有数据库提供的悲观锁可以方便使用为什么要使用乐观锁呢？对于读操作远多于写操作的时候，大多数都是读取，这时候一个更新操作加锁会阻塞所有读取，降低了吞吐量。最后还要释放锁，锁是需要一些开销的，我们只要想办法解决极少量的更新操作的同步问题。换句话说，如果是读写比例差距不是非常大或者你的系统没有响应不及时，吞吐量瓶颈问题，那就不要去使用乐观锁，它增加了复杂度，也带来了额外的风险。

实现乐观锁：
1.表中增加一个表示版本的列，如version int not null。
2.查询时查出版本。
3.删除或修改时，用查出的版本号和数据库对比，如果匹配成功，则执行，否则不执行。如where id=xxx and version=1;。

MySQL锁

表锁

开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
MyISAM只支持表锁。

表读锁

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

MySQL中表读锁四条规律：
当前session(连接)为某个表添加读锁后，任何session都可以读(查询)这个表。
当前session为某个表添加读锁后，当前session不可以写(删除修改)这个表。
当前session为某个表添加读锁后，其他session的写这个表会被一直阻塞。
当前session为某个表添加读锁后，必须释放该锁之后才能读、写其他表。

表写锁

写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

MySQL中表写锁三条规律：
当前session(连接)为某个表添加写锁后，当前session可以读(查询)这个表，也可以写(删除修改)这个表。
当前session为某个表添加写锁后，其他session的读会被一直阻塞（有个特例【不知道是客户端的缓存还是mysql服务器的缓存】，当前session内，查询语句和上一次查询语句一致时，会从缓存取，就不会阻塞），写也会被一直阻塞。
当前session为某个表添加写锁后，必须释放该锁之后才能读、写其他表。

手动增加表锁

格式：

lock table 表名字1 read或者write,表名字2 read或者write,......；

示例：

lock table users read;
lock table users write;

查看表锁

show open tables;

结果集字段解释：
in_use=1=有表锁，in_use=0=无表锁

show status like 'table%';

结果集字段解释：
Table_locks_immediate:产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1；
Table_locks_waited（重要）:出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较严重的表级锁争用情况；

此外， MyISAM的读写锁调度是写优先(优先使用写锁)，这也是MyISAM不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞

释放表锁

释放所有表锁：

unlock tables;

unlock tables会释放当前会话的表锁，也可以释放flush tables with read lock(这个命令会给MySQL中所有的数据库加锁)语句所加的全局只读锁。

通过事务强制释放锁：

show full processlist; -- 显示完整的进程列表
select * from information_schema.innodb_trx; -- 查看有是哪些事务占据了表资源
kill 436;#kill id

行锁

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

行锁是加在主键索引上的。

sql增删改查的时候mysql自动添加和释放行锁，手动添加行锁可以使用select * from where id=1 for update;。手动解锁commit

InnoDB预设的是行级锁，但只有明确指定主键的时候MySQL才会执行行锁，否则MySql将会执行表锁。

InnoDB行锁会在一些情况下自动升级为表锁，如索引失效。

Innodb:存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于 MylSAM的表级锁定的。当系统并发量较高的时候， Innodb的整体性能和 MylSAM相比就会有比较明显的优势了。
但是， Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让 Innodb的整体性能表现不仅不能比 MylSAM高,甚至可能会更差。

行读锁

select语句会自动触发行读锁。

行写锁

update、delete语句或自动触发行读锁。

delete 执行时 MySQL 会将所有涉及的行加写锁和 Gap 锁（间隙锁）。

间隙锁

间隙锁（Gap Lock）：锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。
当我们用范围条件而不是相等条件索引数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项枷锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”。

InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

间隙锁（Gap Lock）：锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。

间隙锁的引入是为了解决在RR隔离级别的幻读问题。

看完后从上帝视角总结一下，其实就是二级索引搜索出的数据本身就有存在间隙的可能，所以要加间隙锁，而且间隙锁是加在一级索引上的。
总结一下就是二级索引的等号条件搜索可能搜出多条，所以也要加间隙锁，加间隙锁实际上是加在主键上，所以相邻主键间的间隙也会被锁定

【危害】
因为 Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。
比如

#假设数据库表的id现在只有：1，2,3
update ... where id>2;#如果该sql不提交
insert ...(id...) values(4...);#则别的sql增删改这个范围的数据会一直阻塞

手动增加行锁

select * from where id=1 for update;

手动解锁：

commit

查看行锁

show status like 'innodb_row_lock%';

结果集字段解释：
Innodb_row_lock_ current_ waits:当前正在等待锁定的数量；
Innodb_row_lock_time:从系统启动到现在锁定总时间长度；
Innodb_row_lock_time_avg:每次等待所花平均时间；
Innodb_row_lock_time_max:从系统启动到现在等待最常的一次所花的时间；
Innodb_row_lock_ waits:系统启动后到现在总共等待的次数；

对于这5个状态变量，比较重要的主要是
Innodb.row_lock_time_avg(等待平均时长)，
Innodb_row_lock_ waits(等待总次数)
Innodb_row_ lock _time(等待总时长)这三项。
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析(Show Profile)系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

锁优化

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
合理设计索引，尽量缩小锁的范围
尽可能较少检索条件，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度
尽可能低级别事务隔离

页锁

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

其他

mysql锁请参考：https://yq.aliyun.com/articles/646976

Oracle锁

select * from t1 for update;#给该表的所有行都加上锁

oracle请参考：https://www.cnblogs.com/zhoading/p/8547320.html