MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Stephen Tu Date:April 13 2012 3:16pm
Subject:Re: Commit commands with SELECT
View as plain text  
Wrapping even just selects around a transaction absolutely matters,
depending if you care about isolation.

Consider the following two clients running on the same mysql instance, w/
--transaction_isolation=serializable. Suppose we have the following innodb

Client 1:
SELECT * FROM foo WHERE i = 0;
SELECT * FROM foo WHERE i = 0;

Client 2:
UPDATE foo SET j = 1 WHERE i = 0;

Suppose the table starts out with a single tuple (0, 0). Now, if client 1
and client 2 are running at the same time, wrapping client 1's select
statements with a BEGIN/COMMIT removes the possibility of the following

C1: SELECT * FROM foo WHERE i = 0;
C2: UPDATE foo SET j = 1 WHERE i = 0;
C1: SELECT * FROM foo WHERE i = 0;

Without the BEGIN/COMMIT, the interleaving above is completely valid. Now
to answer your questions.

> On 09.04.2012, at 11:38, Rozeboom, Kay [DAS] wrote:
> >
> >> We have an application with blocks of code that begin with setting
> autocommit off, and end with a commit.  The code in between does only
> selects, no updating.
> >>
> >> 1)      Am I correct in thinking that the autocommit and commit
> statements don't really accomplish anything useful?

No, you need to reason about whether or not the select statements need to
run in isolation for correctness (like the above example).

> >> 2)      If the autocommit and commit statements are unneeded, do they
> add enough additional overhead that I should be concerned about them?

I don't think you gain any overhead by using explicit transactions. For
instance, a single select statement (I believe) is really equivalent to


However, you do incur overhead in the sense that the longer your
transaction block is, the more time you spend holding (read) locks, and
thus excluding writers.

Hope that helps,
Stephen Tu

Commit commands with SELECTKay [DAS] Rozeboom9 Apr
Re: Commit commands with SELECTKaren Abgarian9 Apr
  • Re: Commit commands with SELECTLuis Motta Campos13 Apr
    • Re: Commit commands with SELECTStephen Tu13 Apr