I see your point now, every query you do just updates one record not much use for transaction there. :-)
But, continuing on the basis of "a good discussion is always educational for both parties:
"Transaction is set of queries that executed on a record as union and record gets locked for access to others until transaction is finished - this assures data integrity."
Transactions are not just locks, they are a failsafe method. For example; when a bank transfers money from one account to another they have to run two queries, one to take the money from the first account, and another to put the money into the other account. If one of the queries failes, the other must be rolled back, or money will have been lost. That is what transactions are for.
BTW: transactions don't just lock records the MySQL way, most databases support various types of locking that can even let you read 'dirty' uncommitted data.
"Another one is to use relative updates, for example if clerk sells three jugs of milk (assuming initial stock was 38) instead of
update dairy_inventory set quantity=35 where category="milk"; you do
update dairy_inventory set quantity=quantity-3 where category="milk";
That way transactions are not necessary...
"
Wahow there, that is a perfect example of 'mysql-thinking'.
If three jugs are sold, the query must decrease the total stock by three.
This means there must first be a check to ensure that there are three or more jugs to sell in the first place.
If there are, you can either get the current stock, subtract 3, and update the table (your first query example)
Or you can run your second query and decrease the stock from whatever it was to whatever it was minus three.
But what if between your check and your update, another person buys some jugs?
Then you will both see that there are 3 jugs for sale, and you'll both try to update the stock.
With or without transactions, both sales will be made up until the point where the stock is decreased.
If your database setup is any good, it will complain about negative stock and refuse to complete the stock update query.
The first sale would work, because there are exactly three jugs to sell at that time, but the second sale would fail because it tries to decrease the stock from zero to -3.
At that moment, whatever changes you made to the database to register the second sale must be undone. And you cannot do that without transactions.
Your example might work, if MYSQL understood things like 'SELECT FOR UPDATE' which would lock the stock records for the jugs untill you are finished with them, which would put the second sale on hold between the stock check and the stock update.
MySQL's solution would probably be to lock the table during each sale, but that is simply not acceptable :-)
