[win32] fix deadlock occuring on the sequence tables with SQLServer
actually, this deadlock would occur with any db backend other that PostgreSQL
as the previous code was heavily relying on PG's SEQUENCE facility, not available
elsewhere.
Deadlock description:
Thread1 starts creating entities (and therefore calls create_eid):
-> this creates a DB-level lock on the entities_id_seq table, which
will last until end of transaction
Thread2 calls create_eid, which acquires the Python lock object, but
updating the entities_id_seq is held by the DB lock
Thread1 wants to create a new entity, calls create_eid, and is stuck by
the Python lock object held by Thread2.
Solution:
use a separate connection to read and write the entities_id_seq table.
--- a/server/sources/native.py Wed Jun 02 16:05:40 2010 +0000
+++ b/server/sources/native.py Wed Jun 02 16:12:18 2010 +0000
@@ -255,6 +255,7 @@
# we need a lock to protect eid attribution function (XXX, really?
# explain)
self._eid_creation_lock = Lock()
+ self._eid_creation_cnx = self.get_connection()
# (etype, attr) / storage mapping
self._storages = {}
# entity types that may be used by other multi-sources instances
@@ -729,13 +730,48 @@
self.doexec(session, sql)
def create_eid(self, session):
- self._eid_creation_lock.acquire()
+ self.debug('create eid')
+ # lock needed to prevent 'Connection is busy with results for another command (0)' errors with SQLServer
+ self._eid_creation_lock.acquire()
+ try:
+ return self.__create_eid()
+ finally:
+ self._eid_creation_lock.release()
+
+ def __create_eid(self):
+ # internal function doing the eid creation without locking.
+ # needed for the recursive handling of disconnections (otherwise we
+ # deadlock on self._eid_creation_lock
+ if self._eid_creation_cnx is None:
+ self._eid_creation_cnx = self.get_connection()
+ cnx = self._eid_creation_cnx
+ cursor = cnx.cursor()
try:
for sql in self.dbhelper.sqls_increment_sequence('entities_id_seq'):
- cursor = self.doexec(session, sql)
- return cursor.fetchone()[0]
- finally:
- self._eid_creation_lock.release()
+ cursor.execute(sql)
+ eid = cursor.fetchone()[0]
+ except (self.OperationalError, self.InterfaceError):
+ # FIXME: better detection of deconnection pb
+ self.warning("trying to reconnect create eid connection")
+ self._eid_creation_cnx = None
+ return self.__create_eid()
+ except (self.DbapiError,), exc:
+ # We get this one with pyodbc and SQL Server when connection was reset
+ if exc.args[0] == '08S01':
+ self.warning("trying to reconnect create eid connection")
+ self._eid_creation_cnx = None
+ return self.__create_eid()
+ else:
+ raise
+ except: # WTF?
+ cnx.rollback()
+ self._eid_creation_cnx = None
+ self.exception('create eid failed in an unforeseen way on SQL statement %s', sql)
+ raise
+ else:
+ cnx.commit()
+ return eid
+
def add_info(self, session, entity, source, extid, complete):
"""add type and source info for an eid into the system table"""