I've got a multi-table query that is updating a customer's account balance. The query involves a couple databases and has multiple aliases in it. I'm not clear what the lock statement should look like to cover the query.
I've read that you need to lock table aliases. Does this mean that I need to alias the transactions table in the INSERT to the same alias as the SELECT? What about intermediate tables like "m"?
INSERT INTO transactions(
, - s.offset_price
, NOW( )
, tx.account_balance - s.offset_price
FROM shipments AS s
INNER JOIN (
, MAX( txn_id ) AS last_tran
GROUP BY user_id
) AS m ON m.user_id = s.customer_id
AND s.shipment_id =$shipment_id
INNER JOIN transactions AS tx
ON tx.txn_id = m.last_tran
AND tx.user_id = s.customer_id
that's a single statement, so you don't have to lock anything
did you test it? does it do what you want?
Yes, the statement does as I want. I didn't realize that a single mysql statement is effectively a lock in itself. I have multiple scripts calling that command concurrently, so I just want to make sure there is no way for it to be hit in multiple processes to create two simultaneous transactions.