4

I have a rather basic question on transactions.
Assume a table Account with a column amount. Now we have the following code:

BEGIN  
$amount = SELECT amount FROM ACCOUNT WHERE id = 123;  
$amount = $amount - 100;  
UPDATE ACCOUNT SET amount = $amount WHERE id = 123;    
COMMIT

Now if this snippet runs concurrently by 2 transactions, T1 and T2 what will happen?
I know that in MySQL by default the isolation level is REPEATABLE READ.
So let's say that amount initially is 500.
So when the 2 transactions finish will the final amount be 300 or 100?
I mean when they start both read from table which I guess is a shared lock.
When one updates the table it gets an exclusive lock right? So what happens to the other transaction? Will it proceed with the amount it "view" when it started i.e. 500?

  • what is autocomit setting and where is your BEGIN/COMMIT? – akuzminsky Mar 11 '14 at 19:23
  • @akuzminsky:Updated OP – Jim Mar 11 '14 at 19:25
2

There is no implicit shared lock in REPEATABLE READ isolation level.

You need to change the transaction sequence as follows:

ISOLATION LEVEL

You could switch to the SERIALIZABLE isolation level and disable autocommit. That way, the row is locked in shared mode implicitly.

If you want to stay with REPEATABLE READ or READ COMMITTED, you will have to call SELECT ... FOR UPDATE manually before doing the UPDATE.

QUERY (Optional Suggestion)

You should also experiment with

UPDATE ACCOUNT SET amount = amount - 100 WHERE id = 123; 

along with disabled autocommit and SERIALIZABLE isolation level.

I suggested this because you are doing two queries (SELECT and UPDATE) to change the amount. You could just do it with one query.

Your Question

Since locking reads and not implicit with REPEATABLE READ, there is a possibility of 500 being used twice.

You asked

Is it possible to set the isolation level at a connection level?I.e. not touch the global isolation level?

Yes, you can set the isolation level within the session:

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL   {
    REPEATABLE READ
  | READ COMMITTED
  | READ UNCOMMITTED
  | SERIALIZABLE    }
  • 1)What do you mean with the "Optional Suggestion" query? 2) Is it possible to set the isolation level at a connection level?I.e. not touch the global isolation level? – Jim Mar 11 '14 at 19:46
  • From your link:"With the SESSION keyword, the statement sets the default transaction level for all subsequent transactions performed within the current session." But what if the transactions are from different client connections? – Jim Mar 11 '14 at 20:30
  • The strongest locking behavior would have to win. – RolandoMySQLDBA Mar 11 '14 at 20:46
  • I will open a separate question on this – Jim Mar 11 '14 at 21:04
  • Question:dba.stackexchange.com/questions/60700/… – Jim Mar 11 '14 at 21:58
2

I think the amount will be 400 after two transactions commit.

Let's test. Session #1:

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

mysql> SELECT amount FROM ACCOUNT WHERE id = 123;
+--------+
| amount |
+--------+
|    500 |
+--------+
1 row in set (0.00 sec)

Session #2:

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

mysql> SELECT amount FROM ACCOUNT WHERE id = 123;
+--------+
| amount |
+--------+
|    500 |
+--------+
1 row in set (0.00 sec)

Session #1

mysql> UPDATE ACCOUNT SET amount = 400 WHERE id = 123;
Query OK, 0 rows affected (2.63 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Session #2

mysql> UPDATE ACCOUNT SET amount = 400 WHERE id = 123;
... waiting for a RW lock held by session #1

Session #1

mysql> commit;

Session #2

Query OK, 0 rows affected (2.63 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

Either session:

mysql> SELECT amount FROM ACCOUNT WHERE id = 123;
+--------+
| amount |
+--------+
|    400 |
+--------+
1 row in set (0.00 sec)
  • Well, if you're expecting 300 then you'd follow RolandoMySQLDBA's advise with SERIALIZABLE level or run SELECT amount FROM ACCOUNT WHERE id = 123 FOR UPDATE – akuzminsky Mar 11 '14 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.