Forum Moderators: open
Hello,
I have some trouble with a script that is doing huge amount of update statements, and is locking a table all the time.
The update is like this 'update $xx_table set $v bbb=$bbb,lastview='$time' where id='$id'
It just saving some values each time, updating some numbers in the database.
What I could possible do to be able to do the update statements but not locking the tables almost all the time which brings the perfomance down?
Thanks for your time.
Perhaps this article will help.
[dev.mysql.com...]
It's hard to say what your problem might be, not knowing your application, how it is coded, use of transactions, etc.
[edited by: ZydoSEO at 6:36 pm (utc) on Dec. 30, 2007]
I will try to give lower priority for update statements and see how it goes. I have lots of SELECTS and many updates on the same tables.
BEGIN TRANSACTION
perform some logic to determine what the update statement needs to look like
Execute the update statement
perform some logic to determine what the insert statement needs to look like for a log table
Execute the insert statement
...
COMMIT TRANSACTION
So a lot of the time spent inside the transaction with tables locked was actually executing application logic or trying to dynamically build the SQL statements or stored proc calls. We found that 95% of the time inside the transaction in some cases was spent performing non-DB logic.
We greatly reduced time inside the transaction (and thus reducing the time that tables were locked) by executing all of the logic first to build a collection or array of SQL statements which need to be executed and THEN beginning a transaction, cycling through the array executing each statement, and then committing the transaction. So the logic above then became something along the lines of:
perform some logic to determine what the update statement needs to look like
Insert the update statement into collection/array
perform some logic to determine what the insert statement needs to look like for a log table
Insert the insert statement into collection/array
...
BEGIN TRANSACTION
For i = 1 to upperbound of collection/array
Execute the SQL in collection/array[i]
COMMIT TRANSACTION
This should help minimize the amount of time the tables are locked.
Sorry for the pseudocode... But hopefully it gets the point across.
[edited by: ZydoSEO at 10:44 pm (utc) on Dec. 30, 2007]
A possible solution might be to convert the offending table to InnoDB because it supports row locking. Please be aware that the InnoDB database driver in general takes more time per statement to execute compared to the MyISAM driver so it might not be a solution if you have a very busy system.
