From: Ben Clewett
Date: May 13 2008 11:00am
Subject: Re: Data Modelling
List-Archive: http://lists.mysql.com/mysql/212775
Message-Id: <482974C7.6090300@clewett.org.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>>>
>>
> 
>