Improve race conditions/MySQL reconnection
This commit aims to provide a better synchronization between all the
different threads in RO, specially regarding DB usage and internal state
consistency.
The following improvements were done:
1. Centralize database retry logic into a single function
This way we can change the procedure and the rules for retrying in a
single place and this reflects in several functions simultaneously
avoiding the need for manual copy and paste (and the potential risk of
forgetting to change somewhere)
2. Minor fixes/improvements related to database connection loss.
Previously `db_base` was already able to identify when the connection
to MySQL was lost, but apparently in a few edge cases the automatic
reconnection was not done.
3. Implement a transaction method
This method replaces the old context manager API for the connection
object that was removed from MySQLdb in version 1.4
In additional it is possible to use a decorator for transactions
(not only the context manager), which is handy sometimes.
4. Add lock mechanism directly to db_base
This helps to improve synchronization between threads.
Some extra synchronization was introduced to functions, as it seemed
to be the case.
Moreover, previously, the cursor object was part of the internal state
of the db_base object, and it was being changed/used without thread
synchronization (error-prone). Having the locking mechanism around the
changes in the cursor property of the class, avoids problems.
5. Add option to fork connection
Useful when independent threading is needed (as long as different
threads don't access the same database table, having separated
connections and locks should work fine).
Change-Id: I3ab34df5e8c2857d96ed14a70e7f65bd0b5189a0
Signed-off-by: Anderson Bravalheri <a.bravalheri@bristol.ac.uk>
diff --git a/osm_ro/tests/test_db.py b/osm_ro/tests/test_db.py
new file mode 100644
index 0000000..e152347
--- /dev/null
+++ b/osm_ro/tests/test_db.py
@@ -0,0 +1,123 @@
+# -*- coding: utf-8 -*-
+# pylint: disable=E1101
+import unittest
+
+from MySQLdb import connect, cursors, DatabaseError, IntegrityError
+import mock
+from mock import Mock
+
+from ..db_base import retry, with_transaction
+from ..nfvo_db import nfvo_db
+from .db_helpers import TestCaseWithDatabase
+
+
+class TestDbDecorators(TestCaseWithDatabase):
+ @classmethod
+ def setUpClass(cls):
+ connection = connect(cls.host, cls.user, cls.password)
+ cursor = connection.cursor()
+ cursor.execute(
+ "CREATE DATABASE IF NOT EXISTS {};".format(
+ connection.escape_string(cls.database)))
+ cursor.execute("use {};".format(cls.database))
+ cursor.execute("""\
+ CREATE TABLE IF NOT EXISTS `test_table` (\
+ `id` int(11) NOT NULL,
+ PRIMARY KEY (`id`)\
+ );\
+ """)
+ cursor.close()
+ connection.close()
+
+ @classmethod
+ def tearDownClass(cls):
+ cls.empty_database()
+
+ def setUp(self):
+ self.maxDiff = None
+ self.db = nfvo_db(self.host, self.user, self.password, self.database)
+ self.db.connect()
+ self.addCleanup(lambda: self.db.disconnect())
+
+ def db_run(self, query, cursor=None):
+ cursor = cursor or self.db.con.cursor()
+ cursor.execute(query)
+ return cursor.fetchone()
+
+ def test_retry_inject_attempt(self):
+ @retry
+ def _fn(db, attempt=None):
+ self.assertIsNotNone(attempt)
+ self.assertEqual(attempt.number, 1)
+
+ _fn(self.db)
+
+ def test_retry_accept_max_attempts(self):
+ success = []
+ failures = []
+
+ @retry(max_attempts=5)
+ def _fn(db, attempt=None):
+ if attempt.count < 4:
+ failures.append(attempt.count)
+ raise DatabaseError("Emulate DB error", "msg")
+ success.append(attempt.count)
+
+ _fn(self.db)
+ self.assertEqual(failures, [0, 1, 2, 3])
+ self.assertEqual(success, [4])
+
+ def test_retry_reconnect_auctomatically(self):
+ success = []
+ failures = []
+
+ @retry(max_attempts=3)
+ def _fn(db, attempt=None):
+ if attempt.count < 2:
+ failures.append(attempt.count)
+ db.con.close() # Simulate connection failure
+ result = self.db_run('select 1+1, 2+2;')
+ success.append(attempt.count)
+ return result
+
+ result = _fn(self.db)
+ self.assertEqual(failures, [0, 1])
+ self.assertEqual(success, [2])
+ self.assertEqual(result, (2, 4))
+
+ def test_retry_reraise_non_db_errors(self):
+ failures = []
+
+ @retry
+ def _fn(db, attempt=None):
+ failures.append(attempt.count)
+ raise SystemError("Non Correlated Error")
+
+ with self.assertRaises(SystemError):
+ _fn(self.db)
+
+ self.assertEqual(failures, [0])
+
+ def test_transaction_rollback(self):
+ with self.assertRaises(IntegrityError), \
+ self.db.transaction() as cursor:
+ # The first row is created normally
+ self.db_run('insert into test_table (id) values (1)', cursor)
+ # The second row fails due to repeated id
+ self.db_run('insert into test_table (id) values (1)', cursor)
+ # The entire transaction will rollback then, and therefore the
+ # first operation will be undone
+
+ count = self.db_run('select count(*) FROM test_table')
+ self.assertEqual(count, (0,))
+
+ def test_transaction_cursor(self):
+ with self.db.transaction(cursors.DictCursor) as cursor:
+ count = self.db_run('select count(*) as counter FROM test_table',
+ cursor)
+
+ self.assertEqual(count, {'counter': 0})
+
+
+if __name__ == '__main__':
+ unittest.main()