SQL transaction and tables SELECT LOCK clarification

Question

I have a question regarding online transactions and want to clarify the below issue with SELECT and UPDATE queries.

I will use the below example to explain this to you.

X is doing an online transaction. His account balance is $1000. He is going to buy $200 item and after that his account balance should be $800. That is fine; now let me elaborate this further

Begin DB transaction.

Step 1: account balance is $1000

{Select the balance from a different script}

Step 2: Buy something for $200

{Select the balance from another script}

Step 3: Remaining balance $800

Commit DB transaction

As you can see in between step 1 and 3 the balance has been checked by a different query outside the transaction and another script is checking balance again within step 2 and 3.

1. With default Mysql transactions what will be the values for first and second balance results? Also by default is Mysql using “SELECT ... LOCK IN SHARE MODE” or “SELECT ... FOR UPDATE” features or do we have to use them within our queries.

2. If we need to return $800 for both balance check queries above by waiting for the transaction to commit which method can we use Eg: “SELECT ... LOCK IN SHARE MODE” or “SELECT ... FOR UPDATE”

3. Also do you know if Mysql and Post greSql handles the transactions differently as mentioned above?

Answer 1

In both MySQL (with InnoDB) and PostgreSQL, using READ COMMITTED or SERIALIZABLE isolation mode, the balance will be the same at step 2 and step 3. The change will only be visible after transaction commit.

MySQL with MyISAM might be different, but you should not be using it if you care about data correctness or consistency.

Neither MySQL nor PostgreSQL will do a FOR SHARE or FOR UPDATE unless you specifically request it.

Both PostgreSQL and MySQL (InnoDB) should behave similarly in READ COMMITTED mode. For the details of PostgreSQL's behaviour see the manual on transaction isolation.

If you want to get 800 for both reads, as described above, there are two things you must do:

• The updating transaction must SELECT ... FOR UPDATE the balance record for the user before INSERTing the transaction record and UPDATEing the balance; and

• The reading transaction ("other script") must SELECT ... FOR SHARE the balance whenever it reads it, so it will wait whenever somebody else is updating it instead of just reading the unchanged version.