[sr-dev] mysql driver update

[Hugh Waite]

Hello,
The locking on these tables was implemented to prevent some deadlock 
situations we saw.
With Postgres, we run a SELECT ... FOR UPDATE command which locks the 
selected rows followed by an UPDATE. Then only the process that locked 
them can write to them. If another process does a similar command, it is 
possible for some of the same rows to be selected for locking, but in a 
different order. When two rows are locked in the opposite order, you end 
up with a deadlock.
The only solution we found was to lock the entire table for writing 
while the SELECT and UPDATE commands were run.

Unfortunately, I'm not familiar with MySQL locks to say what might be 
appropriate in this case.

Regards,
Hugh

On 30/07/2013 05:22, Shane Harrison wrote:
> Hi Peter,
>
> In thread:
> http://lists.kamailio.org/pipermail/sr-dev/2013-January/018347.html
> you mentioned transaction support that you have added to PostgreSQL 
> driver. Unfortunately I do need to use mysql as our HA solution is 
> based on mysql cluster.
>
> Hence I have implemented the required new functions e.g start_transaction.
>
> However mysql only has one table locking mode (unlike PostgreSQL) and 
> if you lock a table within a transaction, you can't read other tables 
> without also obtaining locks for them as well.  As there are places 
> where active_watchers table is locked but the code tries to query the 
> watchers table, this is an issue.  I have simply disabled the table 
> locking for the time being.
>
> It is possible that the implicit transaction locking that mysql is 
> enough to solve the issues you were trying to solve.  To determine 
> this, can you tell me or point to a thread that explains what race 
> conditions were trying to be mitigated in process_notifier mode.
>
> Cheers and thanks
> Shane
>
> -- 
> Imagination NZ Ltd
> Level 6
> 92 Queens Drive
> P0 Box 30449
> Lower Hutt 5040
>
> +64 4 5703870 Extn 875
> +64 21 608919  (mobile)
>
>
> _______________________________________________
> sr-dev mailing list
> sr-dev at lists.sip-router.org
> http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev


-- 
Hugh Waite
Principal Design Engineer
Crocodile RCS Ltd.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sip-router.org/pipermail/sr-dev/attachments/20130730/359aab29/attachment.html>