InnoDB autocommit and locks

[Marcus]

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.

[Bent Stigsen]

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