PostgreSQL and MySQL transaction isolation level difference

The transaction isolation level database 4:

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

In the PostgreSQL transaction isolation level, READ UNCOMMITTED is believed to be READ COMMITTED, so in fact only three.
PostgreSQL the default isolation level is READ COMMTIED, and the MySQL default to REPEATABLE READ MySQL READ UNCOMMITTED, the other is the level, but the transaction isolation level basically have no what general use.

MySQL in the REPEATABLE READ isolation level, The select statement is not common in after a transaction starts have submitted data, But select for update can see, That is to say the common select and select for update to see the result is not the same, This is especially need to allow developers to the attention of the local.
PostgreSQL in the REPEATABLE READ isolation level, select for udpate and select statement of the result is the same, not to look at after a transaction starts have submitted data.


Another MySQL had a very strange gap lock, If the data in the table for(1,2,3,10,11), If the implementation of select * from T1 where id=6 for update; after locking, Other users will not be able to insert (4,5,6,7,8,9) primary key value; if the select for update locks a primary key value in the primary key table than existing value is also a big value, This table is not inserted in the primary key table is larger than the value of the.

The following demonstration:
Building test table and data in PostgreSQL:
osdba=# create table t1(id int primary key, info text);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
osdba=# insert into t1 values(1,'11111');
INSERT 0 1
osdba=# insert into t1 values(2,'22222');
INSERT 0 1
osdba=# insert into t1 values(3,'33333');
INSERT 0 1
osdba=# insert into t1 values(10,'101010');
INSERT 0 1
osdba=# insert into t1 values(11,'111111');

osdba=# select * from t1;
id | info
----+--------
1 | 11111
2 | 22222
3 | 33333
10 | 101010
11 | 111111
(5 rows)

The test data in MySQL:
mysql> create table t1(id int primary key, info varchar(255));
Query OK, 0 rows affected (0.13 sec)


mysql> insert into t1 values(1,'11111');
Query OK, 1 row affected (0.03 sec)

mysql> insert into t1 values(2,'22222');
Query OK, 1 row affected (0.04 sec)

mysql> insert into t1 values(3,'33333');
Query OK, 1 row affected (0.05 sec)

mysql> insert into t1 values(10,'101010');
Query OK, 1 row affected (0.04 sec)

mysql> select * from t1;
+----+--------+
| id | info |
+----+--------+
| 1 | 11111 |
| 2 | 22222 |
| 3 | 33333 |
| 10 | 101010 |
| 11 | 111111 |
+----+--------+
5 rows in set (0.00 sec)


The first test PostgreSQL:
In Windows 1, starting a business in the "REPEATABLE READ":

osdba=# begin isolation level REPEATABLE READ ;
BEGIN

In the window 2 to the table, insert a record:
osdba=# insert into t1 values(5,'555555');
INSERT 0 1

And then back to the window 1:
osdba=# select * from t1 where id=5;
id | info
----+------
(0 rows)

osdba=# select * from t1 where id=5 for update;
id | info
----+------
(0 rows)

You can see in the PostgreSQL or select statement with or without for update, see the results are the same, not to look at the id=5 record.


While MySQL is not the same, the ordinary select MySQL statement is not logged into id=5, but select for update can see, see below:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where id=5;
Empty set (0.00 sec)

mysql> select * from t1 where id=5 for update;
+----+-------+
| id | info |
+----+-------+
| 5 | 55555 |
+----+-------+
1 row in set (0.00 sec)

mysql>

You can see MySQL in the REPEATABLE READ isolation levels, see select for update and select see the result is not the same.


In the MySQL, once you have set the SERIALIZABLE level, in the transaction once the query table, the query will records are locked, while PostgreSQL SERIALIZABLE and MySQL completely different. Query does not lock table.
In the PostgreSQL window, in 1 starts a transaction, and then start a transaction in the window 2, window 2 modified id=3 record, and then to the window 1 modifies the id=3 record, will be in error, thus realizes the isolation level SERIALIZABLE.
Thus it can be seen in the SERIALIZABLE level, PostGreSQL concurrency would be much better than MySQL.

Started by Franklin at February 07, 2016 - 10:18 PM

PG is truly SERIALIZABLE levels, but this level using occasions too little

Posted by Upton at February 21, 2016 - 10:45 PM

Ask, that this should be used in what occasions?

Posted by Verne at February 28, 2016 - 11:22 PM

Previously in the PG document, indeed:

Or to see a specific application, however, I don't

Posted by Upton at March 12, 2016 - 11:47 PM

In their system, using SERIALIZABLE logic, feel to be simple

Posted by Verne at March 24, 2016 - 12:17 AM

Ha ha, is a line, line up to

Posted by Upton at April 05, 2016 - 12:57 AM

Looking at the data, PG has only two levels of reading has been submitted (Read committed), serializable(Serializable),
Small system or Serializable is simple

Posted by Verne at April 17, 2016 - 1:48 AM

Oh, if use serializable business, to collapse,
To special application needs to be useful

Posted by Upton at April 19, 2016 - 2:19 AM

Oh ~ so that is a small system
After all, reading has been submitted (Read committed), in a transaction needs to consider other transaction has already been committed, the situation is more complicated

Posted by Verne at May 04, 2016 - 3:05 AM

Well, there are always play

Posted by Upton at May 18, 2016 - 3:07 AM

I used read committed level, need to be locked
Rather than allow the system to lock, as he uses the lock is obvious

Posted by Jo at January 11, 2017 - 9:55 PM