在运行更新语句时插入语句 – 当使用NOT EXISTS时,插入的行会受影响吗?

huangapple go评论52阅读模式
英文:

Insert statements while an update statement is running - will the inserted rows be affected when NOT EXISTS is used?

问题

在下面的语句中,如果我的更新语句正在运行,同时新的行被插入,是否有可能新插入的行会受到更新语句的影响?

  UPDATE	x 
  SET     x.OpenCloseStatus = 'CLOSED'
  FROM	dbo.OrderWOrkflow x
  WHERE	1=1

  AND NOT EXISTS
		(
		SELECT	MAX(z.OrderWorkflowID)
		FROM	dbo.OrderWorkflow  z
		GROUP BY
				z.HaulRequestOrderID
		HAVING 
				MAX(z.OrderWorkflowID) = x.OrderWorkflowID
		)

如果“SELECT MAX(...”子查询先运行,然后主要更新发生。如果在子查询和更新之间插入了行,它们会被意外地标记为“CLOSED”吗?

我希望如果在更新过程中插入了行,它们不会受到影响。

英文:

In the statement below, if my update statement is running and at the same time new rows are inserted. Is there a possibility of the newly inserted rows being affected by the update statement?

  UPDATE	x 
  SET     x.OpenCloseStatus = 'CLOSED'
  FROM	dbo.OrderWOrkflow x
  WHERE	1=1

  AND NOT EXISTS
		(
		SELECT	MAX(z.OrderWorkflowID)
		FROM	dbo.OrderWorkflow  z
		GROUP BY
				z.HaulRequestOrderID
		HAVING 
				MAX(z.OrderWorkflowID) = x.OrderWorkflowID
		)

If the "SELECT MAX(..." subquery runs first and then then main update happens. If there are rows inserted in between the subquery and the update, will they be inadvertently marked as "CLOSED" by the update?

I'm hoping that if rows are inserted in the middle of the update, they will not be affected.

答案1

得分: 0

在您描述的情况下,正在运行UPDATE语句的同时插入新行,新插入的行不会受到UPDATE语句的影响。

原因是数据库中的每个事务都在与其他事务隔离的情况下运行。当UPDATE语句开始时,它会创建一个事务,锁定需要更新的行。这会阻止其他事务在UPDATE事务完成并提交之前修改这些行。

另一方面,新插入的行将成为单独的事务的一部分。它们不会被UPDATE事务锁定,也不会受到UPDATE语句所做更改的影响。

因此,如果在UPDATE语句中间插入行,它们不会被意外标记为"关闭",UPDATE语句只会影响在UPDATE事务开始之前存在的行。

英文:

In the scenario you described, where an UPDATE statement is running simultaneously with the insertion of new rows, the new rows inserted will not be affected by the UPDATE statement.

The reason is that each transaction in the database operates in isolation from other transactions. When the UPDATE statement starts, it creates a transaction that locks the rows it needs to update. This prevents other transactions from modifying those rows until the UPDATE transaction is complete and committed.

On the other hand, the new rows inserted will be part of separate transactions. They will not be locked by the UPDATE transaction and will not be affected by changes made by the UPDATE statement.

Therefore, if rows are inserted in the middle of the UPDATE statement, they will not be accidentally marked as "CLOSED" by the UPDATE statement. The UPDATE statement will only affect rows that existed before the UPDATE transaction began.

huangapple
  • 本文由 发表于 2023年6月2日 06:02:29
  • 转载请务必保留本文链接:https://go.coder-hub.com/76386003.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定