单个“提交”没有“开始交易”的语句
我正在使用 web2py dal 和 mysqldb 适配器来连接到 mysql 服务器。
我的问题：
- 之后需要一个“提交”而不需要引导“开始事务”
- 为什么在“set autocommit=0”执行“选择” 如果 autocommit=0 则语句开始事务？
服务器信息： innodb引擎 自动提交=1（默认值） tx_isolation=可重复读取（默认值）
general_log：
100356 10:00:00 123456 Connect [email protected] on dummydb
123456 Query SET NAMES 'utf8'
123456 Query SET AUTOCOMMIT = 0
123456 Query COMMIT
123456 Query SET FOREIGN_KEY_CHECKS=1
123456 Query SET sql_mode='NO_BACKSLASH_ESCAPES'
i'm using web2py dal with mysqldb adapter to connect to mysql server.
my question:
- why does it need a single "commit" without leading "begin transaction" after "set autocommit=0"
- does "select" statement begin a transaction if autocommit=0?
server info:
innodb engine
autocommit=1 (default value)
tx_isolation=repeatable-read (default value)
general_log:
100356 10:00:00 123456 Connect [email protected] on dummydb
123456 Query SET NAMES 'utf8'
123456 Query SET AUTOCOMMIT = 0
123456 Query COMMIT
123456 Query SET FOREIGN_KEY_CHECKS=1
123456 Query SET sql_mode='NO_BACKSLASH_ESCAPES'
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
呃...因为你禁用了自动提交？？？
这是一个很好的解释：
换句话说：
“事务”不一定只是“将多个语句作为一个原子实体执行”
自动提交给你一个语句 == 1 个事务的“幻觉”
事实上，“automcommit off”给你“一个语句 ==” 0 个事务”
来自同一链接：
Uh ... because you disabled auto-commit????
Here's a good explanation:
In other words:
"transactions" aren't necessarily only about "executing multiple statements as one atomic entity"
autocommit gives you the "illusion" of one statement == 1 transaction
In fact, "automcommit off" gives you "one statement == 0 transactions"
From the same link:
在标准 SQL 中，您（几乎）总是处于事务内部。如果执行 COMMIT 或 ROLLBACK，则下一条语句将启动一个新事务。因此，如果您希望更改生效，则必须提交它们。
如果启用了 AutoCommit，则每个语句都是一个单例事务，如果成功则自动提交，如果失败则回滚。
当您关闭自动提交时，您必须提交以确保数据库更改生效。
一些 DBMS 在这个主题上有细微的变化。
特别是，Informix 有一种数据库模式，其中 AutoCommit 处于打开状态，直到您执行显式的 BEGIN [WORK]；那么您就处于事务中，直到您执行 COMMIT [WORK] 或 ROLLBACK [WORK]。它还有一个“MODE ANSI”，其行为与标准 SQL 中相同；它有一个无日志模式，根本没有事务。
话虽如此，您所显示的陈述并不是不言而喻的，真正需要交易支持。它往往是 DML 语句（SELECT、INSERT、DELETE、UPDATE、MERGE 等），有时是需要事务支持的 DDL 语句。某些 DBMS 不允许回滚 DDL 语句（Oracle）；其他人则这样做（Informix）。
In Standard SQL, you are (almost) always inside a transaction. If you do a COMMIT or ROLLBACK, the next statement starts a new transaction. Therefore, if you want changes to take effect, you have to COMMIT them.
If you have AutoCommit on, then each statement is a singleton transaction, and is automatically committed if it is successful, or rolled back if it fails.
When you turn AutoCommit off, you have to COMMIT to ensure database changes take effect.
Some DBMS have minor variations on this theme.
Informix, in particular, has one database mode where AutoCommit is on until you do an explicit BEGIN [WORK]; then you're in a transaction until you do COMMIT [WORK] or ROLLBACK [WORK]. It also has a 'MODE ANSI' which behaves as in standard SQL; and it has an unlogged mode where there are no transactions at all.
With all that said, the statements you show are not self-evidently ones that really need transactional support. It tends to be DML statements (SELECT, INSERT, DELETE, UPDATE, MERGE, etc) and sometimes DDL statements that need transactional support. Some DBMS do not allow DDL statements to be rolled back (Oracle); others do (Informix).