Difference Between "Read Commited" and "Repeatable Read"

Difference between read commited and repeatable read

Read committed is an isolation level that guarantees that any data read was committed at the moment is read. It simply restricts the reader from seeing any intermediate, uncommitted, 'dirty' read. It makes no promise whatsoever that if the transaction re-issues the read, will find the Same data, data is free to change after it was read.

Repeatable read is a higher isolation level, that in addition to the guarantees of the read committed level, it also guarantees that any data read cannot change, if the transaction reads the same data again, it will find the previously read data in place, unchanged, and available to read.

The next isolation level, serializable, makes an even stronger guarantee: in addition to everything repeatable read guarantees, it also guarantees that no new data can be seen by a subsequent read.

Say you have a table T with a column C with one row in it, say it has the value '1'. And consider you have a simple task like the following:

BEGIN TRANSACTION;
SELECT * FROM T;
WAITFOR DELAY '00:01:00'
SELECT * FROM T;
COMMIT;

That is a simple task that issue two reads from table T, with a delay of 1 minute between them.

• under READ COMMITTED, the second SELECT may return any data. A concurrent transaction may update the record, delete it, insert new records. The second select will always see the new data.
• under REPEATABLE READ the second SELECT is guaranteed to display at least the rows that were returned from the first SELECT unchanged. New rows may be added by a concurrent transaction in that one minute, but the existing rows cannot be deleted nor changed.
• under SERIALIZABLE reads the second select is guaranteed to see exactly the same rows as the first. No row can change, nor deleted, nor new rows could be inserted by a concurrent transaction.

If you follow the logic above you can quickly realize that SERIALIZABLE transactions, while they may make life easy for you, are always completely blocking every possible concurrent operation, since they require that nobody can modify, delete nor insert any row. The default transaction isolation level of the .Net System.Transactions scope is serializable, and this usually explains the abysmal performance that results.

And finally, there is also the SNAPSHOT isolation level. SNAPSHOT isolation level makes the same guarantees as serializable, but not by requiring that no concurrent transaction can modify the data. Instead, it forces every reader to see its own version of the world (it's own 'snapshot'). This makes it very easy to program against as well as very scalable as it does not block concurrent updates. However, that benefit comes with a price: extra server resource consumption.

Supplemental reads:

• Isolation Levels in the Database Engine
• Concurrency Effects
• Choosing Row Versioning-based Isolation Levels

What is the difference between Non-Repeatable Read and Phantom Read?

From Wikipedia (which has great and detailed examples for this):

A non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads.

and

A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first.

Simple examples:

• User A runs the same query twice.
• In between, User B runs a transaction and commits.
• Non-repeatable read: The A row that user A has queried has a different value the second time.
• Phantom read: All the rows in the query have the same value before and after, but different rows are being selected (because B has deleted or inserted some). Example: select sum(x) from table; will return a different result even if none of the affected rows themselves have been updated, if rows have been added or deleted.

In the above example,which isolation level to be used?

What isolation level you need depends on your application. There is a high cost to a "better" isolation level (such as reduced concurrency).

In your example, you won't have a phantom read, because you select only from a single row (identified by primary key). You can have non-repeatable reads, so if that is a problem, you may want to have an isolation level that prevents that. In Oracle, transaction A could also issue a SELECT FOR UPDATE, then transaction B cannot change the row until A is done.

Read Committed Vs Repeatable Reads in MySQL?

http://ronaldbradford.com/blog/understanding-mysql-innodb-transaction-isolation-2009-09-24/

REPEATABLE-READ

session 1 :

    MariaDB [test]> DROP TABLE IF EXISTS transaction_test;
    Query OK, 0 rows affected (0.22 sec)

    MariaDB [test]> CREATE TABLE transaction_test(
        ->   id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
        ->   val  VARCHAR(20) NOT NULL,
        ->   created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        -> PRIMARY KEY(id)
        -> ) ENGINE=InnoDB DEFAULT CHARSET latin1;
    Query OK, 0 rows affected (0.29 sec)

    MariaDB [test]>
    MariaDB [test]> INSERT INTO transaction_test(val) VALUES ('a'),('b'),('c');
    Query OK, 3 rows affected (0.08 sec)
    Records: 3  Duplicates: 0  Warnings: 0

    MariaDB [test]> START TRANSACTION;
    Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> SELECT * FROM transaction_test;
+----+-----+---------------------+
| id | val | created             |
+----+-----+---------------------+
|  1 | a   | 2016-04-01 10:09:33 |
|  2 | b   | 2016-04-01 10:09:33 |
|  3 | c   | 2016-04-01 10:09:33 |
+----+-----+---------------------+
3 rows in set (0.00 sec)

MariaDB [test]> select sleep(50);

then user2 run next code :

 MariaDB [test]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> INSERT INTO transaction_test(val) VALUES ('x'),('y'),('z');

commit;

then user 1

MariaDB [test]> SELECT * FROM transaction_test;
+----+-----+---------------------+
| id | val | created             |
+----+-----+---------------------+
|  1 | a   | 2016-04-01 10:09:33 |
|  2 | b   | 2016-04-01 10:09:33 |
|  3 | c   | 2016-04-01 10:09:33 |
+----+-----+---------------------+
3 rows in set (0.00 sec)

MariaDB [test]>

READ-COMMITTED

user1

SET SESSION tx_isolation='READ-COMMITTED';
TRUNCATE TABLE transaction_test;
INSERT INTO transaction_test(val) VALUES ('a'),('b'),('c');
MariaDB [test]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select sleep(60);

then user2 run next code :

MariaDB [test]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> INSERT INTO transaction_test(val) VALUES ('x'),('y'),('zwfwfw');

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [test]> commit;

then user1 finish query :

MariaDB [test]> SELECT * FROM transaction_test;
+----+--------+---------------------+
| id | val    | created             |
+----+--------+---------------------+
|  1 | a      | 2016-04-01 10:28:08 |
|  2 | b      | 2016-04-01 10:28:08 |
|  3 | c      | 2016-04-01 10:28:08 |
|  4 | x      | 2016-04-01 10:29:00 |
|  5 | 
y      | 2016-04-01 10:29:00 |
|  6 | zwfwfw | 2016-04-01 10:29:00 |
+----+--------+---------------------+
6 rows in set (0.00 sec)

What is the difference between repeatable read and snapshot isolation

"Snapshot" guarantees that all queries within the transaction will see the data as it was at the start of the transaction.

"Repeatable read" guarantees only that if multiple queries within the transaction read the same rows, then they will see the same data each time. (So, different rows might get snapshotted at different times, depending on when the transaction first retrieves them. And if new rows are inserted, a later query might detect them.)

How does PostgreSQL implement the REPEATABLE_READ isolation level?

READ COMMITTED and REPEATABLE READ are using the same technology: a snapshot that determines which of the versions of a row in the table a transaction can see. The difference is that with READ COMMITTED, the snapshot is taken at the start of each statement, so that each new statement can see everything that has been committed before, while a REPEATABLE READ transaction uses the same snapshot for all statements.

There are two consequences:

• if anything, REPEATABLE READ is cheaper than READ COMMITTED, because it takes fewer snapshots

• REPEATABLE READ provides even higher isolation than required by the SQL standard – the database does not seem to change at all

The price you are paying for REPEATABLE READ is different:

• you risk serialization errors, which force you to repeat the transaction

• VACUUM cannot clean up rows marked dead after the REPEATABLE READ transaction started

MySQL 'REPEATABLE READ' transaction unexpected behavior

Repeatable read isolation level guarantees consistency within a single transaction. You are executing multiple transactions. For the behaviour your expecting you would need to look into locking reads. See here for more info. https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html#isolevel_repeatable-read

What is the difference in executing a single select query in read committed versus repeatable read transaction?

Normally there won't be a difference for a single SELECT statement.

The exception is if the SELECT statement calls user defined functions that themselves issue multiple SQL statements.

In this case, REPEATABLE READ will make all these SQL statements share a single snapshot of the database, while READ COMMITTED will cause each SQL statement to see a different state of the database.

---

Related Topics