By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,133 Members | 1,092 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,133 IT Pros & Developers. It's quick & easy.

InnoDB autocommit and locks

P: n/a
Hello,

I have 2 questions regarding InnoDB tables:

1. In the MySQL manual, it states that
"MySQL begins each client connection with autocommit mode enabled by
default. When autocommit is enabled, MySQL does a commit after each SQL
statement if that statement did not return an error."

Does using BEGIN override this behavior? In other words, with
autocommit on, does BEGIN open up a new transaction and stop committing
until an explicit COMMIT command? Or do I need to turn autocommit off
before multi-query transactions?

2. I have been reading the examples for LOCK IN SHARE MODE and FOR
UPDATE in the manual, but I am still confused as to when I should use
one as opposed to the other. I have gone through tutorials but many
just use the same examples as the manual. I'm sure I'm just missing
something small, but if someone could give a simple example of the
difference between the two, that would be very helpful as the difference
to me seems very suttle.

Thanks in advance.
Jul 17 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Marcus wrote:
Hello,

I have 2 questions regarding InnoDB tables:

1. In the MySQL manual, it states that
"MySQL begins each client connection with autocommit mode enabled by
default. When autocommit is enabled, MySQL does a commit after each SQL
statement if that statement did not return an error."

Does using BEGIN override this behavior? In other words, with
autocommit on, does BEGIN open up a new transaction and stop committing
until an explicit COMMIT command? Or do I need to turn autocommit off
before multi-query transactions?
Perhaps you are looking in another manual. The official online manual is
rather clear on that.
http://dev.mysql.com/doc/mysql/en/in...utocommit.html
[quote]
If the connection has autocommit enabled, the user can still perform a
multiple-statement transaction by starting it with an explicit START
TRANSACTION or BEGIN statement and ending it with COMMIT or ROLLBACK.
[end quote]

So, yes, yes and no.

2. I have been reading the examples for LOCK IN SHARE MODE and FOR
UPDATE in the manual, but I am still confused as to when I should use
one as opposed to the other. I have gone through tutorials but many
just use the same examples as the manual. I'm sure I'm just missing
something small, but if someone could give a simple example of the
difference between the two, that would be very helpful as the difference
to me seems very suttle.


Not sure what examples would be better than the ones in the manual, they
are probably as good as they get. I assume you read this page:
http://dev.mysql.com/doc/mysql/en/in...ing-reads.html

It is not the real/whole story, but perhaps it is easier if you see it
like:..
"FOR UPDATE" is used when you have the intention of updating the rows in
the table you select from, and "LOCK IN SHARE MODE" is used when they
(the selected rows) just are needed for an update elsewhere, and reading
can be shared by many.
/Bent
Jul 17 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.