1、select行为
-
在 Read Committed 隔离级别下，一个查询（通常是 SELECT）只会看到在查询开始之前已提交的数据。这意味着 SELECT 查询不会返回未提交的数据，也不会看到其他事务在查询执行过程中提交的更改。简单来说，查询始终看到的是查询执行时的“快照”状态。这个快照是数据库在查询开始时的“瞬时状态”，确保查询结果是基于一致的提交数据。
-
如果查询正在执行的事务中执行了更新操作（例如 UPDATE），那么即使这些操作还没有提交，它们仍然对该事务中的其他查询可见。换句话说，事务内的未提交数据是可见的，但前提是这些数据已经在当前事务中执行。
postgres=# commit;
COMMIT
postgres=# begin;
BEGIN
postgres=# select * from t1;
id | name
----+------------
2 | wz
1 | yc
(2 rows)
postgres=# update t1 set name='zw' where id=1;
UPDATE 1
postgres=# select * from t1;
id | name
----+------------
2 | wz
1 | zw
(2 rows)
postgres=# update t1 set name='szs' where id=1;
UPDATE 1
postgres=# select * from t1;
id | name
----+------------
2 | wz
1 | szs
(2 rows)
postgres=# rollback;
ROLLBACK
postgres=# select * from t1;
id | name
----+------------
2 | wz
1 | yc
(2 rows)
postgres=# commit;
COMMIT
可以看到在事务内即使未提交，依然能够看到修改后数据。测试以下在可重复隔离级别下的行为，此时选择使用mysql测试
mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | yc |
| 2 | wz |
+------+------+
2 rows in set (0.00 sec)
mysql> update t1 set name='zw' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | zw |
| 2 | wz |
+------+------+
2 rows in set (0.00 sec)
mysql> update t1 set name='szs' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | szs |
| 2 | wz |
+------+------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | yc |
| 2 | wz |
+------+------+
2 rows in set (0.00 sec)
可以看到在可重复读模式下，本事务内依然可以看到本事务修改未提交的数据。
- Read Committed 允许其他事务在当前查询执行期间提交数据。因此，如果在第一个 SELECT 查询开始后，另一个事务提交了数据，第二个 SELECT 查询可能会看到这个提交的数据变化，甚至是在同一事务内。这是与可重复读存在很大区别，会对mysql用户造成困扰，因为mysql默认隔离级别为可重复读，一个事务内，不论任何时候查询，得到的结果总是一样的。
postgresql测试读已提交
--session 1
postgres=# commit;
COMMIT
postgres=# begin;
BEGIN
postgres=# select * from t1;
id | name
----+------------
2 | wz
1 | yc
(2 rows)
--session 2
postgres=# begin;
BEGIN
postgres=# update t1 set name='aaa' where id=1;
UPDATE 1
--session 1
postgres=# select * from t1;
id | name
----+------------
2 | wz
1 | yc
(2 rows)
--session 2
postgres=# commit;
COMMIT
--session 1
postgres=# select * from t1;
id | name
----+------------
2 | wz
1 | aaa
(2 rows)
postgres=# rollback;
ROLLBACK
postgres=# select * from t1;
id | name
----+------------
2 | wz
1 | aaa
(2 rows)
可以看到postgresql数据库在读已提交模式下，并发事务的提交会影响本事务的前后结果。
mysql可重复读测试
--session 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | yc |
| 2 | wz |
+------+------+
2 rows in set (0.00 sec)
--session 2
mysql> update t1 set name='aaa' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--session 1
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | yc |
| 2 | wz |
+------+------+
2 rows in set (0.00 sec)
--session 2
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
--session 1
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | yc |
| 2 | wz |
+------+------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | aaa |
| 2 | wz |
+------+------+
2 rows in set (0.00 sec)
可以看到mysql在本事务内是无法看到其它事务已提交修改的数据，无论其它事务是否已经提交。只有重新开启事务才可以看到其它事务已经提交的数据。
-
Read Committed 提供了较好的并发性能，因为它允许其他事务提交数据，不会造成过多的锁争用。它能保证查询始终看到已提交的数据，从而避免了脏读问题。
-
因为查询看到的数据可能在不同的查询中发生变化（由于其他事务提交），它不能防止不可重复读（non-repeatable read）问题。即一个事务内的两个查询可能会看到不同的数据，尤其是在并发环境下，可能会导致数据不一致。上述postgresql测试读已提交案例已经证实了这点。
-
Read Committed 是一个适合大多数常规应用程序的隔离级别，特别是在对并发性要求较高的场景下。它在读取数据时保证了一定程度的一致性，同时不会导致过度的性能开销。如果应用场景需要保证事务中的多次读取一致性（即同一事务内的查询结果必须一致），则可能需要考虑 Repeatable Read 或更高隔离级别。但是过高的隔离级别也会导致过多的锁竞争降低并发性能。
-
在 Read Committed 隔离级别下，事务中的 SELECT 查询只能看到查询开始时已经提交的数据。这使得查询能够获得一致性，但同时会受到并发事务提交影响，导致同一事务内的不同查询可能看到不同的数据，既不可重复读问题， 对于需要确保数据完全一致性的应用场景，比如金融交易系统，"读已提交"可能不够严格，需要更高的隔离级别（如可重复读或可串行化）来避免数据不一致。
-
Read Committed 是一种平衡性能与一致性的隔离级别，适用于大多数并发性要求较高的应用场景，但可能无法完全避免并发引起的某些数据不一致问题（如不可重复读）。
2、UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE行为
1.行查找的行为
首先，无论是 UPDATE、DELETE、SELECT FOR UPDATE 还是 SELECT FOR SHARE 命令，它们在查找目标行时都遵循 Read Committed 隔离级别的规则：它们只会看到命令开始时已经提交的行数据。这意味着：
- 如果其他事务在此命令执行期间对行进行了修改（更新或删除），这些修改在命令执行时不会立刻被视为有效数据。
- 命令执行时会检查数据的当前状态，但这些数据只能是已经提交的状态，未提交的数据会被排除在外。
2. 行被其他事务更新（或删除）后的影响
在并发环境下，其他事务可能在执行该命令之前已经修改了目标行的内容。举个例子，假设事务A在执行 UPDATE 操作时锁定了某一行，而事务B随后开始执行对同一行的 UPDATE 或 DELETE 操作。以下是几种可能的情况：
- 如果事务A正在更新该行：
事务B在执行时会发现目标行已经被事务A修改过。这时，事务B会被阻塞，直到事务A提交或回滚。如果事务A提交了更改，那么事务B将会看到事务A提交后的行数据并继续其操作。如果事务A回滚，则事务B可以继续操作原来的数据。
- 如果事务A删除了该行：
如果事务A提交了删除操作，那么事务B在执行时将看到该行已被删除，事务B将会忽略该行。如果事务A回滚，事务B可以继续操作原始行。
3. 第二个事务如何处理更新的数据：
如果第一个事务（事务A）提交了更改，事务B将在其操作时根据 WHERE 子句重新评估目标行的匹配情况。具体来说：
- WHERE 子句重新评估：
如果事务A更新了某行的内容，第二个事务（事务B）会重新检查该行是否仍然符合原先的 WHERE 条件。如果行的数据发生了变化，可能就不再符合查询条件。 - 行更新后的继续操作：
如果行的数据仍然符合事务B的 WHERE 条件，事务B会继续对该行进行操作，并且该操作会应用在行的更新版本上。
4. SELECT FOR UPDATE 和 SELECT FOR SHARE 的特别行为：
对于 SELECT FOR UPDATE 和 SELECT FOR SHARE 这样的查询，它们不仅会锁定目标行，还会返回查询的结果。由于这两种查询是为了确保事务在进行期间不会遭到其他事务的修改，它们会确保在查询开始时，锁定的行是数据的最新版本（如果其他事务已经更新了该行）。
- 锁定更新后的行：
即使目标行在查询开始之后发生了变化，SELECT FOR UPDATE 和 SELECT FOR SHARE 会锁定的是数据的更新版本，而不是原始版本。这确保了在锁定期间，其他事务无法对这些行进行进一步的更改。 - 这种行为对并发控制至关重要
因为它允许事务在进行数据更新或共享锁定时，确保不会受到其他事务并发修改的干扰。
5. 事务顺序与并发控制：
这种行为的核心在于 PostgreSQL 的并发控制和锁定机制。在高并发环境下，当多个事务尝试同时更新同一行时，PostgreSQL 会使用行级锁来协调这些事务的执行，确保数据的准确性和一致性：
- 事务A 更新了某行数据并锁定了该行，其他事务（如事务B）如果在此行上执行 UPDATE 或 DELETE 操作，将会等待事务A结束（提交或回滚）。
- 如果事务A提交了更改，事务B会基于事务A提交后的数据进行操作。如果事务A回滚，则事务B将操作原始数据。
- 事务B可以根据实际情况对行的最新版本进行重新评估，并继续进行更新或删除。
3、ON CONFLICT DO UPDATE行为
INSERT … ON CONFLICT DO UPDATE 是 PostgreSQL 提供的一种非常有用的语法，它可以在执行插入操作时自动处理主键冲突或者唯一约束冲突。当插入的记录与现有记录发生冲突时（比如违反唯一约束或主键约束），该命令会执行指定的更新操作，而不是简单地失败。
1.INSERT with ON CONFLICT DO UPDATE 的基本行为：
当使用 INSERT 命令时，如果数据表中已经存在与待插入行冲突的记录（例如，插入的行的唯一键或主键已经存在于数据库中），ON CONFLICT DO UPDATE 子句就会被触发。在这种情况下：
- 插入操作：如果没有冲突，插入操作会将新行插入到表中。
- 更新操作：如果有冲突，且满足冲突条件（例如，主键或唯一约束冲突），则会执行更新操作，更新现有记录。
Read Committed 隔离级别对这种操作的影响在于，事务内的 INSERT 语句只能看到已经提交的行。这意味着：
- INSERT 语句只会看到在事务开始时已经提交的数据。它不能看到其他并发事务中的未提交数据。
- 如果其他事务的插入或更新操作尚未提交，那么 INSERT 操作在执行时是看不到这些更改的，因此这些未提交的更改不会影响该事务的插入操作。
2.ON CONFLICT DO UPDATE 在并发事务中的行为：
假设有两个并发事务同时尝试插入相同的数据，但这两个事务分别在不同时间内启动。
- 事务A 在执行 INSERT 操作时发现行不存在，因此将其插入到表中。
- 事务B 在同一时间执行 INSERT 操作，但由于事务A正在执行，事务B会检查数据库并发现相同的记录已存在（此时事务A尚未提交其插入操作）。
在 Read Committed 隔离级别下，事务B 在执行时只能看到事务A已提交的数据。如果事务A尚未提交，那么事务B并不会看到事务A的影响。因此，事务B会根据其 ON CONFLICT DO UPDATE 子句执行更新操作，即使事务A的更改尚未对事务B可见。
关键点：如果冲突的行源自一个尚未提交的事务，那么事务B将根据该行当前的数据库状态进行更新，即使该行的数据版本在传统意义上对事务B是“不可见”的。
--session 1
postgres=# begin;
BEGIN
postgres=# select * from t1;
id | name
----+------------
2 | wz
1 | aaa
(2 rows)
postgres=# insert into t1(id,name) values(3,'yc') ON conflict(id) do update set name='s1';
INSERT 0 1
--session 2
postgres=# begin;
BEGIN
postgres=# select * from t1;
id | name
----+------------
2 | wz
1 | aaa
(2 rows)
postgres=# insert into t1(id,name) values(3,'insert2') ON conflict(id) do update set name='update2';
--等待
此时session会话并未提交，按照读已提交原则，session2无法看到session1修改的数据，但session2的会话缺检测到了session1 insert一行数据，那么只能等待session1会话的任务完成。那么既然能检测到其它会话未提交的数据却看不到其它会话未提交的数据，是不是违反读已提交原则呢！！！他感知到了别人未提交的数据。
--session1
postgres=# commit;
COMMIT
postgres=# select * from t1;
id | name
----+------------
2 | wz
1 | aaa
3 | yc
(3 rows)
--session2
INSERT 0 1
postgres=# select * from t1;
id | name
----+------------
2 | wz
1 | aaa
3 | update2
(3 rows)
postgres=# commit;
COMMIT
--session 1
postgres=# select * from t1;
id | name
----+------------
2 | wz
1 | aaa
3 | update2
(3 rows)
当session1会话提交后，session2会话从挂起状态转换为反馈状态(INSERT 0 1)。既session2结束了挂起状态，当session2提交之后session1也可以看到session2会话的修改结果。这里有一个问题，session2会话做的是update操作，然而反馈的(INSERT 0 1)是不是不够准确。正常update应该反馈为(UPDATE 1)。
postgres=# update t1 set name='update3' where id=3;
UPDATE 1
3.UPDATE 子句的行为：
即使冲突的行在 INSERT 命令执行时并不可见（因为它在其他事务中更新或插入，但尚未提交），ON CONFLICT DO UPDATE 仍然会影响该行。这是因为 Read Committed 隔离级别仅保证查询到的数据是事务开始时已经提交的版本，但它并没有排除其他事务的影响。换句话说，即使 INSERT 在开始时看不到其他事务的影响，它仍然可以执行 UPDATE 操作，因为 PostgreSQL 在执行 ON CONFLICT DO UPDATE 时，实际上会锁定该行，并在找到冲突行后更新其数据。
如果 INSERT 语句检测到的冲突源自一个未提交的事务，PostgreSQL 会根据当前数据库的状态来处理冲突，即使该行的更新版本尚未对 INSERT 语句可见。执行 UPDATE 子句时，PostgreSQL 会在操作时处理该冲突，无论该行在其他事务中的未提交版本是否对 INSERT 可见。
4.冲突的来源：
冲突通常源自主键或唯一约束的冲突。当 INSERT 命令试图插入的行违反了现有行的约束时，系统会检查是否有 ON CONFLICT 子句指定如何处理冲突。在 Read Committed 隔离级别下，冲突的行在可见性上可能受到其他事务影响，尤其是当这些冲突行是由其他事务插入或更新时。
5.事务的顺序和锁定机制：
PostgreSQL 在执行 INSERT … ON CONFLICT DO UPDATE 时，依赖行级锁来协调事务间的操作。特别是当多个事务尝试修改同一行数据时，锁机制会确保只有一个事务能够成功执行更新操作：
-
行级锁：即使某个事务对某行数据进行了更新或插入，其他事务仍然可以检查该行的数据，但在 INSERT 操作时会尝试锁定冲突的行。如果该行由另一个未提交事务修改，当前事务会被阻塞，直到冲突的事务提交或回滚。
-
冲突事务的处理：当冲突源自未提交的事务时，PostgreSQL 会依照行的当前状态执行操作。如果冲突行未被提交，PostgreSQL 会根据最新的数据库状态进行处理，确保 ON CONFLICT DO UPDATE 子句执行时能够成功更新数据。
4、INSERT with an ON CONFLICT DO NOTHING行为
使用 ON CONFLICT DO NOTHING 子句的 INSERT 语句可能因为另一个事务的结果（该结果在当前 INSERT 快照中不可见）而导致某行插入失败。
--session1
postgres=# begin;
BEGIN
postgres=# insert into t1(id,name) values(4,'session1') ON conflict(id) do nothing;
INSERT 0 1
--session2
postgres=# begin;
BEGIN
postgres=# insert into t1(id,name) values(4,'session2') ON conflict(id) do nothing;
-挂起
--session1
postgres=# commit;
COMMIT
--session2
INSERT 0 0
postgres=# commit;
COMMIT
postgres=# select * from t1;
id | name
----+------------
2 | wz
1 | aaa
3 | update3
4 | session1
(4 rows)
可以看到冲突之后，session2挂起。当session1提交之后，session2反馈(INSERT 0 0)，insert失败。
5、merge行为
1.MERGE 语句的作用
MERGE 语句（也称为“合并”语句）是一种允许你根据特定条件同时执行 INSERT、UPDATE 和 DELETE 操作的 SQL 命令。它通常用于根据目标表和源表之间的匹配情况，决定如何更新、插入或删除数据。例如，可以指定：
- 如果目标表中的某行与源表中的某行匹配，则执行 UPDATE。
- 如果目标表中没有对应的源表行，则执行 INSERT。
- 如果源表中没有与目标表匹配的行，则执行 DELETE。
MERGE INTO t2
USING t3
ON t2.id = t3.id
WHEN MATCHED THEN
UPDATE SET (name,score) = (t3.name,t3.grand)
WHEN NOT MATCHED BY TARGET THEN
INSERT (id,name,score) VALUES (t3.id,t3.name,t3.grand)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
postgresql17版本支持MATCHED BY语法，测试环境为16.4,还支持，需要换个写法
MERGE INTO t2
USING t3
ON t2.id = t3.id
WHEN MATCHED THEN
UPDATE SET name = t3.name, score = t3.grand
WHEN NOT MATCHED THEN
INSERT (id, name, score) VALUES (t3.id, t3.name, t3.grand);
DELETE FROM t2
WHERE NOT EXISTS (
SELECT 1 FROM t3 WHERE t3.id = t2.id
);
2.MERGE 命令的操作顺序
一个 MERGE 命令可能同时包含多个子命令，例如 INSERT 和 UPDATE。虽然它们在语法上是同时存在的，但并不保证这两个操作都会执行。具体来说：
- 在某些情况下，MERGE 会尝试 UPDATE 操作，但如果目标行在并发操作中被修改，并且 JOIN 条件仍然成立，那么 MERGE 将更新目标表中已更新的行。
- 但是，在 MERGE 执行过程中，如果行的更新使得某个条件不再满足，那么 MERGE 会重新评估后续操作。
3.条件重新评估
MERGE 命令中的多个子操作（如 INSERT、UPDATE、DELETE）是条件性的，并且在行更新后会重新评估这些条件。例如：
- 如果 MERGE 试图执行一个操作（比如 UPDATE），并且某行数据在并发更新的过程中发生了变化，MERGE 会从第一个子命令开始重新评估每个子命令的条件，直到找到符合条件的操作并执行。
- 即使原本匹配的条件在操作列表中排在后面，MERGE 也会重新检查它。
4.并发更新对MERGE的影响
- 如果目标行在并发操作中被更新，且更新后的行仍然满足 JOIN 条件，MERGE 将根据更新后的数据执行相应的操作。这意味着 MERGE 可能会作用于行的最新版本。
- 如果并发更新导致连接条件失败（例如行的某些数据被更改使得不再满足连接条件），那么 MERGE 会跳到 NOT MATCHED BY SOURCE 或 NOT MATCHED BY TARGET 的操作部分，并尝试执行这些条件下的操作，直到找到一个匹配并执行。
5.并发删除对 MERGE 的影响
- 如果目标行在并发操作中被删除，MERGE 会跳过匹配的行并执行 NOT MATCHED BY TARGET 操作。它会检查这些操作的条件，并执行第一个符合条件的操作。
6.唯一性冲突
- 如果 MERGE 尝试执行 INSERT 操作，并且存在唯一约束或索引，而且在并发执行中，另一条插入语句也试图插入相同的值，则会引发唯一性冲突错误。
- MERGE 不会重新评估之前的 MATCHED 条件来避免这个错误，因为它不支持自动回滚并重新评估所有条件。这与 ON CONFLICT DO UPDATE 的行为不同，后者可以在冲突时尝试解决冲突。
