SQL 可以“删除”吗？语句后跟“WHERE NOT IN”语句在同一交易中吗？
如果我有以下 SQL 块（在 SQL SERVER 2008 R2 中）：
BEGIN
BEGIN TRAN
DELETE FROM dbo.fooData
WHERE LastUpdate < DateAdd(hour, -1,GETUTCDATE())
COMMIT
BEGIN TRAN
DELETE FROM dbo.barData
WHERE SessionID NOT IN (SELECT sub.SessionId FROM dbo.fooData sub)
COMMIT
RETURN 0
END
我假设我必须在语句之间执行显式 COMMIT，以便从 fooData 删除的数据显示在第二次删除中。这是正确的吗？理想情况下，我希望所有这些都在一笔交易中。示例：
BEGIN
BEGIN TRAN
DELETE FROM dbo.fooData
WHERE LastUpdate < DateAdd(hour, -1,GETUTCDATE())
DELETE FROM dbo.barData
WHERE SessionID NOT IN (SELECT sub.SessionId FROM dbo.fooData sub)
COMMIT
RETURN 0
END
我担心第二个语句不会获取第一个语句已删除的数据。请注意，返回是因为这是存储过程的一部分。我对级联删除或加入不感兴趣，我对这种方法有些限制。
If I have the following SQL block (in SQL SERVER 2008 R2):
BEGIN
BEGIN TRAN
DELETE FROM dbo.fooData
WHERE LastUpdate < DateAdd(hour, -1,GETUTCDATE())
COMMIT
BEGIN TRAN
DELETE FROM dbo.barData
WHERE SessionID NOT IN (SELECT sub.SessionId FROM dbo.fooData sub)
COMMIT
RETURN 0
END
I am assuming that I have to do an explicit COMMIT between the statements in order for the deleted data from fooData to show up in the second delete. Is this correct? Ideally, I'd want all of this to be in one transaction. Example:
BEGIN
BEGIN TRAN
DELETE FROM dbo.fooData
WHERE LastUpdate < DateAdd(hour, -1,GETUTCDATE())
DELETE FROM dbo.barData
WHERE SessionID NOT IN (SELECT sub.SessionId FROM dbo.fooData sub)
COMMIT
RETURN 0
END
My fear is that the second statement will not pick up the first's deleted data. Note, the return is there because this is part of a stored procedure. I am not interested in cascading deletes or joining, I am somewhat constrained to this method.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
相同的事务/会话可以看到它自己的更改。其他会话不会看到此会话中的这些未提交的事务，
因此您的第二种表单（更广泛的事务）可以安全使用。
The same transaction/session can see it's own changes. Other sessions won't see these uncommitted transactions from this session
So your 2nd form (one wider transaction) is safe to use.
您可以使用第二个示例。
因为您处于同一事务中，所以您正在访问先前操作的信息。
隔离表示其他操作无法访问在尚未完成的事务期间已修改的数据。隔离问题发生在并发事务（多个事务同时发生）的情况下。但是您是同一个事务，因此该事务可以并且将会访问第一个操作的结果。
You can use the second example.
Because you're in the same transaction, you are accessing to the information of the previous operations.
Isolation says that other operations cannot access data that has been modified during a transaction that has not yet completed. The question of isolation occurs in case of concurrent transactions (multiple transactions occurring at the same time). but you are int the same transaction, so that transaction can and will access to the result of first operation.