Is it safe to set MySQL isolation in "Read Uncommitted" (dirty reads) for normal use on the Internet? Even with replication?

Question

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

It seems like normal to set the database isolation level to " Read uncommitted " (in this case, dirty reads). My understanding of the general disadvantage of Read Uncommitted is that the reader can read unauthorized data that will later be canceled.

In the CRUD blog / forum usage template, will there ever be a rollback? And even if there is, is there a serious problem reading uncommitted data?

I don’t use replication at the moment, but in the future, if I want to use replication (row-based, not statement-based), the Read Uncommitted isolation level will not let me do this?

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

Answer 1

MySQL 5.1 is more strict when using read-uncommitted and binlogging (necessary for replication), so you may get errors in some simple update / delete operations that you would not get at the default isolation level REPEATABLE READ. I saw simple PK updates, such as:

Update foo set bar = 1 where id = 1234;

Error with: mysql_real_query 1598 error: binary registration is not possible. Message: The READ-UNCOMMITTED transaction level in InnoDB is not safe for binlog mode 'STATEMENT'

Thus, you should be prepared to handle this by switching to repeated readings when data changes or using separate read and write connections with their own isolation levels. The latter can come in handy when / if scaling and replication of your project is necessary, so you can send the selection to a read-only slave and write to the master.

OTOH, using read-uncommitted for reading can be a real enhancement if consistent reads are not strictly needed, since Innodb has fewer locks.

Regarding the question of a possible rollback, I think you are the best person to tell us about it, since you are coding it :).

Answer 2

This isolation level means that you can read inconsistent data. There is no guarantee that the data you are reading is in a consistent view of the database.

If this is a problem or not, this is not a MySQL question, but an application question, which includes an assessment of the risk of using / returning inconsistent data.

In an internet banking application, that would be nice. In a game, this can be good. It depends.

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