Mysql里的锁(排它锁、共享锁、行锁、表锁、间隙锁、临键锁、意向锁)

MySQL中的锁（排他锁、共享锁、行锁、表锁、间隙锁、临时键锁、意向锁） 在MySQL数据库中，锁机制是保证数据一致性和完整性的关键。
MySQL提供了多种锁类型来满足不同场景的需求。
下面是MySQL中主要锁类型的详细说明： 一、属性锁 1 、共享锁（SharedLock、S Lock） 定义：共享锁也称为读锁。
当一个事务对数据加读锁时，其他事务只能对数据加读锁，而不能对数据加写锁。
其他事务可以对其持有写锁，直到所有读锁被释放。
特点：支持并发读取数据，不支持边读数据边修改，避免重复读取问题。
应用场景：适用于需要并发读取数据的场景，保证读取的数据在读取过程中不会被其他事务修改。
2 、排他锁（EXCLUSIVELOCK，特点：数据修改时，不允许其他人同时修改，也不允许其他人读取，从而避免了脏数据、脏读的问题。
应用场景：适合需要修改数据的场景，保证修改过程中数据不会被其他事务读取或修改。
2 、粒度锁 1 、表锁定义：加锁时，锁定整个表。
当next时当一个事务访问表时，必须等到前一个事务的锁被释放后才能访问表。
特点：粒度大，加锁简单，不易冲突。
适合对整张表进行操作，比如批量更新或者删除整张表。
2 、行锁定义：加锁时，表中的一行或多行记录被锁定，只有被锁定的记录不能被访问，其他记录可以正常访问。
比表锁问题多，不易发生冲突，支持比表锁更高的并发。
应用场景：适合对表中特定行的操作，如更新或删除特定行中的数据 3 、记录锁（RecordLock）定义：记录锁也是行锁的一种，但记录锁的范围仅限于表中的某一条记录：命中准确位置，命中位置字段为唯一索引，查询时数据被修改时的重复读取，同时也避免了修改的事务在提交前被其他事务读取时的脏读问题。
间隙锁是行锁的一种。
间隙锁是在事务加锁后锁定一定区间的表记录，遵循左开右闭的原则，就会形成间隙。
查询条件必须命中索引，间隙锁只会出现在REPEATABLE_READ事务级别。
作用：防止幻读问题，保证并发事务时数据的一致性。
下一键锁也是行锁的一种。
该记录已被锁定并且有一个间隙锁的组合，它也会锁定查询范围内的所有间隙位置，并且还会锁定下一个相邻区间：范围查询和命中，查询命中索引。
作用：结合了记录锁和间隙锁的特点，避免了范围查询时的脏读、重复读、幻读等问题。
三、状态锁 1 、意向共享锁（IS Lock） 定义：当事务试图对整个表添加共享锁时，需要首先获得该表的意向共享锁。
表示一个事务打算给表加共享锁，以防止其他事务给整个表加排它锁。
2 、意向排他锁（Intent Exclusive Lock，IX Lock）定义：当一个事务试图对整个表加排它锁时，需要首先获得该表的排它锁：表明一个事务打算对表加排它锁，以防止其他事务对整个表加共享锁或排它锁。
为什么需要意向锁？意向锁的主要作用是避免扫描整个索引树的每个节点来检查是否被锁定，从而提高数据库性能。
当一个事务锁定表中的一行时，同时对该表设置一个意向锁，通知其他事务该表中的行被锁定，从而避免扫描整个索引树。
简而言之，MySQL中的锁机制是一个复杂而强大的系统，可以确保数据的一致性和完整性。
在实际应用中，需要根据具体场景选择合适的锁类型，以平衡并发性和数据一致性。

MySQL 给数据表增加一列，一定会锁表吗？

当您使用MySQL中的InnoDB存储引擎向数据表添加列时，该表不一定会被锁定。
具体行为取决于MySQL版本和操作类型。
下面详细分析： 1 、InnoDB表的锁机制及锁特性 锁分类 操作类型：读锁（共享锁，允许多个读操作并行）和写锁（排他锁，阻塞其他读写操作）。
操作粒度：表锁（锁定整个表）和行锁（锁定单行数据）。
InnoDB默认使用行级锁，并使用多版本并发控制（MVCC）来实现非阻塞读写操作，并且仅在必要时更新表锁。
InnoDB表锁定具有自动隐式锁定功能，无需手动指定。
锁持有时间短，受事务隔离级别和锁模式影响。
支持在线DDL操作（例如修改MySQL5 .6 +元数据）以减少表锁定要求。
2 、MySQL不同版本添加列锁定表行为 1 、MySQL 5 .6 行为 在线DDL支持：从5 .6 版本开始，InnoDB引入在线DDL，允许部分表结构更改而无需锁定整个表。
具体场景： 添加可为空列：在不锁定表的情况下执行快速元数据操作，但可能存在短期行锁。
添加非空列：这也是通过元数据操作完成的，无需锁定表。
注意：由于元数据更新、日志写入等原因，对大型表的操作仍可能导致性能下降。
建议在低负载期间执行这些操作，以避免影响活动。
2 . MySQL 8 .0原子DDL优化：通过减少中间状态阻塞来确保ALTERTABLE操作成功或完全恢复。
即时元数据更新：添加字段时立即更新元数据，无需等待操作完成，减少锁表时间。
InnoDB引擎优化：添加非空字段时，不需要复制所有表数据，而是使用轻量级操作。
增量元数据更新仅更改受影响的部分，从而减少开销。
对 InvisibleIndexes 和 In-PlaceAlter 等功能的新支持可进一步提高并发性能。
3 . 可能的表锁场景 虽然 MySQL 5 .6 + 和 8 .0 对 DDL 操作进行了显着优化，但以下情况仍然会导致表锁： 数据量极大：当表数据或索引量​​过大时，元数据更新或数据重组可能会导致短期完全表锁。
复杂操作：例如更改列类型、同时添加索引或约束，这些都会触发表重建。
低版本限制：MySQL 5 .5 及之前版本没有在线DDL支持，添加列通常需要表锁。
4 . 总结和提示 要点：MySQL 5 .6 + 和 8 .0 在添加列时通常不会锁定表，特别是对于简单操作（例如添加可为空/不可为空的列）。
锁表的行为取决于版本、操作类型和数据量。
实用技巧：优先使用 MySQL 8 .0，以利用原子 DDL 和增量更新等优化。
操作前评估表的大小。
对于大表，建议在非高峰时段运行。
通过 SHOWPROCESSLIST 或性能监控工具观察运营影响。
通过合理选择版本和优化操作时间，您可以最大限度地减少添加列对业务的影响。

MySQL多线程并发控制详解_锁粒度、死锁预防与调优技巧

MySQL通过锁机制和并发控制来实现高并发下的数据一致性和完整性。
其核心在于InnoDB的MVCC和两阶段锁协议（2 PL）的配合。
下面从锁粒度、死锁预防和并发调优三个方面进行详细讲解： 1 、锁粒度：权衡并发和隔离锁粒度决定了并发访问的效率。
MySQL 提供了三种类型的表级锁、行级锁和意向锁： Table-LevelLocks 锁定整个表，适用于低并发场景（例如 DDL 操作或报表生成）。
MyISAM引擎依赖于表级锁，管理简单但容易阻塞。
例如，执行ALTERTABLE时，其他操作需要排队。
缺点：并发量大时性能急剧下降。
仅在在线写入要求极低的场景下，建议手动使用LOCKTABLES。
Row-LevelLocks是InnoDB的核心特性。
它只锁定操作行（如UPDATEusersSETbalance=1 00WHEREid=1 2 3 ），大大提高了OLTP并发能力。
关键点：需要和索引配合使用，否则可能退化为表锁或者触发“间隙锁”（加锁范围过大）。
示例：如果 SELECT...FORUPDATE 错过索引，则会锁定大量不相关的行，导致并发性能降低。
意向锁（IntentionLocks） InnoDB 特有的表级锁用于指示事务的锁定意图（例如行级共享锁 IS 或排他锁 IX）。
作用：避免表级锁和行级锁的冲突检测开销。
例如，事务A为表添加了意向排他锁（IX）后，当事务B尝试添加表级共享锁而不遍历所有行时，会很快检测到冲突。
应用建议：高并发的OLTP系统优先使用行级锁，但需要保证SQL准确命中索引，避免锁升级。
2 . 防止死锁：打破循环依赖。
死锁是指两个或多个事务持有彼此所需的锁，形成循环等待。
InnoDB通过死锁检测机制回滚“受害者”事务，但预防更关键：保持事务简短，减少事务持有锁的时间，并避免耗时的操作（例如网络请求或用户交互）。
经验：如果在交易过程中需要等待外部响应，那么设计可能不合理。
统一的资源访问顺序 所有事务以相同的顺序访问资源。
例如，更新账户余额时，始终先操作ID较小的用户： --事务1 ：先ID=1 ，然后ID=2 UPDATEaccountsSETbalance=...WHEREid=1 ;UPDATEaccountsSETbalance=...WHEREid=2 ;--事务2 ：如果顺序颠倒，很容易导致死锁。
添加适当的索引，确保 SELECT...FORUPDATE、UPDATE 和 DELETE 语句通过索引准确锁定目标行。
当没有索引时，InnoDB可能会扫描整个表并锁定所有行，增加死锁的风险。
避免在事务中等待用户输入。
如果交易开启后需要等待用户确认，那么锁会被长期持有。
正确做法：在开始交易之前获取所有数据。
实现应用层重试机制，捕获死锁异常（MySQL错误码1 2 1 3 ），通过指数退避（如第一次等待5 0ms，第二次等待1 00ms）进行重试。
工具：SHOWENGINEINNODBSTATUS可以查看死锁详细信息（涉及事务、等待锁和持有锁）。
3 . 并发调优：系统性优化并发调优需要全面的硬件、配置、数据库设计和代码优化。
目标是在高并发下保持低延迟和高吞吐量： 优化InnoDB参数 innodb_buffer_pool_size：缓存数据和索引的内存大小。
建议设置为物理内存的5 0%-7 0%。
innodb_log_file_size 和 innodb_log_buffer_size：影响事务日志写入，大的日志文件会降低检查点频率。
innodb_flush_log_at_trx_commit：数据持久性和性能之间的权衡。
1 （默认）：每次提交时同步到磁盘，最安全但性能最低。
0：每秒刷新一次，1 秒数据可能丢失，性能最高。
2 ：每秒提交到操作系统缓存并刷新到磁盘，平衡安全性和性能。
innodb_io_capacity：设置存储系统IOPS，影响后台清理和刷新频率。
innodb_thread_concurrency：MySQL 8 .0之后重要性降低，通常保持默认值。
正确设置连接数。
max_connections 定义最大并发连接数。
如果太高，会消耗内存，如果太低，会导致连接失败。
使用SHOWSTATUSLIKE'Max_used_connections'观察历史峰值然后进行调整。
应用程序优化连接池：避免为每个请求建立/关闭连接的开销。
读写分离：在读多写少的场景下，读请求分发到从库，写请求发送到主库。
分库分表：当数据量和并发压力过大时，分散到多个实例或表。
批量操作：将多个INSERT、UPDATE和DELETE合并为批量操作，以减少网络往返和事务开销。
持续监控分析 SHOWENGINEINNODBSTATUS：查看事务、锁、死锁等运行时状态。
Performance_schema 和 sysschema：监控锁等待、SQL 执行效率等。
慢查询日志：记录执行时间超过 long_query_time 的 SQL，以定位性能瓶颈。
总结：并发调优是一个持续迭代的过程，需要根据监控数据和历史趋势来调整参数或优化代码。
瓶颈往往源于不合理的SQL、索引设计或者应用程序对数据库的不当使用。