Can I turn transactions off in MySQL/InnoDB?

Question

I have a Django app where the default "REPEATABLE READ" transaction isolation level in InnoDB is causing different processes to have different views of the data than that current in the database.

e.g. Process 1 has made a change but Process 2 isn't seeing it.

I don't need transactional integrity in the app; can I just turn off transactions altogether so that all processes doing a SELECT see the same data?

Any downside to doing this?

Is this what is meant by "READ UNCOMMITTED"?

Any pointers welcome Rachel

Answer 1

I'd suggest that you just convert the InnoDB tables to myISAM. If your criteria is speed, you are wasting alot of potential by using a transaction oriented table type (InnoDB) and just disabling transactions. You would gain alot if you just converted the tables to myISAM. It's designed with lack of transactions in mind, while still being able to lock changes (i.e. table locks).

A clean

ALTER TABLE table_name ENGINE = MyISAM;

can do the trick for a single table, dumping, changing type and loading the table does the trick as well.

Answer 2

Autocommit is on by default in InnoDB. Transactions are still used for updates (which is necessary), but they are committed immediately after each statement.

The READ UNCOMMITTED isolation level allows a transaction to read rows which have been written by other transactions but haven't yet been committed. This point is irrelevant however if you're not explicitly using transactions and autocommit is on.

Unfortunately, I'm not too familiar with Django, but from the documentation I see:

How to globally deactivate transaction management

Control freaks can totally disable all transaction management by setting DISABLE_TRANSACTION_MANAGEMENT to True in the Django settings file.

Hope that helps.