Re: database transactions in smsd

[alonso]

On 07/17/2012 03:55 AM, Daniele Forsi wrote:
> 2012/7/17 alonso acuña:
>
>> Hello. I am having some issues with tables getting locked for a long time,
>> like 30 seconds while smsd is running alongside another program that also
>> uses transactions on the outbox table. The other program is getting stuck
> which version of smsd?
> it should be fixed in smsd 1.5.1
>
> http://git.savannah.gnu.org/cgit/gnokii.git/tree/smsd/ChangeLog

Hello. I am using:  smsd - version 1.5.2 from gnokii 0.6.31   .

The command that seems to be causing trouble is this one:
SELECT id, number, text, dreport FROM outbox WHERE processed='0' AND
CURTIME() >= not_before AND CURTIME() <= not_after AND phone = '1' LIMIT
1 FOR UPDATE

This waits most of the time for 30 seconds to get the needed locks. In
the meantime other commands also get stuck. It only happens when there
are sms to send.

I have a second smsd running for phone=2 and this sometimes hangs for 30
seconds but I never see both smsd hung. So perhaps one of them is
casuing the other and the rest of my commands to hang.

The other commands that I am running are like these:
update outbox set statusalto=3 where id=192   //simple update to a
single row
update outbox set processed_date=NOW(),processed=1,error=133 where
type=1 and processed_date='0000-00-00 00:00:00' and
insertdate<'1342536246' //set to error messages that are not processed
after some time.

As I understand the FOR UPDATE would only lock the relevant records and
this should cause no interference between both smsd.  My simple update
above would never run on a record that has not been processed so it
would never wait for that FOR UPDATE to end.

My question here is what is smsd doing after that FOR UPDATE and before
it closes the transaction? I have verified that messages take 1-2
seconds to be sent and I am sending very few messages. It takes 3
minutes for smsd to send 5 messages, most of this time is just wating
for database locks.

Thanks for your help.