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
