4

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

Rachel
  • 2,858
  • 2
  • 26
  • 30
  • 1
    If progress 1 has made a change, it should commit this change if it wants Process 2 to see it. Why would you want to part from this? – Konerak Sep 30 '11 at 11:59
  • Because I just don't want transaction management on. I want speed rather than transactional integrity. I could spend time debugging why Django is holding a transaction open when I don't think it should be; but since I don't care about transactions, that doesn't seem the best use of my time. I'm in the process of moving half the data in a NoSQL db; transactions really don't matter to me. Hence the question - can I turn it off? – Rachel Sep 30 '11 at 13:47

2 Answers2

5

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.

0xCAFEBABE
  • 5,576
  • 5
  • 34
  • 59
  • You sure mean `ALTER TABLE table_name ENGINE = MyISAM;`? – Philipp Reichart Sep 30 '11 at 13:57
  • Thanks for the suggestion. Doesn't that mean I'd only have table-level locking? Which I think is the reason we moved from MyISAM to InnoDB originally – Rachel Sep 30 '11 at 14:23
  • Yes, that's what it means. But you didn't want transactions locking rows, or at least that's what I read into your original post. If the application is not specifically using table locking when running against MyISAM tables, you will get exactly what you wanted: High performance, no overhead through transactions. – 0xCAFEBABE Sep 30 '11 at 14:25
4

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.

Community
  • 1
  • 1
Michael Mior
  • 28,107
  • 9
  • 89
  • 113
  • Thanks for the suggestion, but if I use the DISABLE_TRANSACTION_MANAGEMENT setting, then I have to write lots of manual commit code everywhere which makes life worse. I think the problem is that Django is opening a transaction but only doing the commit on a insert/update, but since InnoDB is by default doing REPEATED_READ, if nothing has been changed in the process doing the reading, it will keep getting the old stale data back. I'll try READ UNCOMMITTED, and see if that helps. – Rachel Sep 30 '11 at 13:56
  • @Rachel You can try READ COMMITED as well - it's faster than the default repetable read, and transactions will see any data which is commited at the time the particular query is executed (with repeatable read even a query that is executed 10s after the start of the transaction will see a snapshot, taken at the start of the transaction, not the query). – Maxim Krizhanovsky Sep 30 '11 at 14:34
  • Sorry Rachel, didn't read the caveat mentioned in the next line. I would confirm that this doesn't disable any `BEGIN TRANSACTION` statements. If `DISABLE_TRANSACTION_MANAGEMENT` results in transactions not being explicitly started, then you're in the clear. – Michael Mior Sep 30 '11 at 14:44