MySQL Transaction Isolation Level with InnoDB

We encourage users to post events happening in the community to the community events group on https://www.drupal.org.
mikeytown2's picture

Question:
Has anyone ran Drupal with the Transaction Isolation Level set to READ UNCOMMITTED? REPEATABLE READ is the default setting, but I think for 99% of the sites out there using dirty reads seems like it would be very helpful.

How to set this in my.conf or via SQL command:
transaction-isolation = READ-UNCOMMITTED
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

One interesting thing is on the mysql performance blog recommends READ-COMMITTED.

Comments

More Details

mikeytown2's picture

According to This Article I need these in my conf file to make this fly in 5.1

innodb_autoinc_lock_mode=2
binlog_format=row
tx_isolation=READ-COMMITTED

One quick

Peter Bowey's picture

One quick fix...
tx_isolation=READ-COMMITTED
should be
transaction-isolation=READ-COMMITTED

--
Linux: Web Developer
Peter Bowey Computer Solutions
Australia: GMT+9:30
(¯`·..·[ Peter ]·..·´¯)

Update

rjbrown99's picture

How did it work out mikeytown2? Did you notice and/or benchmark any improvements with this change? Thanks!

MySQL and PgSQL differs in default settings

gielfeldt's picture

I've also been looking at using READ-COMMITTED isolation level to avoid deadlocks during SELECT ... FOR UPDATE which the merge queries utilize.

The default isolation level for Postgres is READ-COMMITTED, so if Drupal works with Postgres, then I guess it should work for MySQL as well?

I think READ-UNCOMMITTED could be a bit dangerous though.

Anyone using READ-COMMITTED with Drupal 7?

JordanMagnuson's picture

I know this is an old thread, but just wondering if anyone has been using READ-COMMITTED with Drupal 7 in production?

Yes

mikeytown2's picture

I do it and 99% sure d.o does as well.

Yes, all the time and also

btopro's picture

Yes, all the time and also use mikeytown2s https://www.drupal.org/project/apdqc which gives some other related recommendations

Thanks for the replies! For a

JordanMagnuson's picture

Thanks for the replies! For a noob like myself, is the advice above correct? Using READ-COMMITTED as simple as editing my.cnf like below? Anything else I need to do?

[mysqld]
innodb_autoinc_lock_mode=2
binlog_format=row
transaction-isolation=READ-COMMITTED

Wanting to take advantage of https://www.drupal.org/project/cache_consistent