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
table:
CREATE TABLE FOO (i INTEGER, j INTEGER);

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
interleaving:

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

BEGIN; SELECT ...; COMMIT;

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

Thread
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