doulin1867
2014-04-08 20:31
浏览 112
已采纳

Atomic是如何给出多个连接的Mysql事务?

I checked related questions, the answer is kinda "yes, very atomic".

But Im going more specific, because those questions were not that specific and so the answers:

  1. MySQL/INNODB
  2. Several connections at the "same" time
  3. A transaction probably WRITING the same tables (even more, the same queries).
  4. Only one database
  5. No lock table will be done at all
  6. . No nested transactions
  7. No memcache or any othe similar system, everything as simple as possible.
  8. I believe we are using "serializable isoloation, not low level"

In this scenario, are we garanteed by Mysql engine that data integrity will be mantained, or going to specific cases:

  • Those "at the same time " writings will be queued?

  • Rollback in fact will work as spected.

  • readings at "the same time" will find a consistency int the state for those writings?

My simple concerns is that if "commits" and "rollbacks" inside transcations of different connections are atomic or they just make a mess :-)

Thanks.

图片转代码服务由CSDN问答提供 功能建议

我检查了相关问题,答案是有点“是的,非常原子的”。< / p>

但我更具体,因为那些问题并不是那么具体,所以答案是:

  1. MySQL / INNODB
  2. “相同”时间的多个连接
  3. 一个事务可能写相同的表(甚至更多,相同的 查询)。
  4. 只有一个数据库
  5. 根本不会执行锁定表
  6. 。 没有嵌套事务
  7. 没有内存缓存或任何其他类似系统,一切都像 possible一样简单。
  8. 相信我们正在使用“可序列化 isoloation,而不是低级“

    在这种情况下,我们是否通过Mysql引擎保证数据完整性将被保留,或者转向特定情况:

    • 那些“同时”的作品将排队?

    • 事实上,回滚将按照规定进行 。

    • “同时”读数会在 撰写文章的状态中找到一致性吗?

      我的简单问题是,如果在不同连接的交易中“提交”和“回滚”是原子的,或者它们只是弄得一团糟: - )

      谢谢。< / p>

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dszdiavv474681 2014-04-08 21:06
    已采纳

    The "commit" will end a transaction.

    It's the transaction isolation level that really determines whether

    • all statements executed in the context of a transaction are based on a consistent point-in-time snapshot at the beginning of the transaction (REPEATABLE READ and SERIALIZABLE), or whether

    • each statement within the transaction will see changes committed by other transactions ala Oracle (READ COMMITTED), or whether

    • each statement will see changes made by other transactions that are not yet committed ala SQL Server dirty reads (READ UNCOMMITTED)

    To answer your questions:

    The "at the same time writings" will be applied when locks can be obtained. If no other session holds incompatible locks, then locks are obtained, and changes can be applied.

    Rollback will work as expected, that is, a transaction ended with a ROLLBACK statement will revert any changes that were applied, the locked rows will be returned to the state they were in at the beginning of the transaction, and locks will be released. (This also includes any DML changes applied by triggers.) Note that this applies ONLY to InnoDB. Any changes applied to MyISAM tables will have already been committed as if by an Oracle-style autonomous transaction.

    The "at the same time reads" will each be from a consistent snapshot (with REPEATABLE READ or SERIALIZABLE transaction isolation level.)

    NOTE The "commits" and "rollbacks" don't happen "inside transactions", they CONCLUDE a transaction, they mark the end of a transaction. The consistent snapshot at the start of the transaction is gone; the next transaction will get its own consistent snapshot. Those operations aren't best described as "atomic", but they won't, in and of themselves, make a mess.

    It's the InnoDB locking mechanism that prevents conflicting changes. If two simultaneous sessions are attempting to make a change to the same row (one session overwriting changes made by the other), at least one of the transactions will wait to obtain the lock held by the other transaction. When the lock is released by the first transaction, the second transaction can obtain the lock, and proceed with its changes; it is free to overwrite changes just made by the other session.

    Whether things wind up as a mess really depends on the design of the transactions, and what qualifies as a mess.

    点赞 打赏 评论

相关推荐 更多相似问题