Update-Insert in MySQL with REPEATABLE READ Isolation level

Question

I've one secenario where i've to perform update-insert in MYSQL (I've to use REPEATABLE-READ Isolation level) The Secenario is -

there is a table -
+----+--------+--------+--------+
| ID |  NAME  | AMOUNT | STATUS |
+----+--------+--------+--------+
|  1 |  NAME1 |  700   | ACTIVE |
+----+--------+--------+--------+

Now every new entry for NAME = NAME1 should update the old entry with STATUS = INACTIVE, and then new entry should be inserted, i.e.

+----+--------+--------+----------+
| ID |  NAME  | AMOUNT |  STATUS  |
+----+--------+--------+----------+
|  1 |  NAME1 |  700   | INACTIVE |
+----+--------+--------+----------+
|  2 |  NAME1 |  800   |  ACTIVE  |
+----+--------+--------+----------+

if two transactions are going to insert new entry,(one with AMOUNT = 800, second with AMOUNT = 900) they should mark old entry as INACTIVE and then insert new entry. so final output of table (1st instanse) should be

+----+--------+--------+----------+
| ID |  NAME  | AMOUNT |  STATUS  |
+----+--------+--------+----------+
|  1 |  NAME1 |  700   | INACTIVE |
+----+--------+--------+----------+
|  2 |  NAME1 |  800   | INACTIVE |
+----+--------+--------+----------+
|  3 |  NAME1 |  900   |  ACTIVE  |
+----+--------+--------+----------+

Obviously, There will be the race condition, But No issue, the resultant table should have only one ACTIVE record for NAME = NAME1

Now Problem is - Both transations are inserting their record by updating the already inserted row. So finaly I'm getting two ACTIVE records. ie.

+----+--------+--------+----------+
| ID |  NAME  | AMOUNT |  STATUS  |
+----+--------+--------+----------+
|  1 |  NAME1 |  700   | INACTIVE |
+----+--------+--------+----------+
|  2 |  NAME1 |  800   |  ACTIVE  |
+----+--------+--------+----------+
|  3 |  NAME1 |  900   |  ACTIVE  |
+----+--------+--------+----------+

+--------------------------+-------------------------------+
|   T1                     |             T2                | 
+--------------------------+-------------------------------+ 
|  START                   |    START                      |
|  update entry(ID=1)      | wait for lock on entry(ID=1)  |
|  insert new entry(ID=2)  |                               |
|  commit                  |                               |
|                          | update entry(ID=1)            |
|                          | insert new entry(ID=3)        |
|                          | commit                        |
+--------------------------+-------------------------------+

I'm using (and have to use) REPEATABLE READ, so in that case T2 should get the new inserted row (by T1) (I think it's case of phantom read) But It's not happening.

Any suggestions on how this is best achieved would also be helpful.

Thanks

Have you tried reproducing this manually (with 2 mysql clients)? — Vatev, Jul 24 '15 at 12:18
@Vatev : Yes, I've done this analysis in Mysql clients using two terminals. — user3778893, Jul 27 '15 at 06:21

score 0 · Answer 1 · answered Nov 03 '15 at 10:47

0

Use the isolation level SERIALIZABLE and you don't have to worry. No, you don't have to use REPEATABLE READ. You can also set the isolation level per session. There's no other reason to not do this.

answered Nov 03 '15 at 10:47

fancyPants

50,732
33
89
96

Update-Insert in MySQL with REPEATABLE READ Isolation level

1 Answers1