Re: Commit commands with SELECT
Hello COMMIT statements may or may not force the database to call fflush() to flush your double-write to disk. This may or may not affect your performance, depending on your scale, traffic, and how much you're trying to squeeze your hardware. If you're working on the borderline like I am, benchmark, benchmark, benchmark. My 0.02€. Kind regards, -- Luis Motta Campos is a DBA, Foodie, and Photographer On 9 Apr 2012, at 20:47, Karen Abgarian wrote: I vote 1) yes 2) no It could be result of the app developer's convenience to just wrap anything they submit to the database in a transaction. Selects are not transaction but autocommit/commit do no harm. That might be the thinking. 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? 2) If the autocommit and commit statements are unneeded, do they add enough additional overhead that I should be concerned about them? Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139 Fax: 515.281.6137 Email: kay.rozeb...@iowa.gov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Commit commands with SELECT
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
Commit commands with SELECT
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? 2) If the autocommit and commit statements are unneeded, do they add enough additional overhead that I should be concerned about them? Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139 Fax: 515.281.6137 Email: kay.rozeb...@iowa.gov
Re: Commit commands with SELECT
I vote 1) yes 2) no It could be result of the app developer's convenience to just wrap anything they submit to the database in a transaction. Selects are not transaction but autocommit/commit do no harm. That might be the thinking. 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? 2) If the autocommit and commit statements are unneeded, do they add enough additional overhead that I should be concerned about them? Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139 Fax: 515.281.6137 Email: kay.rozeb...@iowa.gov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
