postgresql|数据库|SQL语句冲突的解决

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核8GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介: postgresql|数据库|SQL语句冲突的解决

前言:

postgresql数据库是比较复杂的一个关系型数据库,而有些时候,即使是简单的插入更新操作也是有很多复杂的机制。

那么,什么是冲突?什么时候会遇到冲突(也就是冲突的常见场景)?如果有冲突我们应该怎么去解决?这些问题我想应该是在此文章中详细说明的。

一,

什么是冲突?

复杂点的说法:数据库的冲突主要是指并发事务对同一数据的读写操作和写写操作。例如,当多个用户同时尝试修改同一行数据时,可能会发生冲突。这种冲突可能会导致数据不一致或数据损坏。

解决这种并发冲突的方法有多种。其中一种是通过锁定整个行数据或整个表数据来防止并发错误,这分别被称为行锁和表锁。行锁的开销小,加锁快,但出现死锁的概率较高;表锁的锁定力度大,发生锁冲突的概率较低,但会导致并发度最低。

另外,乐观并发控制是另一种解决冲突的方法,它假设并发冲突相对较少。与悲观方法(预先锁定数据,然后再进行修改)相反,乐观并发不会进行锁定,但如果数据自查询后发生更改,则会安排数据修改在保存时失败。此并发失败会报告给应用程序,应用程序会进行相应处理,例如可能会对新数据重试整个操作。(这些也就是面试DBA的时候常说的悲观锁,乐观锁这些)

简单来说,就

假设有一个名为"employees"的表,其中包含以下列:id、name、age和salary。现在有两个并发事务,它们都试图更新同一个员工的薪水。

事务1:

BEGIN; UPDATE employees SET salary = 5000 WHERE id = 1; COMMIT;

事务2:

BEGIN; UPDATE employees SET salary = 6000 WHERE id = 1; COMMIT;

在这个例子中,事务1将员工的薪水从4000更新为5000,而事务2也将员工的薪水从4000更新为6000。由于这两个操作是并发执行的,因此最终的结果将是员工的薪水被更新为6000,而不是预期的5000。这就是一个典型的PostgreSQL冲突示例。

我们在使用SQLinsert语句进行插入 操作 ,而目标表中存在这些数据将会导致这些insert 的SQL语句失败, 这个时候,我们就可以说有冲突现象发生了。

例如:

二,

冲突在什么时候出现?

很简单,多用户同一时间执行同一个插入的SQL语句的时候,还一种情形是增量迁移,增量同步数据的时候,第二种情形应该是比较常见的。

三,

冲突的解决

  1. 悲观锁(Pessimistic Locking):悲观锁是一种并发控制策略,它假设多个事务在没有冲突的情况下同时执行的可能性很小,因此在每个事务开始时就会锁定数据行或表,直到事务完成并释放锁。这种机制可以有效地避免冲突,但会导致并发性能下降。
  2. 乐观锁(Optimistic Locking):乐观锁是一种并发控制策略,它假设多个事务在没有冲突的情况下同时执行的可能性很大,只有在提交时才会检查是否存在冲突。如果存在冲突,则事务会被回滚并重新尝试。这种机制可以提高并发性能,但需要额外的逻辑来处理冲突。
  3. 时间戳(Timestamping):时间戳是一种简单的并发控制策略,它在每个记录中添加一个时间戳字段,用于记录该记录的最后修改时间。当更新记录时,比较当前时间戳和记录中的时间戳是否一致,如果不一致则说明有其他事务已经修改了该记录,需要重新执行更新操作。
  4. 分布式锁(Distributed Locking):分布式锁是一种在分布式系统中解决并发冲突的机制,它可以确保只有一个节点能够访问共享资源。常见的实现方式包括基于Redis、Zookeeper等中间件实现的分布式锁。
  5. 重试机制(Retry Mechanism):重试机制是一种常见的解决冲突的方法,当某个操作失败时,会进行多次重试,直到成功为止。这种方法适用于一些非关键性操作,但对于关键性操作可能会导致数据不一致等问题。
  6. 忽视冲突  适用于上面第二节说的增量更新同步,迁移数据库的操作

示例1:

忽视冲突

例如,两张表之间的同步,虽然形式上是全表同步,但希望实现的效果是增量同步,此时,我们需要在INSERT语句后面添加冲突指示:on conflict DO NOTHING; 这样有相同的数据时,检查到了冲突,忽略掉此条数据插入,保持原有数据不变。

INSERT INTO 表1名 SELECT * FROM 表2名 on conflict DO NOTHING;

示例2:

冲突后update更新(部分冲突更新)

---表示在向名为table_name的表中插入一条数据,
---其中包含两个字段:column1和column2。
---如果表中已经存在具有相同column1值的记录,
---则将该记录的column2字段更新为新插入记录的column2值。
INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON CONFLICT (column1) DO UPDATE SET column2 = EXCLUDED.column2;

示例3:

冲突后,根据条件更新字段

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON CONFLICT (column1) DO UPDATE SET column2 = EXCLUDED.column2 WHERE column2 < EXCLUDED.column2;

示例4:

DO NOTHING RETURNING:在发生冲突时不执行任何操作,若不冲突返回插入的行。

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON CONFLICT (column1) DO NOTHING RETURNING *;

示例5:

DO UPDATE SET ... WHERE ... RETURNING:在发生冲突时执行更新操作,并根据条件进行更新,并返回更新的行

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON CONFLICT (column1) DO UPDATE SET column2 = EXCLUDED.column2 WHERE column2 < EXCLUDED.column2 RETURNING *;

未完待续!!!

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
打赏
0
0
0
0
43
分享
相关文章
数据库数据恢复——sql server数据库被加密的数据恢复案例
SQL server数据库数据故障: SQL server数据库被加密,无法使用。 数据库MDF、LDF、log日志文件名字被篡改。 数据库备份被加密,文件名字被篡改。
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。
【赵渝强老师】创建PostgreSQL的数据库
本文介绍了在PostgreSQL中通过SQL命令“create database”创建数据库的方法。首先查询系统目录pg_database以查看现有数据库集合,然后使用“create database”命令创建新数据库,并了解其在$PDATA/base目录下对应的文件夹生成。最后重新查询数据库集合确认创建结果,附带视频讲解便于理解操作步骤及注意事项。
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
数据库编程:在PHP环境下使用SQL Server的方法。
看看你吧,就像一个调皮的小丑鱼在一片广阔的数据库海洋中游弋,一路上吞下大小数据如同海中的珍珠。不管有多少难关,只要记住这个流程,剩下的就只是探索未知的乐趣,沉浸在这个充满挑战的数据库海洋中。
53 16
如何优化SQL查询以提高数据库性能?
这篇文章以生动的比喻介绍了优化SQL查询的重要性及方法。它首先将未优化的SQL查询比作在自助餐厅贪多嚼不烂的行为,强调了只获取必要数据的必要性。接着,文章详细讲解了四种优化策略:**精简选择**(避免使用`SELECT *`)、**专业筛选**(利用`WHERE`缩小范围)、**高效联接**(索引和限制数据量)以及**使用索引**(加速搜索)。此外,还探讨了如何避免N+1查询问题、使用分页限制结果、理解执行计划以及定期维护数据库健康。通过这些技巧,可以显著提升数据库性能,让查询更高效流畅。
JetBrains DataGrip 2025.1 发布 - 数据库和 SQL 跨平台 IDE
JetBrains DataGrip 2025.1 (macOS, Linux, Windows) - 数据库和 SQL 跨平台 IDE
58 0
【赵渝强老师】PostgreSQL的模板数据库
在PostgreSQL中,创建新数据库时,默认通过拷贝`template1`实现。`template1`包含标准系统对象,可自定义以影响新数据库内容;而`template0`是纯净模板,仅含预定义对象且不应修改。视频讲解和代码示例展示了如何查看现有数据库信息及标识字段的作用。 ![图示](https://ucc.alicdn.com/pic/developer-ecology/yub6x2mlkqwck_398ed06397a44c2d9bfbb5ae5c90bbc0.png) [视频链接](https://www.bilibili.com/video/BV1szyfY4EQn)
【赵渝强老师】PostgreSQL的模板数据库
数据库数据恢复—SQL Server报错“错误 823”的数据恢复案例
SQL Server数据库附加数据库过程中比较常见的报错是“错误 823”,附加数据库失败。 如果数据库有备份则只需还原备份即可。但是如果没有备份,备份时间太久,或者其他原因导致备份不可用,那么就需要通过专业手段对数据库进行数据恢复。
本机不安装Oracle客户端,使用PL/SQL Developer连接远程数据库
本机不安装Oracle客户端,使用PL/SQL Developer连接远程数据库
415 0

热门文章

最新文章