部分交易完整性？ [英] Partial Transactional Integrity?
问题描述
我意外地在
客户的实时客户表中运行了没有WHERE子句的UPDATE语句。在说哦屎之后很多次，我b $ b关闭了即席查询工具，希望DB2能看到我的断开连接和
回滚隐式事务。 （要么那个或者DB2已经完成了
，在看到我的断开连接之前更新了。）
所以，有两种可能性：
a）0受影响的行
b）1,398,287行受影响
实际发生的事情令人不安，这是我的问题。当询问
看看有多少行受到影响时，这是
c）832,284行受影响
如何中止UPDATE语句只影响
行的60％。使用现代事务RDMS意味着更新是原子的，
并且所有行都受到影响，或者没有行受到影响。
因此，可能有三个答案可能张贴回应：
a）你一定是弄错了;它不仅影响60％的行。
b）当然DB2中的更新不是原子的。这将导致性能问题在生产环境中无法接受。
c）您的客户必须设置一些禁用所有的数据库选项
交易完整性。并且DB2必须有这个选项，因为否则
可能存在生产中无法接受的性能问题
环境。
所以选择你的回答，或添加自己的。
Ian
i accidentally ran an UPDATE statement without the WHERE clause on a
customer''s live customers table. After saying "oh shit" many many times, i
closed the ad-hoc query tool, hoping DB2 would see my disconnect and
rollback the implicit transaction. (Either that or DB2 would have completed
the updated before it saw my disconnect).
So, there were two possibilities:
a) 0 row(s) affected
b) 1,398,287 row(s) affected
What actually happened is disturbing, and is my question. When queried to
see how many rows were affected, it was
c) 832,284 row(s) affected
How is it that an aborted UPDATE statement would affect only 60% of the
rows. Using a modern transactional RDMS means that the update was atomic,
and either all rows were affected, or no rows were affected.
So, there are probably three answers that could be posted in response:
a) You must be mistaken; it didn''t only affect 60% of the rows.
b) Of course updates in DB2 are not atomic. That would lead to performance
problems that are not acceptable in production environments.
c) Your customer must have set some database option that disables all
transactional integrity. And DB2 has to have this option, becuase otherwise
there could be performance problems that are not acceptable in production
environments.
So pick your answer, or add your own.
Ian
推荐答案
11月10日上午6:26，Ian Boyd < ian.msnews ... @ avatopia.comwrote：
On Nov 10, 6:26 am, "Ian Boyd" <ian.msnews...@avatopia.comwrote:
i意外地在
客户上没有WHERE子句的情况下运行UPDATE语句现场客户表。在说哦屎之后很多次，我b $ b关闭了即席查询工具，希望DB2能看到我的断开连接和
回滚隐式事务。 （要么那个或者DB2已经完成了
更新后才能看到我的断开连接）。
所以，有两种可能性：
a）0排受影响
b）1,398,287排受影响
实际发生的事情令人不安，这是我的问题。当询问
看看有多少行受到影响时，这是
c）832,284行受影响
如何中止UPDATE语句只影响
行的60％。使用现代事务RDMS意味着更新是原子的，
并且所有行都受到影响，或者没有行受到影响。
所以，有可能可以回复的三个答案：
a）你一定是弄错了;它不仅影响60％的行。
b）当然DB2中的更新不是原子的。这将导致性能问题在生产环境中无法接受。
c）您的客户必须设置一些禁用所有的数据库选项
交易完整性。并且DB2必须有这个选项，因为否则
可能存在生产中无法接受的性能问题
环境。
所以选择你的答案，或者添加你自己的答案。
Ian
i accidentally ran an UPDATE statement without the WHERE clause on a
customer''s live customers table. After saying "oh shit" many many times, i
closed the ad-hoc query tool, hoping DB2 would see my disconnect and
rollback the implicit transaction. (Either that or DB2 would have completed
the updated before it saw my disconnect).
So, there were two possibilities:
a) 0 row(s) affected
b) 1,398,287 row(s) affected
What actually happened is disturbing, and is my question. When queried to
see how many rows were affected, it was
c) 832,284 row(s) affected
How is it that an aborted UPDATE statement would affect only 60% of the
rows. Using a modern transactional RDMS means that the update was atomic,
and either all rows were affected, or no rows were affected.
So, there are probably three answers that could be posted in response:
a) You must be mistaken; it didn''t only affect 60% of the rows.
b) Of course updates in DB2 are not atomic. That would lead to performance
problems that are not acceptable in production environments.
c) Your customer must have set some database option that disables all
transactional integrity. And DB2 has to have this option, becuase otherwise
there could be performance problems that are not acceptable in production
environments.
So pick your answer, or add your own.
Ian
似乎如果更新是（当你比如说）一个单独的SQL语句，
DBMS的基本ACID保证会提供它完全执行或者根本不运行
。我会投票给DBMS错误。
BEA Systems的Joe Weinstein
或
It seems that if the update was (as you say) a single SQL statement,
the DBMS''s basic ACID guarantees would provide that it either execute
completely or not at all. I would vote for a DBMS bug.
Joe Weinstein at BEA Systems
or
如果你没有直接通过IBM提供的界面执行SQL，
你有另一层软件可能会导致你看到的b
。我也会在那里检查。
因为这是一个生产表，我会假设恢复记录是活动的b $ b。如果您大致了解何时开始更新
（时间戳），您可以尝试使用时间点恢复到离线
数据库来恢复当前数据丢失。我已经在一些
的场合用过这个来解决你遇到的完全相同的问题。
Phil Sherman
Ian Boyd写道：
If you are not executing SQL directly through an IBM supplied interface,
you have another layer of software that possibly could cause what you
are seeing. I''d also check there.
Since this is a production table, I''d assume that recovery logging is
active. If you know approximately when you started the update
(timestamp), you can try using a point-in-time recovery to an offline
database to recover the data that was lost. I''ve used this on a number
of occasions to fix exactly the same problem you''ve run into.
Phil Sherman
Ian Boyd wrote:
i意外地在
客户的实时客户表中运行了没有WHERE子句的UPDATE语句。在说哦屎之后很多次，我b $ b关闭了即席查询工具，希望DB2能看到我的断开连接和
回滚隐式事务。 （要么那个或者DB2已经完成了
，在看到我的断开连接之前更新了。）
所以，有两种可能性：
a）0排受影响
b）1,398,287排受影响
实际发生的事情令人不安，这是我的问题。当询问
看看有多少行受到影响时，这是
c）832,284行受影响
如何中止UPDATE语句只影响
行的60％。使用现代事务RDMS意味着更新是原子的，
并且所有行都受到影响，或者没有行受到影响。
所以，有可能可以回复的三个答案：
a）你一定是弄错了;它不仅影响60％的行。
b）当然DB2中的更新不是原子的。这将导致性能问题在生产环境中无法接受。
c）您的客户必须设置一些禁用所有的数据库选项
交易完整性。并且DB2必须有这个选项，因为否则
可能存在生产中无法接受的性能问题
环境。
所以选择你的答案，或添加你自己的答案。
Ian
i accidentally ran an UPDATE statement without the WHERE clause on a
customer''s live customers table. After saying "oh shit" many many times, i
closed the ad-hoc query tool, hoping DB2 would see my disconnect and
rollback the implicit transaction. (Either that or DB2 would have completed
the updated before it saw my disconnect).
So, there were two possibilities:
a) 0 row(s) affected
b) 1,398,287 row(s) affected
What actually happened is disturbing, and is my question. When queried to
see how many rows were affected, it was
c) 832,284 row(s) affected
How is it that an aborted UPDATE statement would affect only 60% of the
rows. Using a modern transactional RDMS means that the update was atomic,
and either all rows were affected, or no rows were affected.
So, there are probably three answers that could be posted in response:
a) You must be mistaken; it didn''t only affect 60% of the rows.
b) Of course updates in DB2 are not atomic. That would lead to performance
problems that are not acceptable in production environments.
c) Your customer must have set some database option that disables all
transactional integrity. And DB2 has to have this option, becuase otherwise
there could be performance problems that are not acceptable in production
environments.
So pick your answer, or add your own.
Ian
Ian Boyd写道：
Ian Boyd wrote:
i意外地在
客户的实时客户表中运行了没有WHERE子句的UPDATE语句。在说哦屎之后很多次，我b $ b关闭了即席查询工具，希望DB2能看到我的断开连接和
回滚隐式事务。 （要么那个或者DB2已经完成了
更新，直到它看到我的断开连接）。
i accidentally ran an UPDATE statement without the WHERE clause on a
customer''s live customers table. After saying "oh shit" many many times, i
closed the ad-hoc query tool, hoping DB2 would see my disconnect and
rollback the implicit transaction. (Either that or DB2 would have completed
the updated before it saw my disconnect).
DB2是事务性的，我不会购买那个空间中的bug的概念。
很可能UPDATE不是'' ta简单更新。
也许一个提交计数的光标可以记录下来？
干杯
Serge
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
WAIUG会议
http：// www.iiug.org/waiug/present/Fo...Forum2006.html
这篇关于部分交易完整性？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
