European Experts Exchange (EEE.org) :: Free Experts PHP MySql Oracle Microsoft Web SQL Delphi


Databases :: MySql :: Locking tables
By: blenkhn	Date: 24/07/2003 00:00:00	Points: 500	Status: Answered Quality : Excellent
I have an auction site and have noticed some really strange results when getting close to the end of teh sale. I believe that it is due to to many threads trying to work with the tables at once. Using perl can someone tell me how to lock the table for writes then release the table for the next write? I will need to lock two tables the 'proxy' table and the 'item' table. I am using the DBI module. Please be specific.
By: Mr_Peerapol	Date: 24/07/2003 04:43:00		Type : Comment
LOCK TABLES proxy READ, item READ . . (your existing code) . UNLOCK TABLES Peerapol

By: VGR	Date: 24/07/2003 04:56:00		Type : Comment
1) is not transaction-safe 2) "By using incremental updates (UPDATE customer SET value=value+new_value) or the LAST_INSERT_ID() function, you can avoid using LOCK TABLES in many cases."

By: blenkhn	Date: 24/07/2003 06:39:00		Type : Comment
VGR when you say it is not transactional safe what do you mean. The last few minutes of the auction made the script go wonky. Please explain incremnetal updates better. I am using the update function to update the price in the item table. but there are several different possiblilities for the update like the bid not enough to beat the original proxy and if the proxy is beat then the write needs to happen to two tables. the old proxy deleted, the new proxy inserted and the bid upgraded. Mr_Peerapol would the lock occur like $dbh->do("LOCK TABLES proxy READ, item READ"); and this would lock the processes till the write has been complete and then unlock the table which will then allow the reads?

By: VGR	Date: 24/07/2003 06:45:00		Type : Comment
I mean this : <A HREF="http://www.mysql.com/doc/en/LOCK_TABLES.html">http://www.mysql.com/doc/en/LOCK_TABLES.html</a>

By: Squibi	Date: 28/07/2003 04:30:00		Type : Answer
First of all, are you using InnoDB tables? If not you need to change to InnoDB, as it supports transactions (which you need for what you are doing). BEGIN; SELECT ... FROM proxy, item WHERE ... FOR UPDATE UPDATE .... SET .... COMMIT; This will lock the ROWS in question, not the entire table, and prevent reads and writes on the rows in question, along with certain gap rows, read the documentation on transactions at <A HREF="http://www.mysql.com/doc/en/InnoDB_transaction_model.html">http://www.mysql.com/doc/en/InnoDB_transaction_model.html</a> Regards,

By: VGR	Date: 28/07/2003 17:26:00		Type : Assist
no you don't "need" transactions, you "may use" transactions. You asked for LOCKing, you got the answer. It'll solve your problems

By: Squibi	Date: 28/07/2003 17:28:00		Type : Comment
Considering the high concurrency involved, I would say that Transactions, especially ones involving SELECT ... FOR UPDATE, are the best solution to the problem at hand, we are dealing with high concurrency here and In situations with multiple auctions ending at the same time table locks are going to be slower than row level locking combined with transactions.

By: blenkhn	Date: 05/08/2003 14:57:00		Type : Comment
I would like to thank all those who are answering my questions. i apologize for the delay in making any other comments as I have been out camping for the past week. (which is why I made it urgent) I think that my best answer has come from Squibi. The question that I have is can you have other code running inbetween the statements or is it all one statement? for example I would change my code to read: $sth = $dbh->prepare("SELECT Userid, Price, Item FROM Auction WHERE Item = '$i' FOR UPDATE"); $sth->execute; (@rowinfo) = $sth->fetchrow_array(); $sth->finish; then some other code for deciding if the bid is appropriate and then when doing the update: $dbh->do("UPDATE Auction SET Price = '$newlistprice' WHERE Item = '$i'"); How and where would I use the BEGIN and COMMIT statements?

By: Squibi	Date: 05/08/2003 15:02:00		Type : Comment
STart with BEGIN, run the SELECT .. FOR UPDATE, run the UPDATE, and then COMMIT. Your rows will be read/write loacked until teh COMMIT. Regards,

By: VGR	Date: 05/08/2003 15:19:00		Type : Comment
early wake up, squibi ;-)

By: Squibi	Date: 05/08/2003 15:21:00		Type : Comment
Late night actually, gotta make the world a safer place for MySQL users.

By: VGR	Date: 05/08/2003 15:25:00		Type : Comment
well, if it involves VB, you sure have a lot of work :D

By: Squibi	Date: 05/08/2003 15:45:00		Type : Comment
Hehe, only when doing windows apps, I stick to PHP for web stuff.

By: VGR	Date: 06/08/2003 03:51:00		Type : Comment
just to tell : do you know a true language, a fast compiler producing true EXEs and a nice IDE (all in one) whose name begins with "D" and ends with "elphi" ?

By: blenkhn	Date: 06/08/2003 08:16:00		Type : Comment
Please let me know if the two statements for the begin and commit statements are: $dbh->do("BEGIN WORK"); and $dbh->do("COMMIT WORK"); ?

By: Squibi	Date: 06/08/2003 09:34:00		Type : Comment
WORK should be omitted: $dbh->do("BEGIN"); and $dbh->do("COMMIT");

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

Contact

page generated in 248.520140 milliseconds

Why Google AdSense ads ?

New Improved Search!