Geek asks and answers

SQL transaction and SELECT LOCK tables

I have a question regarding online transactions, and I want to clarify the problem below with queries SELECTand UPDATE.

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

X makes an online transaction. His account balance $1000. He is going to buy $ 200, and after that his balance should be $800. This is normal; now let me clarify 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, between steps 1 and 3, the balance was checked by another request outside the transaction, and another script checks the balance again in steps 2 and 3.

  • Mysql , ? Mysql "SELECT ... LOCK IN SHARE MODE" or "SELECT ... FOR UPDATE" .

  • $800 , , Eg: "SELECT ... LOCK IN SHARE MODE" or "SELECT ... FOR UPDATE"

  • , Mysql Post greSql -, ?

+3
1

MySQL ( InnoDB) PostgreSQL, READ COMMITTED SERIALIZABLE, 2 3. .

MySQL MyISAM , , .

MySQL, PostgreSQL FOR SHARE FOR UPDATE, .

PostgreSQL MySQL (InnoDB) READ COMMITTED. PostgreSQL . .

800 , , :

  • SELECT ... FOR UPDATE INSERT UPDATE ;

  • ( "other script" ) SELECT ... FOR SHARE , , , - , .

+3

More articles: