0

So I was playing with transactions, and I have tried to subtract the funds from one transfer it to another. As you can see from the picture, the first update query wasn't successful...Unlike the second one, which executed successfully. Now, what I was expecting is, that when I hit the commit, I wouldn't see any changes. But that wasn't the case. Also, I have use START TRANSACTION (it implicitly sets autocommit to 0), rather than BEGIN command.

Here is the output of this:

enter image description here

What I am missing here?

Whirlwind
  • 14,286
  • 11
  • 68
  • 157
  • The first query is very much successful, just no records got updated. – HoneyBadger Jul 03 '17 at 11:25
  • @HoneyBadger Oh, I see. So, if this is considered as successful, how to rollback a transaction if there is no enough funds on a specific account? – Whirlwind Jul 03 '17 at 11:32
  • I don't follow, the two updates in your example seem unrelated to each other, so why do you want to roll back if the first doesn't update any records? – HoneyBadger Jul 03 '17 at 11:35
  • @HoneyBadger They are not unrelated. It is me just playing with transactions in MySQL, so everything seems a bit messed up. Still, they are related. I am transferring funds from one acc to another. So, I am taking 50 bucks from Fred's acc, and sending them to Bob's acc. Of course, if Fred doesn't have enough funds on his acc, I want to rollback everything ... – Whirlwind Jul 03 '17 at 11:38
  • I think Gordon gave you a good lead to follow. Normally, if insufficient funds occur, you'd want it reported with big red exclamation marks. This is why I would do this in application and not let the database deal with it quietly. – HoneyBadger Jul 03 '17 at 11:41
  • @HoneyBadger There is no application. It is just me checking how things works in a database and trying to figure out when transaction might fail and stuff... Haven't been using transactions since ever :), so I just wanted to refresh the memory. And yeah, I am just trying storing procedures and seems to be working. – Whirlwind Jul 03 '17 at 11:52

3 Answers3

1

I don't understand your confusion. Both your updates succeeded. The first one happened to not affect any rows, so only the second one actually changed the data.

You committed the transaction, so all changes take effect.

If you wanted to test transactions, roll back the transaction. Then, when you look at the data, you'll see that nothing changed.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hm... I see. So, I would have to go with some kind of CHECK constraint if I want to restrict deducting fund from an acc that has no funds? I mean, how do I make a transaction to fail, if there is no enough funds? – Whirlwind Jul 03 '17 at 11:28
  • @Whirlwind . . . Write a stored procedure and use `if` logic. Within the transaction, check if the first affects any rows. If not, rollback the transaction. (Well, there are no changes, so rollback/commit does the same thing.) – Gordon Linoff Jul 03 '17 at 11:35
  • @Whirlwind . . . A check constraint won't help you in MySQL because the database does not enforce them. – Gordon Linoff Jul 03 '17 at 12:07
  • Yeah, thanks. I am aware of that. That is why, I have said "some kind of CHECK constraint" ... I mean on some kind of a replace. – Whirlwind Jul 03 '17 at 12:08
1

Not any of your operations has failed.

  1. In first update, where conditions were not satisfied and hence no any row was updated.
  2. In second one, where condition was satisfied for one record, hence that one record was updated.
Keyur Panchal
  • 1,382
  • 1
  • 10
  • 15
0

As an addition to Gordon's answer and his mentioning of stored procedure, I will add an answer just for the future readers and for the completeness, because my real issue was how to rollback a transaction if some condition is not satisfied:

DELIMITER //

CREATE PROCEDURE transfer(IN sender INT, IN receiver INT)

BEGIN

  START TRANSACTION;

   SET @senderBalance = (SELECT balance FROM bank_acc WHERE acctnum =  sender LIMIT 1);
   select @senderBalance;

    IF (@senderBalance < 50) THEN
      ROLLBACK;
    ELSE

    update bank_acc set balance = balance - 50 where acctnum = sender;
    update bank_acc set balance = balance + 50 where acctnum = receiver;

      COMMIT;
    END IF;
END//


DELIMITER ;

Later, you can use it like this:

call transfer(@sender := 20, @receiver := 10);
Whirlwind
  • 14,286
  • 11
  • 68
  • 157
  • @GordonLinoff Feel free to comment or edit this post if you see something wrong. But this worked for me. – Whirlwind Jul 03 '17 at 14:09