Transaction issue in MyISAM

Question

Here is the problem. I have a couple of tables in MySQL MyISAM tables. And also I have several queries that depend on each other. Something like that:

CREATE TABLE users (
  name varchar(255) DEFAULT NULL PRYMARY KEY,
  money int(10) unsigned DEFAULT NULL
);
INSERT INTO users(name, money) VALUES('user1', 700);
INSERT INTO users(name, money) VALUES('user2', 200);

I need to transfer money from 1 user to anouther

<?php
$query1 = "UPDATE users SET money=money-50 WHERE name = 'user1'";
$query2 = "UPDATE users SET money=money+50 WHERE name = 'user2'";

The problem is that the connection is interrupted between these two requests, the money is simply lost, the first user loses it, the other does not receive it. I could use InnoDB or BDB to start the transaction and roll back both error requests in either of them, but still I have this option for MyISAM. How is this problem usually resolved?

Answer 1

MyISAM - . , , , InnoDB. .

- , .

CREATE TABLE users(name VARCHAR(255), PRIMARY KEY (name));
CREATE TABLE transactions(from_user VARCHAR(255), to_user VARCHAR(255), amount INT);

, , .

:

INSERT INTO transactions VALUES('user1', 'user2', 50);

:

SELECT (SELECT SUM(amount) FROM transactions WHERE to_user='user2') - (SELECT SUM(amount) FROM transactions WHERE from_user='user2')

​​ , . , , . .

. , MyISAM.

UPDATE users SET money=IF(name='user1',money-50, money+50) WHERE name='user1' OR name='user2';

Answer 2

-, , , - . , , , , , .

, . , idempotent , , , . , , , . . , - :

CREATE TABLE transactions(
    id int auto_increment primary key,
    committed boolean default false,
    user1 varchar(255), 
    user2 varchar(255),
    balance1 int,
    balance2 int,
    index (id, committed)
);

:

INSERT INTO transactions(user1, user2, balance1, balance2) 
    VALUES(
        'user1', 
        'user2', 
        (SELECT money + 50 FROM users where name='user1'),
        (SELECT money - 50 FROM users where name='user2')
    );

, . , , . , , , .

Answer 3

UPDATE users u1
INNER JOIN users u2
SET u1.money=u1.money-50, u2.money=u2.money+50
WHERE u1.name = 'user1'
AND u2.name = 'user2'