1 Replies - 2276 Views - Last Post: 21 August 2012 - 12:31 PM

#1 Alhazred  Icon User is offline

  • D.I.C Head

Reputation: 9
  • View blog
  • Posts: 174
  • Joined: 25-July 07

I need some explanation about transactions and concurrent accesses

Posted 21 August 2012 - 09:51 AM

I've got 2 tables, this is their structure (not useful fields omitted)
PRODUCT
----------------------------------
| id | name | quantity | sold |
----------------------------------
"quantity" can only have 2 values, 1 or 0, no products with a quantity>1 are allowed
"sold" has 2 values: 1=yes, 0=no

SOLD
-----------------
| id | product |
-----------------
"product" references PRODUCT (id)

The problem is to manage 2 concurrent users who want to buy the same product.

What if I use a transaction with a structure like the following (pseudocode)?
TRANSACTION BEGIN
SELECT id FORM product WHERE id=product_id AND sold=0

IF num_rows == 1
{
    UPDATATE product SET sold=1 WHERE id=product_id
    IF affected_rows == 1
    {
        INSERT INTO sold ...
        IF affected_rows == 1
        {
            COMMIT
        }
        ELSE //insert failed
        {
            ROLLBACK
        }
    }
    ELSE //update failed
    {
        ROLLBACK
    }
}
ELSE //prodtuct has been sold just before to confirm
{
    ROLLBACK
}


This is the scenario:
- 1st user confirms the purchase
- 1st user performs the SELECT, it returns 1 result and goes on
- 2nd user confirms the purchase for the same product
- 2nd user performs the SELECT before than the 1st executes a COMMIT or a ROLLBACK

What happens now? Does the 2nd user's SELECT waits for the 1st transaction to end or is it executed and gets a result as the 1st?
Does the transaction locks the row?

Is This A Good Question/Topic? 0
  • +

Replies To: I need some explanation about transactions and concurrent accesses

#2 Alhazred  Icon User is offline

  • D.I.C Head

Reputation: 9
  • View blog
  • Posts: 174
  • Joined: 25-July 07

Re: I need some explanation about transactions and concurrent accesses

Posted 21 August 2012 - 12:31 PM

Solved using SELECT ... FOR UPDATE
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1