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=dff68160...

Author: Peter Dunkleypeter.dunkley@crocodile-rcs.com Committer: Peter Dunkleypeter.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=dff6...

---

sr-dev mailing list sr-dev@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev

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#id254832... 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=dff68160...

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=dff6...

---

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