数据库的事务/隔离级别/锁的分类

命令

【手动添加表锁】
lock table 表名 read(write), 表名2 read(write)
【查看表上加过锁】
show open tables;

in_use 字段 1表示有锁

【释放表锁】
unlock tables;
【如何分析表锁定】
show status like "table%"

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 168   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 32    |
| Table_open_cache_misses    | 2     |
| Table_open_cache_overflows | 2     |
+----------------------------+-------+

【查看事务级别】
show variables like "tx_isolation";
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
【查看索引】
show index from test_innodb_lock;

-+---------------+
| Table            | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_innodb_lock |          1 | test_innodb_a_ind      |            1 | a           | A         |           6 |     NULL | NULL   | YES  | BTREE      |
 |               |
| test_innodb_lock |          1 | test_innodb_lock_b_ind |            1 | b           | A         |           7 |     NULL | NULL   | YES  | BTREE      |
 |               |
+------------------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

【行锁的分析】
sho'w status like "innodb_row_lock%"

+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 121532 |
| Innodb_row_lock_time_avg      | 30383  |
| Innodb_row_lock_time_max      | 51028  |
| Innodb_row_lock_waits         | 4

数据库中的CAP原理

CAP概述

C：Consistency 一致性

A: Availability 可用性

P：Partition Tolerance 区分容错性
CAP 理论核心是：一个分布式系统不可能同时很好的满足一致性，一致性和区分容错性这三个需求，最多只能同时较好的满足两个。

因此，根据CAP原理，将NoSQL数据库分成满足CA原则、CP原则和AP原则 三大类：
CA - 单点集群，满足一致性，可用性的系统，通常在可扩展性上不太强大。 （传统数据库）
CP - 满足一致性，分区容忍性的系统，通常性能不是特别高。 （Redis、MongoDB）
AP - 满足可用性，分区容忍性的系统，通常可能对一致性要求低一些。 （大多数网站架构的选择）
Consistency 一致性

对于一致性这个特点，可以分为客户端和服务端两个不同的视角。对于客户端而言，一致性主要指的是多并发访问同时更新过的数据如何获取的问题。从服务端而言，则是更新如何复制到整个系统，以保证数据最终一致。一致性是因为有并发读写才有的问题，因此在理解一致性的问题上时，一定要结合考虑并发读写的场景。

从客户端角度，多进程并发访问时，更新过的数据在不同进程如何获取不同策略，决定了不同的一致性。对于关系型数据库，要求更新过的数据被后续的访问都能看到，这是强一致性。如果能容忍后续的部分或者全部访问不到，则是弱一致性。如果经过一段时间后要求能访问到更新后的数据，则是最终一致性。

Availability 可用性

对于一个可用的分布式系统，每一个非故障节点必须对每一个请求做出响应。也就是，该系统使用的任何算法必须最终终止。当同时要求分区容忍时，这是一个很强的定义：即使是严重的网络错误，每个请求必须终止。

好的可用性主要指的是系统能够很好的为用户服务，不出现用户操作失败或者访问超时等用户体验不好的情况。可用性通常指的是可用性和分布式数据冗余，负载均衡等有着很大的关联。

Partition Tolerance 区分容错性

区分容错性和扩展性紧密相关，在分布式应用中，可能因为一些分布式原因导致系统无法正常运行，好的分区容错性要求能够使应用虽然是一个分布式系统，而看上去确好像在一个可以运转正常的整体，比如现在分布式系统中有一个或者几个宕机了，其他剩余的机器能够正常运转；或者是机器之间网络异常，将分布式系统分隔未独立的几个部分，几个部分还能维持分布式系统的运作，这样就具有好的分区容错性。

事务和ACID

事务：由一组sql语句组成的逻辑处理单元，事务具有以下4个属性，通常简称事务的ACID属性。

• 原子性（Atomicity）：
  事务是一个原子操作单元，其对数据的修改，要么全部执行，要么全部不执行。

• 一致性（Consistent）:
  在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须正确的

• 隔离性（Isolation）:
  数据库系统提供一定的隔离机制，保证事务在不受外部并发操作的影响的“独立”环境执行。这意味着事务处理过程中间状态对外部都是不可见的，反之亦然。

• 持久性（Durable）:
  事务完成后，它对于数据的修改时是永久的，即使出现系统的故障也能保持。

并发事务带来问题

• 更新丢失（Lost Update）
• 脏读(Dirty Reads)

一个事务正在对一条记录做修改，在这个事务完成提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏数据”，并据此做进一步的处理，就会产生未提交的数据依赖的关系。这种现象被形象叫做：“脏读”。

事务A读取到了事务B已修改但未提交的数据，还这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。

• 不可重复读(Non-Repeatable Reads)

事务A读取到了事务B已经提交的修改数据，不符合隔离性

• 幻读(Phantom Reads)
  事务A读取了事务B提交的新增数据，不符合隔离性。

事务隔离级别为了 解决并发事务的问题

（1）Read Uncommitted（读取未提交内容）

A可以读取到B还未提交的事务。

（2）Read Committed（读取提交内容）

A只能读取到B已经提交的事务。并且A事务中两次读到的内容不一致，原有就是B提交事务。

（3）Repeatable Read（可重读）

A只能读取到B已经提交的事务。并且A事务中两次读到的内容一致，A事务结束后再读取会读取到B提交事务。

（4）Serializable（可串行化）A事务未提交，B事务就等待

表锁

1617101640.png
总结：读锁（共享锁）会阻塞写，但是不会阻塞读；写锁（排它锁）写，和读都会堵塞

行锁

1617101748(1).png

• 危害

1. 无索引行锁升级为表锁
2. 间隙锁危害
3. 面试题：如何锁定一行select * from test_innodb_lock where a = 9 for update;

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

优化建议

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