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

[Steven Ayre]

+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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.freeswitch.org/pipermail/freeswitch-users/attachments/20130716/ee0b3661/attachment.html