Table level locking is inherent to MyIsam.
Look into partitioning, as this breaks table into two or more other
tables which will lock separately.
Or use InnoDB:
ALTER TABLE ... SET ENGINE=InnoDB; (I think)
Ben
Krishna Chandra Prajapati wrote:
> Hi,
>
> I am looking for a solution to reduce the table locks as mytop shows that
> table gets locked very frequently. During report generation.
>
> Thanks,
> Prajapati
>
> On Tue, May 13, 2008 at 1:10 PM, Ananda Kumar <anandkl@stripped> wrote:
>
>> what is the reason for creating main_dup. If your thinking of taking a
>> backup of all the changes from main table, then the trigger also will have
>> to wait, till the locks on the main table are released.
>>
>> This trigger would add another feather to the lock/load on the machine.
>>
>> regards
>> anandkl
>>
>>
>> On 5/13/08, Krishna Chandra Prajapati <prajapatikc@stripped> wrote:
>>> Hi,
>>>
>>> Generally, in data modelling there are some independent table, user
>>> related
>>> tables, master tables and one main table which is related with most of
>>> the
>>> tables. Almost in all the queries main table is involved to fetch the
>>> `id`.
>>> In this way main table is used maximum. some times the main table gets
>>> locked due to the other query. When the hits on database server
>>> increases
>>> the locking time will increase.
>>>
>>> Is there any way to reduce the locking time of main table.
>>> for eg main_dup can be created. To reflect all the changes from main
>>> table
>>> to main_dup trigger can be used. So main_dup can be used to reduce the
>>> locking time of table.
>>>
>>> The above is my idea. Is there any other way to reduce the locking
>>> period.
>>>
>>> --
>>> Krishna Chandra Prajapati
>>> MySQL DBA,
>>> Ed Ventures e-Learning Pvt.Ltd.
>>> 1-8-303/48/15, Sindhi Colony
>>> P.G.Road, Secunderabad.
>>> Pin Code: 500003
>>> Office Number: 040-66489771
>>> Mob: 9912924044
>>> URL: ed-ventures-online.com
>>> Email-id: prajapatikc@stripped
>>>
>>
>
>
