Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

[MySQL] I need some explanation about transactions and concurrent accesses

pseudocode mysql

  • Please log in to reply
7 replies to this topic

#1 Alhazred

Alhazred

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 133 posts

Posted 21 August 2012 - 08:53 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?
  • 0

#2 Orjan

Orjan

    CC Mentor

  • Moderator
  • 2918 posts
  • Location:Karlstad, Sweden
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Pascal
  • Learning:Java, C#

Posted 21 August 2012 - 09:12 AM

are the database structure fixed, or is that what you have done?
  • 0

I'm a System developer at XLENT Consultant Group mainly working with SugarCRM.
Please DO NOT send mail or PM to me with programming questions, post them in the appropriate forum instead, where I and others can answer you.


#3 gregwarner

gregwarner

    Obi Wan of Programming

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1586 posts
  • Location:Arkansas
  • Programming Language:C, Java, C++, C#, PHP, Transact-SQL

Posted 21 August 2012 - 09:17 AM

If you're using InnoDB tables, SELECT ... LOCK IN SHARE MODE allows other concurrent sessions to read the values, but not update them. The lock is released on COMMIT or ROLLBACK. You could use that to obtain the lock on the record while you update its status to "sold".
  • 0

ti-99-sig.png
Hofstadter's Law: It always takes longer than you expect, even when you take into account Hofstadter's Law.
– Douglas Hofstadter, Gödel, Escher, Bach: An Eternal Golden Braid


#4 Alhazred

Alhazred

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 133 posts

Posted 21 August 2012 - 09:33 AM

The tables have other fields, but those posted are the only I think was useful for the explanation.
By the way, if needed I can modify the tables' structure.

Of course I'm using InnoDB tables.
So if I declare the SELECT as LOCK IN SHARE MODE my code should work, right?

If a user has more than 1 product into his shopping cart would it be correct this?
FOR EACH product into the shopping cart
{
    SELECT id FROM product where id=produc_id AND sold=0 LOCK IN SHARE MODE;
    IF num_rows == 0
    {
	    ROLLBACK
	    EXIT
    }
}
//if I arrive here all the SELECTs succeeded, all the products are locked
//and now I can safely go on updating the "sold" field of each product
//in the same way as explained in the 1st post except for the SELECT
//which doesn't need to be executed anymore

  • 0

#5 gregwarner

gregwarner

    Obi Wan of Programming

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1586 posts
  • Location:Arkansas
  • Programming Language:C, Java, C++, C#, PHP, Transact-SQL

Posted 21 August 2012 - 10:15 AM

There's no need to do the rollback inside the if statement. If nothing was selected, nothing gets locked. What's worse is, the rollback releases all the other locks you may have already obtained.

It would be better to construct one select statement for all the products, rather than doing a loop like this. That would obtain all the locks at the same time.
  • 0

ti-99-sig.png
Hofstadter's Law: It always takes longer than you expect, even when you take into account Hofstadter's Law.
– Douglas Hofstadter, Gödel, Escher, Bach: An Eternal Golden Braid


#6 Alhazred

Alhazred

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 133 posts

Posted 21 August 2012 - 10:25 AM

Good advice.
By the way I'm reading on MySQL Reference Manual that LOCK IN SHARE MODE prevents a row to be updated by another transactions, but the row can be read and this scenario can happen
- 1st user's SELECT is executed, the row can be read but not updated by any other transaction
- 2nd user's SELECT is executed before COMMIT or ROLLBACK, the row is locked also by this SELECT and it will not be possible to update it outside the transaction, by the 1st transaction neither
- both the users are in a deadlock
  • 0

#7 gregwarner

gregwarner

    Obi Wan of Programming

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1586 posts
  • Location:Arkansas
  • Programming Language:C, Java, C++, C#, PHP, Transact-SQL

Posted 21 August 2012 - 11:13 AM

Better use SELECT ... FOR UPDATE instead then.

EDIT:
Note, the documentation also says that a searched UPDATE will also lock the rows it's modifying as well.
  • 0

ti-99-sig.png
Hofstadter's Law: It always takes longer than you expect, even when you take into account Hofstadter's Law.
– Douglas Hofstadter, Gödel, Escher, Bach: An Eternal Golden Braid


#8 Alhazred

Alhazred

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 133 posts

Posted 21 August 2012 - 11:31 AM

Yes, I think I will use the SELECT ... FOR UPDATE.
Thank you.
  • 0





Also tagged with one or more of these keywords: pseudocode, mysql

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download