[sr-dev] pua db_mode DB_LOCKING_WRITE

[Richard Good]

Hi

We have noticed when using pua module with db_mode set to 1 the lock type
defaults to DB_LOCKING_WRITE which locks the whole table and can cause
performance issues.

I noticed this fix for the presence module last year specifically on this
issue:
http://lists.kamailio.net/pipermail/sr-dev/2014-January/022820.html

I've attached an almost identical patch for pua I want to commit.  The
patch makes this a configurable parameter that defaults to DB_LOCKING_WRIT.

If no one raises any concerns I will go ahead and commit.

Regards
Richard.

-- 


This email is subject to the disclaimer of Smile Communications at http://www.smilecoms.com/home/email-disclaimer/ <http://www.smilecoms.com/disclaimer>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sip-router.org/pipermail/sr-dev/attachments/20150116/cd02c2cd/attachment.html>
-------------- next part --------------
diff --git a/modules/pua/doc/pua_admin.xml b/modules/pua/doc/pua_admin.xml
index e7b242d..0d7ccba 100644
--- a/modules/pua/doc/pua_admin.xml
+++ b/modules/pua/doc/pua_admin.xml
@@ -287,6 +287,28 @@ modparam("pua", "db_mode", 0)
 		</example>
 	</section>
 	<section>
+	    <title><varname>db_table_lock_type</varname> (integer)</title>
+	    <para>
+		Enable (=1) or disable (=0) the Locks for table during an transaction.
+		Locking only the "current" table causes problems with a MySQL-Databases
+		in "DB-Only" mode.
+	    </para>
+	    <para>
+		<emphasis>
+		    Default value is 1 (Write Lock for the Tables).
+		</emphasis>
+	    </para>
+	    <example>
+		<title>Set <varname>db_table_lock_type</varname> parameter</title>
+		<programlisting format="linespecific">
+...
+modparam("pua", "db_table_lock_type", 0)
+...
+</programlisting>
+	    </example>
+	</section>
+	
+	<section>
 		<title><varname>check_remote_contact</varname> (int)</title>
 		<para>
 		When sending a SUBSCRIBE check that the remote contact matches the
diff --git a/modules/pua/pua.c b/modules/pua/pua.c
index e44a38d..8122b24 100644
--- a/modules/pua/pua.c
+++ b/modules/pua/pua.c
@@ -73,6 +73,9 @@ int reginfo_increase_version = 0;
 pua_event_t* pua_evlist= NULL;
 int dbmode = 0;
 
+int db_table_lock_type = 1;
+db_locking_t db_table_lock = DB_LOCKING_WRITE;
+
 int pua_fetch_rows = 500;
 
 /* database connection */
@@ -134,6 +137,7 @@ static param_export_t params[]={
 	{"check_remote_contact",     INT_PARAM, &check_remote_contact},
 	{"db_mode",                  INT_PARAM, &dbmode},
 	{"fetch_rows",               INT_PARAM, &pua_fetch_rows},
+	{ "db_table_lock_type",     INT_PARAM, &db_table_lock_type},
 	{0,                          0,         0}
 };
 
@@ -280,10 +284,13 @@ static int mod_init(void)
 			register_timer(db_update, 0, update_period);
 	}
 
+	if (db_table_lock_type != 1)
+		db_table_lock = DB_LOCKING_NONE;
+	
 	if(pua_db)
 		pua_dbf.close(pua_db);
 	pua_db = NULL;
-
+	
 	return 0;
 }
 
diff --git a/modules/pua/send_publish.c b/modules/pua/send_publish.c
index c04163e..8cd553b 100644
--- a/modules/pua/send_publish.c
+++ b/modules/pua/send_publish.c
@@ -49,6 +49,8 @@
 #include "event_list.h"
 #include "pua_db.h"
 
+extern db_locking_t db_table_lock;
+
 str* publ_build_hdr(int expires, pua_event_t* ev, str* content_type, str* etag,
 		str* extra_headers, int is_body)
 {
@@ -216,7 +218,7 @@ void publ_cback_func(struct cell *t, int type, struct tmcb_params *ps)
 
 	if (dbmode == PUA_DB_ONLY && pua_dbf.start_transaction)
 	{
-		if (pua_dbf.start_transaction(pua_db, DB_LOCKING_WRITE) < 0)
+		if (pua_dbf.start_transaction(pua_db, db_table_lock) < 0)
 		{
 			LM_ERR("in start_transaction\n");
 			goto error;
@@ -502,7 +504,7 @@ int send_publish( publ_info_t* publ )
 	
 	if (dbmode == PUA_DB_ONLY && pua_dbf.start_transaction)
 	{
-		if (pua_dbf.start_transaction(pua_db, DB_LOCKING_WRITE) < 0)
+		if (pua_dbf.start_transaction(pua_db, db_table_lock) < 0)
 		{
 			LM_ERR("in start_transaction\n");
 			goto error;
diff --git a/modules/pua/send_subscribe.c b/modules/pua/send_subscribe.c
index 17a4f3f..67aa9d2 100644
--- a/modules/pua/send_subscribe.c
+++ b/modules/pua/send_subscribe.c
@@ -48,6 +48,7 @@
 #include "pua_db.h"
 #include "../presence/subscribe.h"
 
+extern db_locking_t db_table_lock;
 
 void print_subs(subs_info_t* subs)
 {
@@ -301,7 +302,7 @@ void subs_cback_func(struct cell *t, int cb_type, struct tmcb_params *ps)
 
 	if (dbmode == PUA_DB_ONLY && pua_dbf.start_transaction)
 	{
-		if (pua_dbf.start_transaction(pua_db, DB_LOCKING_WRITE) < 0)
+		if (pua_dbf.start_transaction(pua_db, db_table_lock) < 0)
 		{
 			LM_ERR("in start_transaction\n");
 			goto error;
@@ -687,7 +688,7 @@ faked_error:
 
 		if (pua_dbf.start_transaction)
 		{
-			if (pua_dbf.start_transaction(pua_db, DB_LOCKING_WRITE) < 0)
+			if (pua_dbf.start_transaction(pua_db, db_table_lock) < 0)
 			{
 				LM_ERR("in start_transaction\n");
 				goto error;
@@ -986,7 +987,7 @@ int send_subscribe(subs_info_t* subs)
 
 	if (dbmode == PUA_DB_ONLY && pua_dbf.start_transaction)
 	{
-		if (pua_dbf.start_transaction(pua_db, DB_LOCKING_WRITE) < 0)
+		if (pua_dbf.start_transaction(pua_db, db_table_lock) < 0)
 		{
 			LM_ERR("in start_transaction\n");
 			goto error;