Hi guys,

I am trying to create a value transfer system in PHP (you know, where
users can send something like money to each other within the system).
What I am trying to do is to make the transfer function in a single
mysql query so that I can avoid multithread collisions.
Because first I have to check if the user has suffivient balance for
the transfer and the transfer can only be made after this check. If
the check and transfer is in different queries, then it is possible
that 2 instances of the program run simultaneously and so more will be
transfered then the balance, this is what I call multithread
collision.
So my idea was to do the whole thing in a single mysql IF statement, I
tried it a couple of times in phpmyadmin but for some reason it doesnt
seem to work. The mysql manual does not say too much about the IF
statement. (I am talking about the statement, not the IF() function)
So anyone any ideas?

many thanks
Peter

Peter wrote:
> Hi guys,
>
> I am trying to create a value transfer system in PHP (you know, where
> users can send something like money to each other within the system).
> What I am trying to do is to make the transfer function in a single
> mysql query so that I can avoid multithread collisions.
> Because first I have to check if the user has suffivient balance for
> the transfer and the transfer can only be made after this check. If
> the check and transfer is in different queries, then it is possible
> that 2 instances of the program run simultaneously and so more will be
> transfered then the balance, this is what I call multithread
> collision.
> So my idea was to do the whole thing in a single mysql IF statement, I
> tried it a couple of times in phpmyadmin but for some reason it doesnt
> seem to work. The mysql manual does not say too much about the IF
> statement. (I am talking about the statement, not the IF() function)
> So anyone any ideas?
>
> many thanks
> Peter

Make the tables INNODB and use transactions
If you cannot use the INNODB engine, use MyISAM and LOCK TABLES instead

Peter wrote:
> Hi guys,
>
> I am trying to create a value transfer system in PHP (you know, where
> users can send something like money to each other within the system).
> What I am trying to do is to make the transfer function in a single
> mysql query so that I can avoid multithread collisions.
> Because first I have to check if the user has suffivient balance for
> the transfer and the transfer can only be made after this check. If
> the check and transfer is in different queries, then it is possible
> that 2 instances of the program run simultaneously and so more will be
> transfered then the balance, this is what I call multithread
> collision.
> So my idea was to do the whole thing in a single mysql IF statement, I
> tried it a couple of times in phpmyadmin but for some reason it doesnt
> seem to work. The mysql manual does not say too much about the IF
> statement. (I am talking about the statement, not the IF() function)
> So anyone any ideas?
>
> many thanks
> Peter

Forgot, depending on how you are holding the various values, you can put the
credit criteria in the WHERE clause of an UPDATE statement

>I am trying to create a value transfer system in PHP (you know, where
>users can send something like money to each other within the system).
>What I am trying to do is to make the transfer function in a single
>mysql query so that I can avoid multithread collisions.

You really can't reduce the balance in one record and increase the
balance in another record in one query. (Having a column for each
user balance, rather than a balance column for each user row, is
impractical and won't support very many users.)

Use transactions (and InnoDB tables) to avoid interference between
multiple MySQL connections. If you can't use InnoDB tables and
transactions, use MyISAM tables and LOCK TABLES. But transactions
are much better. You don't have to do all the checks ahead of time.
If any check fails (like the destination account not existing),
roll back the transaction.

There are a number of checks needed:

- amount to be transferred is greater than zero.
(this can be checked ahead of time).
You might want to check against outrageously large transfers here.
- source account exists
- source account has sufficient balance
- destination account exists
- destination account can hold the balance (e.g. balance will
not overflow. This might not be a practical problem. Then
again, you need some way to prevent very large transfers from
being interpreted as negative numbers.)

You may have to deal with other restrictions on an account, such
as account freezes by the IRS or for non-payment of child support.
(No, I'm not talking about *in-game* child support, but you might
have to deal with that also.)

Certain transactions may have to allow an overdraft (like the overdraft
fee).

On jan. 21, 02:17, gordonb.et...@burditt.org (Gordon Burditt) wrote:
> >I am trying to create a value transfer system in PHP (you know, where
> >users can send something like money to each other within the system).
> >What I am trying to do is to make the transfer function in a single
> >mysql query so that I can avoid multithread collisions.
>
> You really can't reduce the balance in one record and increase the
> balance in another record in one query. (Having a column for each
> user balance, rather than a balance column for each user row, is
> impractical and won't support very many users.)
>
> Use transactions (and InnoDB tables) to avoid interference between
> multiple MySQL connections. If you can't use InnoDB tables and
> transactions, use MyISAM tables and LOCK TABLES. But transactions
> are much better. You don't have to do all the checks ahead of time.
> If any check fails (like the destination account not existing),
> roll back the transaction.
>
> There are a number of checks needed:
>
> - amount to be transferred is greater than zero.
> (this can be checked ahead of time).
> You might want to check against outrageously large transfers here.
> - source account exists
> - source account has sufficient balance
> - destination account exists
> - destination account can hold the balance (e.g. balance will
> not overflow. This might not be a practical problem. Then
> again, you need some way to prevent very large transfers from
> being interpreted as negative numbers.)
>
> You may have to deal with other restrictions on an account, such
> as account freezes by the IRS or for non-payment of child support.
> (No, I'm not talking about *in-game* child support, but you might
> have to deal with that also.)
>
> Certain transactions may have to allow an overdraft (like the overdraft
> fee).

Hey Gordon and Paul, thank you for the answers.

So please just confirm me if I am right:
Mysql certainly has an inner queue for queries coming from differenct
connections. If I initiate a transaction from my connection, then
mysql groups all the queries in this transaction in its queue and
executes them strictly after each other (of course, except if I roll
them back).
What if my program takes too much time in PHP, for example I start a
transaction in mysql, get some data from some tables and then
Sleep(1000) in PHP and then close the transaction.
Will the database be blocked for this time? Or just the affected
tables? What happens with the other queries coming from other clients?

Do you think it is more practical to do the (balance sufficiency)
check before the actual value transfer, OR do the value transfer and
check afterwards if the remaining balance is still positive and roll
the transaction back if not?

Many thanks
Peter

Peter wrote:
> On jan. 21, 02:17, gordonb.et...@burditt.org (Gordon Burditt) wrote:
>>> I am trying to create a value transfer system in PHP (you know, where
>>> users can send something like money to each other within the system).
>>> What I am trying to do is to make the transfer function in a single
>>> mysql query so that I can avoid multithread collisions.
>> You really can't reduce the balance in one record and increase the
>> balance in another record in one query. (Having a column for each
>> user balance, rather than a balance column for each user row, is
>> impractical and won't support very many users.)
>>
>> Use transactions (and InnoDB tables) to avoid interference between
>> multiple MySQL connections. If you can't use InnoDB tables and
>> transactions, use MyISAM tables and LOCK TABLES. But transactions
>> are much better. You don't have to do all the checks ahead of time.
>> If any check fails (like the destination account not existing),
>> roll back the transaction.
>>
>> There are a number of checks needed:
>>
>> - amount to be transferred is greater than zero.
>> (this can be checked ahead of time).
>> You might want to check against outrageously large transfers here.
>> - source account exists
>> - source account has sufficient balance
>> - destination account exists
>> - destination account can hold the balance (e.g. balance will
>> not overflow. This might not be a practical problem. Then
>> again, you need some way to prevent very large transfers from
>> being interpreted as negative numbers.)
>>
>> You may have to deal with other restrictions on an account, such
>> as account freezes by the IRS or for non-payment of child support.
>> (No, I'm not talking about *in-game* child support, but you might
>> have to deal with that also.)
>>
>> Certain transactions may have to allow an overdraft (like the overdraft
>> fee).
>
> Hey Gordon and Paul, thank you for the answers.
>
> So please just confirm me if I am right:
> Mysql certainly has an inner queue for queries coming from differenct
> connections. If I initiate a transaction from my connection, then
> mysql groups all the queries in this transaction in its queue and
> executes them strictly after each other (of course, except if I roll
> them back).

No. MySQL will execute the queries as they come across. However, it
will lock certain rows or tables so others cannot access them and get
invalid data.

> What if my program takes too much time in PHP, for example I start a
> transaction in mysql, get some data from some tables and then
> Sleep(1000) in PHP and then close the transaction.
> Will the database be blocked for this time? Or just the affected
> tables? What happens with the other queries coming from other clients?
>

Some rows and/or tables may be locked for that time. Other queries will
wait until either the locks are released or they reach a timeout value -
after which they fail.

The moral of the story - NEVER hold a transaction across user input or
anything else external. Get in there, do your job and COMMIT the changes.

> Do you think it is more practical to do the (balance sufficiency)
> check before the actual value transfer, OR do the value transfer and
> check afterwards if the remaining balance is still positive and roll
> the transaction back if not?
>

You need to check after performing an update on the table. Querying the
table does not lock anything.

For instance, the following would be perfectly valid. Assuming $500 in
the account. Both programs have checks for $400 to process

1) Program A fetches the balance and determines sufficient funds exist
($500)
2) Program B fetches the balance and determines sufficient funds exist
($500)
3) Program A decrements the balance by $400 ($100 left)
4) Program B tries to decrement the balance but can't because the
table is locked
5) Program A completes processing and COMMITs the transaction
6) Program B decrements the balance by $400 (-$300 left)

Note - the same thing could happen if steps 4 and 5 were reversed -
except Program B wouldn't wait.

The key here is - both checked the balance before EITHER decremented it,
and determined there was sufficient funds. And there was for either,
but not for both at the same time.

> Many thanks
> Peter
>


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Hey guys,

I came up with this one. It is mostly a pseudo code because I cleared
all the php for readibility.

Hope the early insert into accounthistory locks the table properly.
I was thinking about making only the accounts table innodb abd leave
the history table with some default engine, for performance reasons,
but I could not find a safe way so i made both innodb.

many thanks
Peter

'CREATE TABLE Accounts ('
. ' `accountID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,'
. ' `uID` BIGINT UNSIGNED NOT NULL ,' //userid
. ' `balance` DECIMAL(18, 6),' //the current, always up to data
balance
. ' PRIMARY KEY ( `accountID` ) ) CHARACTER SET = latin1 ENGINE =
innodb;'
. ' ';

'CREATE TABLE Accounthistory (' //stores transaction history
. ' `accounthistoryID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,'
. ' `accountID` BIGINT UNSIGNED NOT NULL ,'
. ' `partneraccountID` BIGINT UNSIGNED NOT NULL ,'
. ' `prevbalance` DECIMAL(18, 6),'
. ' `amount` DECIMAL(18, 6),'
. ' `newbalance` DECIMAL(18, 6),'
. ' `time` DECIMAL(18, 6),' //timestamp
. ' `memo` TEXT,' //some memo
. ' PRIMARY KEY ( `accounthistoryID` ) ) CHARACTER SET = latin1
ENGINE = innodb;'
. ' ';

"TRANSACTION START;"
"UPDATE Accounts SET balance = balance + ".$amount." WHERE accountID =
".$accountIDto.";"
"UPDATE Accounts SET balance = balance - ".$amount." WHERE accountID =
".$accountIDfrom.";"

"INSERT INTO Accounthistory (accountID, partneraccountID, time, memo,
amount) VALUES ($accountIDfrom,$accountIDto,$time,'$memo',(-1*
$amount));"
$accounthistoryIDfrom = mysql_insert_id();

"INSERT INTO Accounthistory (accountID, partneraccountID, time, memo,
amount) VALUES ($accountIDto,$accountIDfrom,$time,'$memo',($amoun t));"
$accounthistoryIDto = mysql_insert_id();

$ispositive = query("SELECT IF(balance>=0,1,0) FROM Accounts WHERE
accountID = ".$accountIDfrom.";"))

if(!$ispositive) {
"ROLLBACK;"
return -1;
}

$balancefrom = query("SELECT balance FROM Accounts WHERE accountID =
$accountIDfrom;")
$balanceto = query("SELECT balance FROM Accounts WHERE accountID =
$accountIDto;")

"UPDATE Accounthistory SET newbalance=$balanceto,
prevbalance=($balanceto - $amount) WHERE accounthistoryID=
$accounthistoryIDto;"
"UPDATE Accounthistory SET newbalance=$balancefrom,
prevbalance=($balancefrom + $amount) WHERE accounthistoryID=
$accounthistoryIDfrom;"

"COMMIT;"
return 1;

Peter wrote:
> Hey guys,
>
> I came up with this one. It is mostly a pseudo code because I cleared
> all the php for readibility.
>
> Hope the early insert into accounthistory locks the table properly.
> I was thinking about making only the accounts table innodb abd leave
> the history table with some default engine, for performance reasons,
> but I could not find a safe way so i made both innodb.
>
> many thanks
> Peter
>
> 'CREATE TABLE Accounts ('
> . ' `accountID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,'
> . ' `uID` BIGINT UNSIGNED NOT NULL ,' //userid
> . ' `balance` DECIMAL(18, 6),' //the current, always up to data
> balance
> . ' PRIMARY KEY ( `accountID` ) ) CHARACTER SET = latin1 ENGINE =
> innodb;'
> . ' ';
>
> 'CREATE TABLE Accounthistory (' //stores transaction history
> . ' `accounthistoryID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,'
> . ' `accountID` BIGINT UNSIGNED NOT NULL ,'
> . ' `partneraccountID` BIGINT UNSIGNED NOT NULL ,'
> . ' `prevbalance` DECIMAL(18, 6),'
> . ' `amount` DECIMAL(18, 6),'
> . ' `newbalance` DECIMAL(18, 6),'
> . ' `time` DECIMAL(18, 6),' //timestamp
> . ' `memo` TEXT,' //some memo
> . ' PRIMARY KEY ( `accounthistoryID` ) ) CHARACTER SET = latin1
> ENGINE = innodb;'
> . ' ';
>
> "TRANSACTION START;"
> "UPDATE Accounts SET balance = balance + ".$amount." WHERE accountID =
> ".$accountIDto.";"
> "UPDATE Accounts SET balance = balance - ".$amount." WHERE accountID =
> ".$accountIDfrom.";"
>
> "INSERT INTO Accounthistory (accountID, partneraccountID, time, memo,
> amount) VALUES ($accountIDfrom,$accountIDto,$time,'$memo',(-1*
> $amount));"
> $accounthistoryIDfrom = mysql_insert_id();
>
> "INSERT INTO Accounthistory (accountID, partneraccountID, time, memo,
> amount) VALUES ($accountIDto,$accountIDfrom,$time,'$memo',($amoun t));"
> $accounthistoryIDto = mysql_insert_id();
>
> $ispositive = query("SELECT IF(balance>=0,1,0) FROM Accounts WHERE
> accountID = ".$accountIDfrom.";"))
>
> if(!$ispositive) {
> "ROLLBACK;"
> return -1;
> }
>
> $balancefrom = query("SELECT balance FROM Accounts WHERE accountID =
> $accountIDfrom;")
> $balanceto = query("SELECT balance FROM Accounts WHERE accountID =
> $accountIDto;")
>
> "UPDATE Accounthistory SET newbalance=$balanceto,
> prevbalance=($balanceto - $amount) WHERE accounthistoryID=
> $accounthistoryIDto;"
> "UPDATE Accounthistory SET newbalance=$balancefrom,
> prevbalance=($balancefrom + $amount) WHERE accounthistoryID=
> $accounthistoryIDfrom;"
>
> "COMMIT;"
> return 1;
>

That's the idea, although I think I'd subtract from the outgoing account
first and check the balance. If it's negative, there's no need to do
the others. Just roll back the transaction.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Michael Austin wrote:
> Jerry Stuckle wrote:
>> Peter wrote:
>>> Hey guys,
>>>
>>> I came up with this one. It is mostly a pseudo code because I cleared
>>> all the php for readibility.
>>>
>>> Hope the early insert into accounthistory locks the table properly.
>>> I was thinking about making only the accounts table innodb abd leave
>>> the history table with some default engine, for performance reasons,
>>> but I could not find a safe way so i made both innodb.
>>>
>>> many thanks
>>> Peter
>>>
>>> 'CREATE TABLE Accounts ('
>>> . ' `accountID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,'
>>> . ' `uID` BIGINT UNSIGNED NOT NULL ,' //userid
>>> . ' `balance` DECIMAL(18, 6),' //the current, always
>>> up to data
>>> balance
>>> . ' PRIMARY KEY ( `accountID` ) ) CHARACTER SET = latin1 ENGINE =
>>> innodb;'
>>> . ' ';
>>>
>>> 'CREATE TABLE Accounthistory (' //stores transaction history
>>> . ' `accounthistoryID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,'
>>> . ' `accountID` BIGINT UNSIGNED NOT NULL ,'
>>> . ' `partneraccountID` BIGINT UNSIGNED NOT NULL ,'
>>> . ' `prevbalance` DECIMAL(18, 6),'
>>> . ' `amount` DECIMAL(18, 6),'
>>> . ' `newbalance` DECIMAL(18, 6),'
>>> . ' `time` DECIMAL(18, 6),' //timestamp
>>> . ' `memo` TEXT,' //some memo
>>> . ' PRIMARY KEY ( `accounthistoryID` ) ) CHARACTER SET = latin1
>>> ENGINE = innodb;'
>>> . ' ';
>>>
>>> "TRANSACTION START;"
>>> "UPDATE Accounts SET balance = balance + ".$amount." WHERE accountID =
>>> ".$accountIDto.";"
>>> "UPDATE Accounts SET balance = balance - ".$amount." WHERE accountID =
>>> ".$accountIDfrom.";"
>>>
>>> "INSERT INTO Accounthistory (accountID, partneraccountID, time, memo,
>>> amount) VALUES ($accountIDfrom,$accountIDto,$time,'$memo',(-1*
>>> $amount));"
>>> $accounthistoryIDfrom = mysql_insert_id();
>>>
>>> "INSERT INTO Accounthistory (accountID, partneraccountID, time, memo,
>>> amount) VALUES ($accountIDto,$accountIDfrom,$time,'$memo',($amoun t));"
>>> $accounthistoryIDto = mysql_insert_id();
>>>
>>> $ispositive = query("SELECT IF(balance>=0,1,0) FROM Accounts WHERE
>>> accountID = ".$accountIDfrom.";"))
>>>
>>> if(!$ispositive) {
>>> "ROLLBACK;"
>>> return -1;
>>> }
>>>
>>> $balancefrom = query("SELECT balance FROM Accounts WHERE accountID =
>>> $accountIDfrom;")
>>> $balanceto = query("SELECT balance FROM Accounts WHERE accountID =
>>> $accountIDto;")
>>>
>>> "UPDATE Accounthistory SET newbalance=$balanceto,
>>> prevbalance=($balanceto - $amount) WHERE accounthistoryID=
>>> $accounthistoryIDto;"
>>> "UPDATE Accounthistory SET newbalance=$balancefrom,
>>> prevbalance=($balancefrom + $amount) WHERE accounthistoryID=
>>> $accounthistoryIDfrom;"
>>>
>>> "COMMIT;"
>>> return 1;
>>>
>>
>> That's the idea, although I think I'd subtract from the outgoing
>> account first and check the balance. If it's negative, there's no
>> need to do the others. Just roll back the transaction.
>>
>
> I would tend to put all of this into a stored procedure such that your
> PHP only provides one statement and is executed in one persistent
> connection. Don't forget to include the checks already mentioned - does
> outaccount have the funds etc... - hopefully you are using a big
> numeric datatype that would prevent you from worrying about the overflow.
> (reference:
> http://dev.mysql.com/tech-resources/...datatypes.html)
>
> EXAMPLE call:
> mysql> call xfervalue($outvalue,$outaccount,$inaccount);
>
> where $outvalue is the value to be xfered,
> $outaccount is the account the value is being removed
> $inaccount is the account you to which you are adding the value.
>
> A good resource for store procedures is:
> http://dev.mysql.com/doc/refman/5.0/...rocedures.html
>

Michael,

He's brand new to MySQL. With his level of experience, I wouldn't even
think of suggesting he use an SP.

You need to walk before you can run. And in this case the SP won't
really help him - other than to put it all in one place. But it's going
to be a heck of a lot harder for him to implement and debug.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Hey guys,

Jerry:
> I'd subtract from the outgoing account first and check the balance...
Done. And I put an overflow check after the incominga account update.

Michael:
> I would tend to put all of this into a stored procedure...
Yes, I was thinking about it. But as Jerry said, as a newbie it would
probably be a nightmare. However, I am shure that on long term SP is
the only acceptable solution, for its security and performance.

Anyway. I am happy that noone has found any serious mistakes in my
code. So I think I will be alright with transactions for the moment.

Another topic: Is there a way to get the unix timestamp in sql with
microsecond precision? Because I could not find anything about it in
the manual, so at the moment I use php microtime(), it is not bad, but
you know, it would be much nicer to put this in sql.

Many thanks
Peter