Is it safe to set MySQL isolation to “Read Uncommitted” (dirty reads) for typical Web usage? Even with replication?

Question

I'm working on a website with typical CRUD web usage pattern: similar to blogs or forums where users create/update contents and other users read the content.

Seems like it's OK to set the database's isolation level to "Read Uncommitted" (dirty reads) in this case. My understanding of the general drawback of "Read Uncommitted" is that a reader may read uncommitted data that will later be rollbacked.

In a CRUD blog/forum usage pattern, will there ever be any rollback? And even if there is, is there any major problem with reading uncommitted data?

Right now I'm not using any replication, but in the future if I want to use replication (row-based, not statement-based) will a "Read Uncommitted" isolation level prevent me from doing so?

What do you think? Has anyone tried using "Read Uncommitted" on their RDBMS?

Answer 1

This isolation level means that you may read inconsistent data. There is no gurantee that data you read is from a consistent view of the database.

Whethere this is an issue or not is not a MySQL question, but an application question, which involves an assessment of the risk of using/returning inconsistent data.

On an Internet banking app it would ne a no-go. On a game it may be OK. It depends.

I have used both "read uncommitted" and replication, and have had no issues.

Answer 2

MySQL 5.1 is stricter when using read-uncommitted and binlogging (needed for replication) - so you can get errors on some simple update/delete statements that you wouldn't get in the default isolation level REPEATABLE READ. I have seen simple PK updates such as:

Update foo set bar=1 where id=1234;

Error with: mysql_real_query error 1598 message : Binary logging not possible. Message: Transaction level 'READ-UNCOMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'

So you must be prepared to deal with this by switching back to repeatable reads when modifying data, or using separate connections for reads and writes, with their own isolation levels. The latter can get handy when/if your project scales and replication is needed, so you can send select to a read-only slave and writes on the master.

OTOH, using read-uncommitted for reads can be a real gain, if consistent reads aren't strictly needed, as Innodb has less locks to take.

As for the question regarding if rollbacks are possible, I think you're the best person to tell us about it, as you are the one coding it :).