[Freeswitch-users] Lock wait timeout exceeded; try restarting transaction

[Steven Ayre]

Run 'SHOW ENGINE INNODB STATUS\G'

What does it show in the latest deadlock section?



On 17 July 2013 14:16, Lloyd Aloysius <lloyd.aloysius at gmail.com> wrote:

> This is newly installed switch. Only one call and two registrations. I do
> not do any queries against the DB. I do not see the problem again in last
> 24 hours. But want to know how to solve this issue.
>
> Switch - 2 Quad Core / 24 GB Memory / 15RPM SCSI , DB - 2 Quad Core / 24
> GB Memory / 15RPM SCSI . Switch and DB connected through a cross over
> cable directly. I do not think this related to a environment issue.
>
> Thanks
> Lloyd
>
>
>
> On Tue, Jul 16, 2013 at 7:01 AM, Steven Ayre <steveayre at gmail.com> wrote:
>
>> +1
>>
>> The error is the actual MySQL error, so yes it's definitely a lock
>> timeout.
>>
>> This means two different queries were trying to access the same data at
>> the same time. Normally one'll wait until the other is finished, but if it
>> waits too long you get the lock timeout error.
>>
>> It can be caused by a query taking too long, or a transaction modifying
>> the data but then taking too long before commit. To avoid that make sure
>> MySQL is tuned correctly. Good indexes on the tables and good sizes for the
>> cache buffers.
>>
>> Make sure you're using a proper index for the delete operation, I'd also
>>> avoid ORs if possible.
>>
>>
>> The query is part of mod_sofia, so isn't modifiable. Having a
>> KEY(hostname,expires) on sip_dialogs may help though if it doesn't already
>> exist (check with SHOW CREATE TABLE sip_dialogs).
>>
>> Avoid accessing sip_dialogs from any of your own code to minimise the
>> number of things that might block each other.
>>
>> -Steve
>>
>>
>>
>> On 16 July 2013 06:48, Rafal Gwizdala <rafal.gwizdala at gmail.com> wrote:
>>
>>> I'd bet this is a lock timeout, caused by database operation taking too
>>> long.
>>> Make sure you're using a proper index for the delete operation, I'd also
>>> avoid ORs if possible.
>>> R
>>>
>>>
>>> On Tue, Jul 16, 2013 at 4:21 AM, Lloyd Aloysius <
>>> lloyd.aloysius at gmail.com> wrote:
>>>
>>>> Hi,
>>>>
>>>> I got the following error
>>>>
>>>> 2013-07-15 21:47:41.061450 [ERR] switch_odbc.c:514 ERR: [delete from
>>>> sip_dialogs where (expires = -1 or (expires > 0 and expires <= 1373939087))
>>>> and hostname='mydomain.com']
>>>> [STATE: HY000 CODE 1205 ERROR: [unixODBC][MySQL][ODBC 5.1
>>>> Driver][mysqld-5.5.32]Lock wait timeout exceeded; try restarting
>>>> transaction]
>>>>
>>>> and the odbcinst.ini
>>>>
>>>> [MySQL]
>>>> Description     = ODBC for MySQL
>>>> Driver          = /usr/lib/libmyodbc5.so
>>>> Setup           = /usr/lib/libodbcmyS.so
>>>> Driver64        = /usr/lib64/libmyodbc5.so
>>>> Setup64         = /usr/lib64/libodbcmyS.so
>>>> FileUsage       = 1
>>>> Threading       = 0
>>>> UsageCount      = 1
>>>>
>>>> This is a mysql memory leak? How can fix this error?
>>>>
>>>> DB Server running with 24 GB memory and 2 Quad Core Xeon
>>>>
>>>>
>>>> Thanks
>>>> Lloyd
>>>>
>>>>
>>>> _________________________________________________________________________
>>>> Professional FreeSWITCH Consulting Services:
>>>> consulting at freeswitch.org
>>>> http://www.freeswitchsolutions.com
>>>>
>>>> 
>>>> 
>>>>
>>>> Official FreeSWITCH Sites
>>>> http://www.freeswitch.org
>>>> http://wiki.freeswitch.org
>>>> http://www.cluecon.com
>>>>
>>>> FreeSWITCH-users mailing list
>>>> FreeSWITCH-users at lists.freeswitch.org
>>>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>>>> UNSUBSCRIBE:
>>>> http://lists.freeswitch.org/mailman/options/freeswitch-users
>>>> http://www.freeswitch.org
>>>>
>>>>
>>>
>>> _________________________________________________________________________
>>> Professional FreeSWITCH Consulting Services:
>>> consulting at freeswitch.org
>>> http://www.freeswitchsolutions.com
>>>
>>> 
>>> 
>>>
>>> Official FreeSWITCH Sites
>>> http://www.freeswitch.org
>>> http://wiki.freeswitch.org
>>> http://www.cluecon.com
>>>
>>> FreeSWITCH-users mailing list
>>> FreeSWITCH-users at lists.freeswitch.org
>>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>>> http://www.freeswitch.org
>>>
>>>
>>
>> _________________________________________________________________________
>> Professional FreeSWITCH Consulting Services:
>> consulting at freeswitch.org
>> http://www.freeswitchsolutions.com
>>
>> 
>> 
>>
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://wiki.freeswitch.org
>> http://www.cluecon.com
>>
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users at lists.freeswitch.org
>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>> http://www.freeswitch.org
>>
>>
>
> _________________________________________________________________________
> Professional FreeSWITCH Consulting Services:
> consulting at freeswitch.org
> http://www.freeswitchsolutions.com
>
> 
> 
>
> Official FreeSWITCH Sites
> http://www.freeswitch.org
> http://wiki.freeswitch.org
> http://www.cluecon.com
>
> FreeSWITCH-users mailing list
> FreeSWITCH-users at lists.freeswitch.org
> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> http://www.freeswitch.org
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.freeswitch.org/pipermail/freeswitch-users/attachments/20130717/87e18706/attachment.html