Need some help with table locking

Databases
1

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(
  user_id,
  offsetter_id,
  txn_type,
  txn_amount,
  txn_timestamp,
  account_balance,
  shipment_id
)
SELECT 
  s.customer_id
  ,  s.offsetter_id
  , 'Offset'
  , - s.offset_price
  , NOW( ) 
  , tx.account_balance - s.offset_price
  , s.shipment_id
FROM shipments AS s
  INNER JOIN (
    SELECT 
      user_id
      , MAX( txn_id ) AS last_tran
    FROM transactions
    WHERE 
      user_id=$user_id
    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
2

that’s a single statement, so you don’t have to lock anything

did you test it? does it do what you want?

3

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.