Mysql – Transaction basics. What is the result of 2 transactions running concurrently

concurrencyinnodbMySQLtransaction

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?

Best Answer

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    }

Related Solutions

Sql-server – Isolation and atomiticy of SQL Server UPDATE

The rows that qualify for an update are always stabilized during discovery by using (at least) U locks (Update locks, see Lock Compatibility). When is time to actually update them, the U lock is upgraded to X lock. Because of this stabilization the row cannot disappear nor can it be modified between the discovery and the update. Nor can a second UPDATE statement discover the same row: a concurrent UPDATE will have to wait for first to commit even if it only needs to 'check' the row and not actually update it. So Batch A is 100% bulletproof. For Batch B, most times the engine will understand what you mean and scan the join with update semantics (your IN clause will be rewritten as a JOIN during query normalization).

There are also differences in behavior depending on whether the plan requires Halloween Protection or not, but that only means the U locks is longer lived or not.

As a general comment the queries you show are the hallmark queries of using table row state where you should be using queues. The concurrency of SET InProgress = 1 WHERE Completed = 0 is basically 1, because it will always require a scan. An equivalent solution using queues for events to signal requests to transition state will scale basically X CPUs.

Postgresql – Can sequential queries result in concurrent transactions

To answer your question in the title: No.

Consecutive queries in the same session (same transaction or not) are never concurrent. And there can only be one transaction at a time in the same session. Concurrency issues are impossible that way, those only happen between transactions in multiple sessions.

One possible reason for your error message might be a glitch in your function, which can be simplified:

CREATE OR REPLACE FUNCTION delete_top_message(_queue_id bigint
                                            , _source_client_id bigint)
  RETURNS SETOF text AS
$func$
BEGIN
   RETURN QUERY
   DELETE FROM message m
   USING (
      SELECT id
      FROM   message
      WHERE  queue_id = _queue_id
      AND    receiver_client_id IN (_source_client_id, -1)
      ORDER  BY insertion_time
      LIMIT  1
      ) m1
   WHERE  m.id = m1.id
   RETURNING m.content;

   IF NOT FOUND THEN
      RAISE 'No message available.';
   END IF;
 END
$func$ LANGUAGE plpgsql;

Your original check:

IF (SELECT rv IS NULL) THEN

could be simplified to:

IF rv IS NULL THEN

But that's still no good. It fires when message.content is NULL, not only when no row has been found. The table definition is missing in the question, so we don't know if that's possible.

Either way, the special variable FOUND is the better tool for the purpose. My audited version is also simpler and faster. Note that plpgsql functions do not return results until the end of the function or RETURN is reached. We can just return rows and still cancel the result later if need be. There is an example in the manual, which is a spin-off from this related question on SO:

FUNCTION syntax error