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
