Menu
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?
492
SET ISOLATION provides the same functionality as the ISO/ANSI-compliant SET TRANSACTION statement for isolation levels of DIRTY READ (called UNCOMMITTED in SET TRANSACTION), COMMITTED READ, and REPEATABLE READ (called SERIALIZABLE in SET TRANSACTION).
Specifies that statements can read rows that have been modified by other transactions but not yet committed. Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction.
Repeatable read with pessimistic locking The repeatable read transaction isolation level is the default. This isolation level prevents dirty reads and non-repeatable reads, but does not prevent phantom reads.
Read Uncommitted – Read Uncommitted is the lowest isolation level. In this level, one transaction may read not yet committed changes made by other transactions, thereby allowing dirty reads. At this level, transactions are not isolated from each other.
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 :).
180
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.
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
