英文:
How to lock a source table to block inserts during merge and backup?
问题
执行合并操作的代码,确保备份和清理源数据表,将源表锁定以防止更改(插入、删除等)。所用表名为poc_a、poc_b、poc_c,结构相同,包括[id, w, p, TimeStamp]这四列。合并操作基于w和p列进行分区,并保留最新的一行数据。匹配时更新目标表,不匹配时插入新行。备份源表数据到poc_b,然后清空poc_a表。
英文:
To perform a merge from a source table which gets updated continuously (ex: like every 1min) as well as copying the data from the source table to a backup table and then to delete the source table. Basically the data on which merge occurred should be backed up and cleaned up from source. The source table should be locked so that no changes (inserts,deletes etc) are made.
Assume the tables are called poc_a, poc_b, poc_c all with same structure [id,w,p,TimeStamp<-- 4 columns]
I want to do something like this:
begin tran merg
MERGE poc_c as Target
USING (
select id,w,p,[TimeStamp] from (
SELECT id,w,p,[TimeStamp],ROW_NUMBER() OVER (PARTITION BY w, p
ORDER BY [TimeStamp] DESC) AS RN
FROM poc_a with (tablockx)
) DUPL_FILTER -- filter first row from N duplicate rows
WHERE RN = 1
) Source
ON
Source.w = Target.w AND Source.p = Target.p
WHEN MATCHED THEN
update set
Target.[id] = Source.[id],
Target.[w] = Source.[w],
Target. = Source.
,
Target.[TimeStamp] = Source.[TimeStamp]
WHEN NOT MATCHED BY Target THEN
insert ([id],[w],
,[TimeStamp]) values(Source.id,Source.w,Source.p,Source.[TimeStamp])
;
--WAITFOR DELAY '00:00:15'
insert into poc_b select * from poc_a --will there be data loss here or below truncate?
truncate table poc_a
commit tran merg
答案1
得分: 1
你可以在开始事务前添加这行代码:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
这将使合并仅读取在合并事务开始之前已提交的数据,这不会锁定用于插入的表,但合并事务在其他事务提交之前不会获取这些更改。
如果你想完全锁定你的事务使用的表,你可以执行:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
这是可能的最高隔离级别,将完全锁定表,直到你的合并事务被提交。
英文:
You could add this line before begin transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITED;
Which will make merge read ONLY data that was commited before the merge transaction started, this will not lock the table for insterts, but the merge transaction will not acquire those changes before other transactions are commited.
If you want to completely lock access to a table that your transaction is using you could do
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Which is the highest level of isolation possible and will completely lock table for other transactions until your merge transaction is commited.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论