Re: Show innodb engine status tuning db server
Posted by:
Rick James
Date: September 29, 2013 08:40AM
A large table without an adequate index can take a long time to UPDATE or DELETE. That makes the transaction longer, and may 'lock' more of the table than intended.
Having several operations in a single transaction is reasonable and normal. Putting too much into a single transaction makes trouble. Doing lengthy things (such as waiting for a user to respond) in the transaction can lead to hitting "lock wait timeout".
Design each transaction to have all the statements it needs, but no more.
Going back to the original question... There is no tunable parameter that can solve your problem.
You showed us one query:
UPDATE tblMst SET ... WHERE mSN='BAC1014'
That will lock all the rows having mSN='BAC1014', and the index is helpful:
KEY `mSN` (`mSN`)
But the other queries in this transactions and the conflicting transaction are also involved.
Subject
Views
Written By
Posted
2476
September 15, 2013 09:37AM
1036
September 16, 2013 07:14PM
1574
September 17, 2013 08:52AM
1016
September 18, 2013 07:21PM
1080
September 19, 2013 09:08AM
989
September 23, 2013 12:48PM
982
September 24, 2013 08:45PM
1216
September 24, 2013 10:01PM
911
September 26, 2013 11:29PM
1044
September 27, 2013 03:53AM
932
September 27, 2013 11:52PM
1001
September 28, 2013 12:39PM
Re: Show innodb engine status tuning db server
950
September 29, 2013 08:40AM
956
October 01, 2013 01:43AM
974
October 01, 2013 10:14PM
889
October 01, 2013 10:35PM
919
October 02, 2013 07:43PM
1022
October 02, 2013 11:08PM
973
October 03, 2013 07:48PM
984
October 04, 2013 09:04PM
1058
October 06, 2013 01:43PM
876
October 07, 2013 09:06AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.