Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The question is about queries that are not wrapped in 'begin-commit' block, but about plain inserts and updates that are atomic in PostgreSQL, MySQL (innodb engine at least). So how is this implemented internally?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

A transaction is started for each statement that occurs outside of an explicit transaction block. Whether a commit is automatically issued following the statement is dependent on the RDBMS configuration. MySQL has the autocommit option, SQL Server has IMPLICIT_TRANSACTIONS, PostgreSQL is always auto commit.

PostgreSQL:

In the standard, it is not necessary to issue START TRANSACTION to start a transaction block: any SQL command implicitly begins a block. PostgreSQL's behavior can be seen as implicitly issuing a COMMIT after each command that does not follow START TRANSACTION (or BEGIN), and it is therefore often called "autocommit". Other relational database systems might offer an autocommit feature as a convenience.

InnoDB:

In InnoDB, all user activity occurs inside a transaction. If autocommit mode is enabled, each SQL statement forms a single transaction on its own. By default, MySQL starts the session for each new connection with autocommit enabled, so MySQL does a commit after each SQL statement if that statement did not return an error.

SQL Server:

SQL Server operates in the following transaction modes.

Autocommit transactions - Each individual statement is a transaction.

Explicit transactions - Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.

Implicit transactions - A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.

share|improve this answer
    
thx Mark, things made clear. But one question about MySQL: what is autocommit is on and I wrap several queries in transaction? Wrapped transaction are not possible in MySQL as far as I'm concerned –  Zapadlo Apr 12 '13 at 9:51
2  
I'm not sure what you mean. START TRANSACTION disables autocommit in MySQL. –  Mark Storey-Smith Apr 12 '13 at 11:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.