List:General Discussion« Previous MessageNext Message »
From:Ben Clewett Date:May 13 2008 11:00am
Subject:Re: Data Modelling
View as plain text  
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
>>>
>>
> 
> 
Thread
Data ModellingKrishna Chandra Prajapati13 May
  • Re: Data ModellingAnanda Kumar13 May
    • Re: Data ModellingKrishna Chandra Prajapati13 May
      • Re: Data ModellingBen Clewett13 May
        • Re: Data ModellingKrishna Chandra Prajapati13 May
  • Re: Data ModellingBen Clewett13 May
    • Re: Data ModellingKrishna Chandra Prajapati13 May