I was wondering what happens when several users try to UPDATE a MySQL database at the same time. Went online and learnt about Transactions.
Do I need to define the transactions statements.(START TRANSACTION,... COMMIT/ROLLBACK) for every one of my database UPDATE queries? Is there an automated way to achieve this?
By default , MySQL has autocommit enabled, which can be toggled off using
set session autocommit=off;
For transaction based queries like banking, manual use of TRANSACTION is executed, which ignores the autocommit setting. e.g:
START TRANSACTION;
update bank_card set balance=balance-100;
update credit_card set balance=balance+100;
ROLLBACK; -- execute rollback to cancel the transaction if necessary
COMMIT;
Should something went wrong in any of the UPDATE statements,by executing ROLLBACK; the transaction is aborted and no UPDATE is performed.
When several users try to UPDATE the same table, MySQL will place a LOCK on related rows which prevents simultaneous writing. The first UPDATE query will release the lock by either committing (successful) or rolling back (usually something went wrong) before the second update query can acquire the lock and do the UPDATE.