我读过很多资料，他们说在数据库中实现可序列化的方法之一是使用两阶段锁定。但是我真的不明白在Jim Gray 的这个例子中两阶段锁定如何确保可序列化。
一个例子是我们在数据库中有两行，一行的值为white，另一行的值为black。我有两笔交易：
- TX1 会将白色的值更新为黑色
- TX2 会将黑色的值更新为白色
如果TX1和TX2同时执行，则TX1获取值为white的行锁，TX2获取值为black的行锁。所以没有锁定冲突，最终，值被交换了。
SQL Server 中的已提交读快照和快照隔离级别取消了大多数锁定，除了一个：写入者仍然锁定其他写入者。
文档蹑手蹑脚地说了这么多，随后没有记录任何其他非常有趣的内容：
它真的只是一个被独占锁定的修改行吗？或者它也可以是不相关的行（例如，在索引中相邻）或页面？
我确实查看了锁，sys.dm_tran_locks并且我只在未提交的事务期间看到修改行上的排他锁 - 页面仅锁定为IX.
我还测试了两个事务是否可以在一个非常小的表中的两个未提交事务期间同时修改两个不同的行，该表可能适合一页并且效果也很好。
如果确实只有修改的行被独占锁定，那么如果它确保没有两个连接同时写入同一行，那么这将为具有对数据库的独占访问权限的应用程序提供无锁写入的保证。
这在我想到的场景中是可能的——但是如果页面锁定起作用，几乎没有办法做这样的事情，因为无法预测哪些行会受到影响。
由于VACUUM FULL锁表，在我们的生产环境中对我们来说是不可接受的。
是否可以只在生产系统上运行VACUUM而从不运行？VACUUM FULL
是否VACUUM使新空间可重复使用INSERT？
所以这是永远挂起的查询：
ALTER TABLE tasks
ADD COLUMN in_progress BOOLEAN NOT NULL DEFAULT FALSE;
该表的tasks行数少于 20,000 行，每 5 分钟左右查询一次。
我检查了pg_stat_activity10 次，它从未显示任何锁定表的查询：
SELECT *
FROM pg_stat_activity
WHERE query LIKE '%tasks%';
--- No results
我尝试了真空吸尘器，但没有帮助：
VACUUM (VERBOSE, ANALYZE) tasks;
我还尝试添加没有约束和默认值的列，我希望它在这样的表上几乎是即时的，但是当我停止查询时它运行了 1 分钟：
ALTER TABLE tasks
ADD COLUMN in_progress BOOLEAN;
我在同一时间段内对另一个表（约 1000 行）运行了查询，它是即时的。
任何想法？
PostgreSQL 11.13
通过 DBeaver 执行的查询（我多次无效/重新连接以防万一）。
我最近偶然发现了SELECT ... FOR UPDATE与(LEFT) JOIN. 这是表结构以及重现结果的场景：
表结构
create table counter (
counter_id serial primary key,
current_counter int not null default 0
);
create table diff (
diff_id serial primary key,
diff_increase int not null default 0,
counter_id serial references counter(counter_id) not null
);
设想
有两个并发事务 A 和 B，都执行相同的查询。
- 事务 A 以该查询开始，并且能够获取锁并继续。
select *
from counter
left join diff on counter.counter_id = diff.counter_id
where counter.counter_id = 1
order by diff.diff_id desc
limit 1
for update of counter
;
事务 B 尝试执行相同的查询，但无法获取锁，因此等待。
事务 A 将执行以下查询：
update counter
set current_counter = current_counter + 100
where counter_id = 1
;
insert into diff (diff_increase, counter_id) values (100, 1)
;
commit;
- 事务 A 已完成，数据库的状态现在应如下所示：
-- counter table
counter_id | current_counter
------------------------------
1 | 200
-- diff table
diff_id | diff_increase | counter_id
--------------------------------------
1 | 50 | 1
2 | 50 | 1
3 | 100 | 1
预期行为
事务 B 看到更新的计数器 ( current_counter = 200) 和最后的差异 ( diff_id = 3)。
实际行为
事务 B 继续使用counter表的新状态（意思是current_counter = 200），而diff_id仍然是 2 而不是 3。
这种行为是预期的吗？如果是这样，为什么同一个查询会看到数据库的不同状态？这不违反READ COMMITTED隔离级别的保证吗？
在 Linux 上使用 PostgreSQL 13 进行测试。
我有一个需要每天更新的 SQL 表。在更新发生时，可能有也可能没有对该表的查询。它大约有 500,000 行。
当更新表的作业与针对它的查询同时运行时，我们遇到了锁定冲突的问题。
所以我重写了更新表的过程如下：
ALTER procedure [dbo].[Table_Generate] as
declare @d datetime = getdate(), @c as int
--Check temp tables
IF OBJECT_ID('tempdb..#final') IS NOT NULL
DROP TABLE #final
IF OBJECT_ID('tempdb..#base') IS NOT NULL
DROP TABLE #base
--Get source data from linked server
select
ID,
Reference,
StartDate,
EndDate,
Description,
SomeCode
into #base
from [LinkedServer].[Database].dbo.[A_View]
--Generate row_hash
select
ID,
Reference,
StartDate,
EndDate,
Description,
SomeCode,
hashbytes('SHA2_256',(
select
ID,
Reference,
StartDate,
EndDate,
Description,
SomeCode
from #base sub where sub.ID = main.ID for xml raw)) as row_hash
into #final
from #base main
select @c = count(*) from #final
if @c >0 begin
merge [The_Table_Staging] as target
using #final as source
on source.ID = target.ID
--New rows
when not matched by target then
insert ( RunDate,
ID,
Reference,
StartDate,
EndDate,
Description,
SomeCode,
Row_Hash
) values (
@d,
source.ID,
source.Reference,
source.StartDate,
source.EndDate,
source.Description,
source.SomeCode,
source.row_hash)
--Existing changed rows
when matched and source.row_hash != target.row_hash then update set
target.RunDate = @d
,target.Reference = source.Reference
,target.StartDate = source.StartDate
,target.EndDate = source.EndDate
,target.Description = source.Description
,target.SomeCode = source.SomeCode
,target.row_hash = source.row_hash
--Deleted rows
when not matched by source then delete;
--Existing unchanged rows
update [The_Table_Staging] set RunDate = @d where RunDate != @d
--Commit changes
begin transaction
exec sp_rename 'The_Table_Live', 'The_Table_Staging_T'
exec sp_rename 'The_Table_Staging', 'The_Table_Live'
exec sp_rename 'The_Table_Staging_T', 'The_Table_Staging'
commit transaction
end
这个想法是为了减少不必要的行更新，同时也尽量减少对活动表的锁定。我不太喜欢重命名表，但更新/插入需要 5-10 秒，而表重命名几乎是即时的。
所以我的问题是：这种方法可以吗，和/或我可以改进它吗？
谢谢！
编辑以回应 JD：
嗨，JD，请不要道歉 - 我在这里接受建设性的批评。
- 我不知道
MERGE有问题。我自己从来没有遇到过问题，但是谢谢 - 我可能会将这部分重写为单独的
INSERT/UPDATE/DELETE语句 - 我通常同意。我这样做的原因是，如果我
TRUNCATE/INSERT在那个时候从 staging 开始，它需要 6-10 秒，而sp_rename需要不到一秒。所以锁定表的时间更少 - 这不会影响表锁定，因为它首先将数据放入临时表。我别无选择，只能使用链接服务器或 SSIS，在这种情况下，我更喜欢链接服务器将所有 SQL 保存在一个地方
- 我总是使用
XML而不是CONCAT因为否则 'a', 'bc' 的哈希值与 'ab', 'c' 相同，这是不正确的
从登台到填充活动表的所有处理都很好 - 我只想尽量减少最终活动表的锁定时间，
我尝试复制这里给出的场景：https ://www.sqlskills.com/blogs/paul/the-curious-case-of-the-bulk_operation-lock-during-a-heap-nolock-scan/
创建了 2 个表 - 带有聚集索引的 table1，没有任何索引的 table2。
当我在 table1 上编写选择查询with (lock)时，应用了 Sch-S 锁。同样，当我在 table2 上编写相同的查询时，它也有一个 Sch-S 锁。
两种情况都没有应用 S 锁，如上面的链接所示。
要检查锁，我使用以下查询：
SELECT * FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
AND resource_associated_entity_id = OBJECT_ID(N'dbo.table1');
是否使用 nolock 块写入器从堆（没有聚集索引的表）中选择数据？
当我有一个包含多个查询的事务时，我可以通过使用waitfor和检查dm_tran_locks.
但我不能将一个查询“暂停”一半。具体来说，我想知道这个查询将如何持有锁：
update my_table set column1=new_value
where column2=filter_value
这会从一开始就需要更新锁定吗？或者它会在确定指定的行之前使用共享锁where，然后请求 U 锁？
我试图弄清僵局。有问题的表mytb仅用于以下查询：
delete mytb with (uplock,holdlock) where key_value=@value
通过 sp_executesql 调用。后面提到的两个进程都使用相同的代码，除了键值。
我正在使用跟踪标志 1222，这是服务器日志中的资源列表部分：
resource-list
objectlock lockPartition=0 objid=1433553235 subresource=FULL dbid=16 objectname=mydb.dbo.mytb id=lock5620a3480 mode=IX associatedObjectId=1433553235
owner-list
owner id=process4bd94c8 mode=IX
waiter-list
waiter id=process4c85288 mode=X requestType=convert
objectlock lockPartition=0 objid=1433553235 subresource=FULL dbid=16 objectname=mydb.dbo.mytb id=lock5620a3480 mode=IX associatedObjectId=1433553235
owner-list
owner id=process4c85288 mode=IX
waiter-list
waiter id=process4bd94c8 mode=X requestType=convert
我的理解如下：两个进程（让我们从它们的最后两个 id 字符中称它们为“c8”和“88”）设法在表上获得相同的 IX 锁，然后尝试变成 X 锁以删除必要的行，但一个进程阻塞了另一个进程。
我的理解正确吗？如果是，为什么进程共享 IX 锁？系统是否不应该拒绝访问 IX 锁来限制竞争条件的进程，这将导致第一个进程首先完成，然后第二个进程可以开始？
发表评论补充：在挖掘更多时，我发现where条件上的索引不存在，而我希望它是聚集的主键。没有这个索引会不会是死锁的原因？
我们注意到这种奇怪的情况，即不返回记录的查询将在指定时等待锁定WITH(UPDLOCK)。
查询很简单
SELECT primaryKey FROM someTable WITH(UPDLOCK)
WHERE columA = 'A'
AND columnB BETWEEN 6 AND 8
AND columnC != 'C' ;
运行此查询的系统使用隔离级别read uncommitted，我们无法更改。
有没有一种方法，例如提示，不会让这个等待，但如果它会产生结果，仍然会锁定记录？
我注意到索引使用在其中起作用，因为其他查询没有这种行为。但遗憾的是，定义索引也不在可能的范围内。
我想出的唯一解决方案是IF EXISTS在执行实际查询之前在其中抛出一个，但由于竞争条件，它仍然可能发生。
类似的问题已在此处得到解答：为什么 UPDLOCK 会导致 SELECTs 挂起（锁定）？
如果没有索引来定位要锁定的行，所有测试的行都将被锁定，并且对符合条件的行的锁定将一直保持到事务完成。
但我不认为那里提供的答案适用于我的问题版本，因为查询不会返回行来“限定”。
遗憾的是，我无法访问该系统。我们只能对某些表运行查询。
目的是锁定和读取记录（如果存在）并最终更新它。如果丢失，请插入。
UPDLOCK之所以使用它，是因为在我的理解中，当锁定未提交的读取隔离时，这是正确的。
该表有一个主键，但未在 where 子句中使用。