mysql和postgresql锁机制对比

最新推荐文章于 2025-02-14 22:44:33 发布

原创最新推荐文章于 2025-02-14 22:44:33 发布 · 1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #postgresql #数据库 #java #sql

本文深入探讨了MySQL和PostgreSQL的锁机制，包括表锁和行锁的类型及其用法。PostgreSQL的锁分类更详细，避免锁升级，但使用更复杂；而MySQL在特定情况下会将行锁升级为表锁。两种数据库各有优势，如PostgreSQL的物理复制和无CPU核心数限制，以及MySQL的直观执行计划和索引组织表。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

数据库锁是我们开发中经常需要使用的。锁是数据库事务的基础，通过锁才能保证数据库在并发时能够保证数据的安全和一致，才能够达到事务的一致性和隔离性。

1. mysql锁的介绍

mysql锁下面有详细的介绍：

mysql数据库表锁行锁的理解_傻鱼爱编程的博客-CSDN博客

2. postgresql锁的介绍

postgresql是完全开源的，且大多数公司都在使用这个数据库。

postgresql中表锁和行锁比mysql分类要细很多。

表锁：

1.访问共享锁（ACCESS SHARE）使用SELECT命令可在查询的表上获得该锁。一般规则是所有的查询中只有读表才获取此锁。
2.行共享锁（ROW SHARE）使用SELECT FOR UPDATE和SELECT FOR SHARE命令可在当前表上获得该锁。
3.行排他锁（ROW EXCLUSIVE）使用UPDATE、INSERT 和 DELETE 命令在目标表上获得该锁（以及查询中所有引用的表的访问共享锁）。一般规则是所有修改表的查询获得该锁。
4.共享更新排他锁（SHARE UPDATE EXCLUSIVE） - VACUUM（不含FULL），ANALYZE，CREATE INDEX CONCURRENTLY，和一些 ALTER TABLE 的命令获得该锁。
5.共享锁（SHARE），也就是读锁，当它加到表上之后，整个表只允许读，如果我们为一个表创建索引（如：CREATE INDEX 命令），会创建这种锁。
6.共享行排他锁（SHARE ROW EXCLUSIVE） - 不被任何命令隐式获取。
7.排他锁（EXCLUSIVE）它不能由任何命令获取，这个锁模式在事务获得此锁时只允许读取操作并行。
8.访问排他锁（ACCESS EXCLUSIVE）与所有的模式的锁冲突 - ALTER TABLE，DROP TABLE，TRUNCATE，REINDEX，CLUSTER，VACUUM FULL 等命令。此锁模式是 LOCK 命令的默认模式。

表互斥的明细表如下：

行锁：

pgsql中有两种方式会用到行锁：
对行执行update,delete操作
显式指定行锁（例如select for update）

行锁的分类如下：

1.更新（FOR UPDATE）会锁定SELECT检索到的行，就好像它们要被更新。可以阻止它们被其他事务锁定、修改或者删除，直到当前事务结束。
2.无键更新（FOR NO KEY UPDATE）这种模式与 FOR UPDATE 相似，获得的锁较弱,它不会阻塞SELECT FOR KEY SHARE 锁模式。不修改主键、唯一键值的UPDATE会获得这种锁模式。
3.共享（FOR SHARE）这种模式与无键更新锁类似，不过它在每个检索到的行上获得一个共享锁而不是排他锁。不阻止它们进行 SELECT FOR SHARE 或 SELECT FOR KEY SHARE。
4.键共享（FOR KEY SHARE）类似于共享，但该锁是较弱的共享锁。阻止了 SELECT FOR UPDATE，但不阻止 SELECT FOR NO KEY UPDATE。一个键共享锁阻止其他事务进行 DELETE 或任何更改该键值的 UPDATE，但不妨碍任何其他 UPDATE、SELECT FOR NO KEY UPDATE、SELECT FOR SHARE 或者SELECT FOR KEY SHARE。

表互斥的明细表如下：

3. 两种数据库之间的对比

postgresql数据库锁的分类详细，他不会出现锁升级的情况，但也带来用法的繁琐。

mysql数据库当行锁不是所在主键上时会升级成表锁。

mysql和postgresql总体不同基本对比如下：

一.PostgreSQL的优势
1、PGSQL没有CPU核心数限制，MySQL能用128核CPU。
2、PG主表采用堆表存放，MySQL采用索引组织表，能够支持比MySQL更大的数据量。
3、PG的主备复制属于物理复制(完胜逻辑复制，维护简单)，相对于MySQL基于binlog的逻辑复制（sql_log_bin、binlog_format等参数设置不正确都会导致主从不一致），数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。
4、MySQL的存储引擎插件化机制，存在锁机制复杂影响并发的问题，而PG不存在这个机制。
6、PGSQL支持 JIT 执行计划即时编译，使用LLVM编译器，MySQL不支持执行计划即时编译。
6、PGSQL一共有255个参数，用到的大概是80个，参数比较稳定，用上个大版本配置文件也可以启动当前大版本数据库(版本兼容性好)，而MySQL一共有707个参数，用到的大概是180个，参数不断增加，就算小版本也会增加参数，大版本之间会有部分参数不兼容情况。参数多引起维护成本加大。比如：PGSQL系统自动设置从库默认只读，不需要人工添加配置，维护简单，MySQL从库需要手动设置参数super_read_only=on，让从库设置为只读。

二、MySQL的优势
1、MySQL数据库查看sql的执行计划更直观易懂。
2、MySQL采用索引组织表，这种存储方式非常适合基于主键匹配的查询、删改操作，但是对表结构设计存在约束。
3、MySQL的优化器较简单，系统表、运算符、数据类型的实现都很精简，非常适合简单的查询操作。
4、MySQL分区表的实现要优于PG的基于继承表的分区实现，主要体现在分区个数达到上千上万后的处理性能差异较大。