single "commit" statement without "begin transaction"

Question

i'm using web2py dal with mysqldb adapter to connect to mysql server.

my question:

1. why does it need a single "commit" without leading "begin transaction" after "set autocommit=0"
2. 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'

Answer 1

Uh ... because you disabled auto-commit????

Here's a good explanation:

http://rpbouman.blogspot.com/2007/02/mysql-transactions-and-autocommit.html

With autocommit enabled, every statement is wrapped within its own transaction. Successful execution of a statement is implicitly followed by a COMMIT, and the occurrence of an error aborts the transaction, rolling back any changes.

By default, autocommit is enabled in MySQL.

In other words:

1. "transactions" aren't necessarily only about "executing multiple statements as one atomic entity"

2. autocommit gives you the "illusion" of one statement == 1 transaction

3. In fact, "automcommit off" gives you "one statement == 0 transactions"

From the same link:

... The whole point of having autocommit off is that you can issue multiple statements and commit them all at once.

Answer 2

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).