MySQL Forums
Forum List  »  General

Re: what is transactiona and non transactional table in mysql?
Posted by: Rick James
Date: March 01, 2014 06:42PM

MySQL (unlike other databases) has multiple "Engines". Some Engines (eg, InnoDB) handle "transactions", some (eg, MyISAM) do not.

The implementation of "Tables" in a transactional Engine need some extra bits and bytes to handle transactional semantics.

"Autocommit" is one way to 'simplify' your code. When using InnoDB with autocommit=1, each write (INSERT, UPDATE, DELETE, etc) is implicitly turned into a transaction, as if it were BEGIN; INSERT...; COMMIT;.

Normally, a programmer explicitly does BEGIN; ... COMMIT;. This allows multiple statements to be performed withing one transaction.

When multiple things happen (multiple statement and/or multiple rows affected by, say, one UPDATE statement) in one transaction, those changes either _all_ happen, or _none_ happen. This all-or-none occurs even if the server crashes. That's the "Atomic" aspect of "transactional semantics".

There are other aspects... Look up ACID. ('A' is for Atomic.)

A textbook example of Atomic is
BEGIN;
UPDATE ... # Subtract some money from my account
UPDATE ... # Add the money to your account
COMMIT;
Without "atomic", if the system crashes between the UPDATEs, and the money is gone forever. (Banks don't like that.) This example cannot be implemented via "autocommit=1"; use explicit BEGIN and COMMIT.

Options: ReplyQuote


Subject
Written By
Posted
Re: what is transactiona and non transactional table in mysql?
March 01, 2014 06:42PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.