The question is about queries that are not wrapped in 'begin-commit' block, but about plain inserts and updates that are atomic in postgres, mysql (innodb engine at least). So how is this implemented internally?
3
-
1"Plain" inserts and updates run in the context of an implicit transaction (an implied BEGIN...COMMIT block). There's really no difference.Mike Sherrill 'Cat Recall'– Mike Sherrill 'Cat Recall'2013-07-21 20:49:05 +00:00Commented Jul 21, 2013 at 20:49
-
i guess you should have written this as an answer, not comment -- it would be acceptedVadim Samokhin– Vadim Samokhin2013-07-22 12:06:48 +00:00Commented Jul 22, 2013 at 12:06
-
I wrote it up as an answer, and linked to the docs.Mike Sherrill 'Cat Recall'– Mike Sherrill 'Cat Recall'2013-07-22 14:05:57 +00:00Commented Jul 22, 2013 at 14:05
Add a comment
|
1 Answer
All DML statements run in the context of a transaction--either an explicit transaction (BEGIN...COMMIT) or an implicit transaction. I'm pretty sure this is true for all SQL dbms. It's certainly true for PostgreSQL.
By default (without BEGIN), PostgreSQL executes transactions in "autocommit" mode, that is, each statement is executed in its own transaction and a commit is implicitly performed at the end of the statement (if execution was successful, otherwise a rollback is done).
answered Jul 22, 2013 at 13:57
Mike Sherrill 'Cat Recall'
96.6k20 gold badges134 silver badges195 bronze badges
4 Comments
Vadim Samokhin
And by the way, what if autocommit mode is turned off? Is any statement anyway wrapped in begin ... commit?
Mike Sherrill 'Cat Recall'
You can't really turn it off, except in embedded SQL. (Default setting is off for embedded SQL.) BEGIN has the effect of turning it off; PostgreSQL will wait for either COMMIT or ROLLBACK.
Vadim Samokhin
Hm, I can do the thing in mysql in its settings -- do I really can't do the same in postgres? So the question about mysql: what if autocommit set to 0?
Mike Sherrill 'Cat Recall'
You can't do it the same way in PostgreSQL. If you
set autocommit=off, you get "ERROR: SET AUTOCOMMIT TO OFF is no longer supported". In PostgreSQL, you "turn autocommit off", so to speak, by using BEGIN or START TRANSACTION. In MySQL, if autocommit is off, any SQL DML implicitly starts a transaction, and you have to either COMMIT or ROLLBACK to complete it.