SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need some help with table locking

    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"?

    Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    that's a single statement, so you don't have to lock anything

    did you test it? does it do what you want?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    169
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •