Original text in my blog: blog.zlb37.xyz/2018-06-13_…

Today, we checked the data in the database and found that the data of two users were inconsistent with the transaction records. User0 lost 200 virtual coins and user lost 200 virtual coins. Thankfully, users haven’t complained yet, so get your data right, but also look into why it happened.

Virtual coins are products that circulate within websites, allowing users to buy, sell and give away.

There are three tables related to user transactions, namely user, Sell_ORDER and transfer_ORDER. Transfer_order records all virtual currency transfer records, while Sell_ORDER records buying and selling virtual currency.

Look up sell_ORDER:

SELECT order_num, seller, buyer, count, complete_time \
    FROM sell_order \
    WHERE seller=user0 AND buyer=user1 \
        AND complete_time > DATE_ADD(NOW.INTERVAL - 1 DAY);
Copy the code
+-----------+--------+-------+-------+--------------------+
| order_num | seller | buyer | count |complete_time       |
+-----------+--------+-------+----------------------------+
| 233333333 | user0  | user1 | 200   |2018-01-01 12:00:00 |
+-----------+--------+-------+-------+--------------------+
Copy the code

There’s only one record. No exceptions. The database cleanup script relies on this table. The problem may be in transfer_Order.

SELECT order_num, from.to.count.type, complete_time \
    FROM transfer_order \
    WHERE from=user0 AND to=user1 \
        AND complete_time > DATE_ADD(NOW.INTERVAL - 1 DAY);
Copy the code
+-----------+-------+-------+-------+----------------------------+
| order_num | from  | to    | count | type | complete_time       |
+-----------+-------+-------+-------+----------------------------+
| 666666666 | user0 | user1 | 200   | sell | 2018-01-01 12:00:00 |
+-----------+-------+-------+-------+----------------------------+
| 666666667 | user0 | user1 | 200   | sell | 2018-01-01 12:00:00 |
+-----------+-------+-------+-------+----------------------------+
Copy the code

This means that there is only one sell order, but two transfer orders are generated. The initial guess is that the user frantically clicked the transfer button, so this BUG should be hidden in the code and restore the scene of the accident:

def sell_order_complete(order_num, seller, buyer, count):
    try:
        session = DBSession()
        
        session.query(TableSellOder) \
            .filter(TableSellOder.order_num == order_num) \
            .update({TableSellOder.complete_time == now(), TableSellOder.status: 'Success'})
            
        session.query(TableUser) \
            .filter(TableUser.id = seller) \
            .update({TableUser.count: TableUser.count - count})
            
        session.query(TableUser) \
            .filter(TableUser.id = buyer) \
            .update({TableUser.count: TableUser.count + count})
            
        session.add(TableTrandferOrder(order_num, seller, buyer, count, "sell"))
        
    except Exception:
        session.rollback()
        rasie_http_error(500)
Copy the code

Python and SQLAlchemy are used in the sample code, which is not real code

It looks fine, putting all database operations into one transaction and rolling them back in case of an exception. However, SQL UPDATE statements do not raise exceptions whether or not they have actually changed the statement. While the user frantically executes this function, the data in the sell_ORDER table has been modified, but with the same parameters, this function can be executed indefinitely until the seller’s balance is insufficient.

Therefore, when executing an SQL UPDATE statement, you need to check whether the number of rows affected by the SQL UPDATE statement is the expected number. If the number of rows affected by the SQL UPDATE statement is not the expected number, you need to manually start the exception and stop executing the SQL UPDATE statement.

This is a profound lesson, the revised code is as follows:

def sell_order_complete(order_num, seller, buyer, count):
    try:
        session = DBSession()
        
        assert session.query(TableSellOder) \
            .filter(TableSellOder.order_num == order_num) \
            .update({TableSellOder.complete_time == now(), TableSellOder.status: 'Success'}) \ = =1
            
        assert session.query(TableUser) \
            .filter(TableUser.id = seller) \
            .update({TableUser.count: TableUser.count - count}) \
            == 1
            
        assert session.query(TableUser) \
            .filter(TableUser.id = buyer) \
            .update({TableUser.count: TableUser.count + count}) \
            == 1
            
        session.add(TableTrandferOrder(order_num, seller, buyer, count, "sell"))
        
    except Exception:
        session.rollback()
        rasie_http_error(500)
Copy the code