Jump to content
Larry Ullman's Book Forums

Transactions, a more deep dive


Recommended Posts

Hi,

I'm learning transactions (chapter 7, from 236 to 238 page). I have some doubts.

The example on the book (tha bank account) is meant for be manually checked, but i think in a real application this check should be automatic.

For example (example like that in the book):

START TRANSACTION;
UPDATE accounts
SET balance = (balance-100)
WHERE account_id = 2 AND balance>100;

--If the above query is succesfully then:
UPDATE accounts
SET balance = (balance+100)
WHERE account_id =1;

--How can I exec the commit only if everything is ok?
COMMIT;

Respect to the book, in the first query I added the check . The first query is executed only if the balance>100.

However the second query (namely the second update) should be executed only if the prevoious query has decreased the balance.  How could I automatically check this?

Furthermore the COMMIT;   has to be executed only if the previous 2 queries have done their job.

How could this be implemented?

 

Another question about transaction is relative to multiple concurrent access to a database i.e. the scenario is that I have more users that access in writing in the same time the same record of a table. For example an UPDATE that modify the amount of available t-shirts (2 users in the same time decrease this amount).   Sql database guarantees atomicity  of an UPDATE query (and other queries)? (Because if the UPDATE queries can be "scheduled" you could have a wrong result)

Thanks in advance.

And compliments on the book.

Best regards.

 

Edited by NickReferee
Link to comment
Share on other sites

Thanks for your questions and for the nice words. For the first question, when executing queries like this, there really is no "automatic". In a real application these queries would be executed by code that verifies the results as part of the process. For example, using PHP you could start a transaction, fetch the balances, adjust the balances, fetch the new balances, verify, and then commit. 

For the second question, what you describe is called a "race condition" and can be prevented by implementing locks. So you'd lock the row, run one update, and then unlock the row. A subsequent query that would theoretically make a quantity negative (assuming that wasn't allowed) would then fail. 

Link to comment
Share on other sites

8 hours ago, Larry said:

Thanks for your questions and for the nice words. For the first question, when executing queries like this, there really is no "automatic". In a real application these queries would be executed by code that verifies the results as part of the process. For example, using PHP you could start a transaction, fetch the balances, adjust the balances, fetch the new balances, verify, and then commit. 

For the second question, what you describe is called a "race condition" and can be prevented by implementing locks. So you'd lock the row, run one update, and then unlock the row. A subsequent query that would theoretically make a quantity negative (assuming that wasn't allowed) would then fail. 

Good morning Mr. Larry.

Thanks for the reply.

I delved into the question and I have found a solution in MYSQL:

 

START TRANSACTION;

UPDATE accounts
SET balance = (balance-100)
WHERE account_id = 2 AND balance>100;

UPDATE accounts
SET balance = (balance+100)
WHERE account_id =1 
  AND ROW_COUNT();  -- check does a row was altered in previous statement
                    -- if not then this statement will not alter any row too

COMMIT;
image.gif

I discovered the ROW_COUNT() function that return how much rows has affected from previous INSERT,UPDATE,DELETE

 

About second question I found out that InnoDB tables have a lock by default(https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html) thereby no problem with multiple user modify of a record.

 
Link to comment
Share on other sites

 Share

×
×
  • Create New...