[sr-dev] git:master: modules_k/pua: Use database row/ table locking where supported in DB only mode

[Peter Dunkley]

Module: sip-router
Branch: master
Commit: 92aedbb83f5d6cbfc7c4b5e68f260f7ecccc992e
URL:    http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=92aedbb83f5d6cbfc7c4b5e68f260f7ecccc992e

Author: Peter Dunkley <peter.dunkley at crocodile-rcs.com>
Committer: Peter Dunkley <peter.dunkley at crocodile-rcs.com>
Date:   Tue Aug 21 15:40:56 2012 +0100

modules_k/pua: Use database row/table locking where supported in DB only mode

- Under load there are lots of DB deadlocks when using
  (start|end)_transaction() with multiple presence processes and/or
  servers.
- Without using (start|end)_transaction() multiple processes/servers
  overwrite each others changes.
- Using row locking (where possible) and table locking (where
  required) fixes these problems.
- IMPORTANT NOTE: DB only, multi-process/multi-server, presence will
  only work properly under high-load when using a database driver that
  supports transactions and locking (currently just db_postgres).

---

 modules_k/pua/pua_db.c         |    6 ++++--
 modules_k/pua/send_publish.c   |    4 ++--
 modules_k/pua/send_subscribe.c |   22 ++++++++++++++++++++--
 3 files changed, 26 insertions(+), 6 deletions(-)

diff --git a/modules_k/pua/pua_db.c b/modules_k/pua/pua_db.c
index 05dd1e3..4be51bf 100644
--- a/modules_k/pua/pua_db.c
+++ b/modules_k/pua/pua_db.c
@@ -817,6 +817,7 @@ ua_pres_t *get_record_puadb(str pres_id, str *etag, ua_pres_t *result, db1_res_t
 	db_row_t *rows;
 	db1_res_t *res;
 	int n_query_cols = 0, nr_rows;
+	db_query_f query_fn = pua_dbf.query_lock ? pua_dbf.query_lock : pua_dbf.query;
 
 	q_cols[n_query_cols] = &str_pres_id_col;	
 	q_vals[n_query_cols].type = DB1_STR;
@@ -840,7 +841,7 @@ ua_pres_t *get_record_puadb(str pres_id, str *etag, ua_pres_t *result, db1_res_t
 		return(NULL);
 	}
 
-	if(pua_dbf.query(pua_db, q_cols, 0, q_vals,
+	if(query_fn(pua_db, q_cols, 0, q_vals,
 				NULL,n_query_cols,0,0,&res) < 0)
 	{
 		LM_ERR("DB query error\n");
@@ -1162,6 +1163,7 @@ ua_pres_t *get_dialog_puadb(str pres_id, str *pres_uri, ua_pres_t *result, db1_r
 	db_row_t *rows;
 	db1_res_t *res;
 	int n_query_cols = 0, nr_rows;
+	db_query_f query_fn = pua_dbf.query_lock ? pua_dbf.query_lock : pua_dbf.query;
 
 	if (pres_uri == NULL)
 	{
@@ -1188,7 +1190,7 @@ ua_pres_t *get_dialog_puadb(str pres_id, str *pres_uri, ua_pres_t *result, db1_r
 		return(NULL);
 	}
 
-	if(pua_dbf.query(pua_db, q_cols, 0, q_vals,
+	if(query_fn(pua_db, q_cols, 0, q_vals,
 				NULL,n_query_cols,0,0,&res) < 0)
 	{
 		LM_ERR("DB query error\n");
diff --git a/modules_k/pua/send_publish.c b/modules_k/pua/send_publish.c
index 97935c1..1f4ed37 100644
--- a/modules_k/pua/send_publish.c
+++ b/modules_k/pua/send_publish.c
@@ -216,7 +216,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) < 0)
+		if (pua_dbf.start_transaction(pua_db, DB_LOCKING_WRITE) < 0)
 		{
 			LM_ERR("in start_transaction\n");
 			goto error;
@@ -498,7 +498,7 @@ int send_publish( publ_info_t* publ )
 	
 	if (dbmode == PUA_DB_ONLY && pua_dbf.start_transaction)
 	{
-		if (pua_dbf.start_transaction(pua_db) < 0)
+		if (pua_dbf.start_transaction(pua_db, DB_LOCKING_WRITE) < 0)
 		{
 			LM_ERR("in start_transaction\n");
 			goto error;
diff --git a/modules_k/pua/send_subscribe.c b/modules_k/pua/send_subscribe.c
index 89a8141..1c10bf0 100644
--- a/modules_k/pua/send_subscribe.c
+++ b/modules_k/pua/send_subscribe.c
@@ -301,7 +301,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) < 0)
+		if (pua_dbf.start_transaction(pua_db, DB_LOCKING_WRITE) < 0)
 		{
 			LM_ERR("in start_transaction\n");
 			goto error;
@@ -676,6 +676,24 @@ faked_error:
 
 	if (dbmode==PUA_DB_ONLY)
 	{
+		if (pua_dbf.end_transaction)
+		{
+			if (pua_dbf.end_transaction(pua_db) < 0)
+			{
+				LM_ERR("in end_transaction\n");
+				goto error;
+			}
+		}
+
+		if (pua_dbf.start_transaction)
+		{
+			if (pua_dbf.start_transaction(pua_db, DB_LOCKING_WRITE) < 0)
+			{
+				LM_ERR("in start_transaction\n");
+				goto error;
+			}
+		}
+
 		if (convert_temporary_dialog_puadb(presentity) < 0)
 		{
 			LM_ERR("Could not convert temporary dialog into a dialog\n");
@@ -968,7 +986,7 @@ int send_subscribe(subs_info_t* subs)
 
 	if (dbmode == PUA_DB_ONLY && pua_dbf.start_transaction)
 	{
-		if (pua_dbf.start_transaction(pua_db) < 0)
+		if (pua_dbf.start_transaction(pua_db, DB_LOCKING_WRITE) < 0)
 		{
 			LM_ERR("in start_transaction\n");
 			goto error;