Hi Klaus,

I have added some helper functions to SRDB1 to wrapper db_raw_query() to do BEGIN/COMMIT/ROLLBACK.  I have updated the parts of RLS that will benefit from this and will do the same to presence when I complete the presence notifier development I am working on.

Thanks,

Peter

On Fri, 2012-04-20 at 12:09 +0200, Klaus Darilion wrote: 
Yes, I meant BEGIN/COMMIT stuff. I am not that familiar with Kamailio's 
DB layer if it has a wrapper for DB-transactions, but I would expect 
that this can be done using raw SQL queries.

The point was just that I just had another project were we had problems 
with the auto_reconnect parameters, as some proprietary modules were 
using transactions.

http://www.kamailio.org/docs/modules/3.2.x/modules/db_postgres.html#id2548325
http://www.kamailio.org/docs/modules/3.2.x/modules/db_mysql.html#id2530866

If the DB module has automatic reconnect enabled, then, if the query 
fails because of connection problems, the query is just repeated as the 
client library will then reconnect to the DB.

Consider the following scenario:

BEGIN
SELECT ... FOR UPDATE;
UPDATE ....
UPDATE ....
---- TCP connection is lost --> automatic rollback
---- automatic reconnect ......
UPDATE ....
UPDATE ....
COMMIT


In this case the last UPDATEs where done outside the transaction. I 
think DB-transactions (BEGIN/COMMIT) are currently not used by modules, 
but if they are used, automatic reconnect must be disabled. (maybe we 
need (or already have) a wrapper in the DB API for transaction which 
handles this correctly).


regards
Klaus


On 20.04.2012 10:31, Peter Dunkley wrote:
> Hi Klaus,
>
> By wrapping the queries into a DB transaction do you mean something
> along the lines of doing BEGIN/COMMIT/ROLLBACK in PostgreSQL around some
> sets of transactions? For example, the query selecting records with the
> updated flag set and subsequent query to unset the flag?
>
> If so this would be good to prevent multiple servers attempting to do
> the same set of updates at the same time. Is there an easy (and DB
> independent) way to do this Kamailio?
>
> I am not sure I understand the issue about auto_reconnect?
>
> Thanks,
>
> Peter
>
> On Fri, 2012-04-20 at 10:03 +0200, Klaus Darilion wrote:
>> Hi Peter!
>>
>> I remember we had some discussion that same queries may be wrapped into
>> a DB transaction to avoid inconsistencies when using multiple presence
>> servers.
>>
>> I just wonder if this is now the case or not. Because then,
>> auto_reconnect in the DB module must be disabled as it may happen that
>> the connection is closed/connected during a transaction.
>>
>> regards
>> Klaus
>>
>> On 18.04.2012 18:36, Peter Dunkley wrote:
>> >  Module: sip-router
>> >  Branch: master
>> >  Commit: dff68160e4decc43f2da8948ea03bc4d469ded96
>> >  URL:http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=dff68160e4decc43f2da8948ea03bc4d469ded96
>> >
>> >  Author: Peter Dunkley<peter.dunkley@crocodile-rcs.com  <mailto:peter.dunkley@crocodile-rcs.com>>
>> >  Committer: Peter Dunkley<peter.dunkley@crocodile-rcs.com  <mailto:peter.dunkley@crocodile-rcs.com>>
>> >  Date:   Wed Apr 18 17:29:32 2012 +0100
>> >
>> >  modules_k/rls: RLS full-state NOTIFY requests now sent by notifier process(es)
>> >
>> >  - Also modified the notifier process stuff to only work when in
>> >     DB only mode.  This is because the full-state handling stuff
>> >     in the notifier processes relies on DB only mode.
>> >  - Leaving the full-state stuff outside of the notifier process
>> >     didn't work because there was a row update race between the
>> >     notifier process and non-notifier process when full-state and non-
>> >     full-state NOTIFY requests were generated at the same time.
>> >  - This ensures that (with default options) you get at most one NOTIFY
>> >     (or set of NOTIFYs when splitting large NOTIFYs is enabled) per
>> >     5s per watcher from RLS.
>> >  - It also helps spread out the NOTIFY generation load more evenly
>> >     across time.
>> >
>> >  ---
>> >
>> >    modules_k/rls/README            |    6 ++
>> >    modules_k/rls/doc/rls_admin.xml |    8 ++
>> >    modules_k/rls/resource_notify.c |  175 +++++++++++++++++++++++++++++++++++++--
>> >    modules_k/rls/rls.c             |   25 ++++--
>> >    modules_k/rls/rls.h             |    3 +
>> >    modules_k/rls/rls_db.c          |   20 ++++-
>> >    modules_k/rls/subscribe.c       |   40 ++++++----
>> >    7 files changed, 240 insertions(+), 37 deletions(-)
>> >
>> >  Diff:http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commitdiff;h=dff68160e4decc43f2da8948ea03bc4d469ded96
>> >
>> >  _______________________________________________
>> >  sr-dev mailing list
>> >  sr-dev@lists.sip-router.org  <mailto:sr-dev@lists.sip-router.org>
>> >  http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
>
> --
> Peter Dunkley
> Technical Director
> Crocodile RCS Ltd
>


-- 
Peter Dunkley
Technical Director
Crocodile RCS Ltd