SQlite db connection held open about automation-tools

[ross-spencer]

Instances of session in transfer.py:

$ cat transfers/transfer.py | ack -A 2 -B 2 session

def get_status(am_url, am_user, am_api_key, ss_url, ss_user, ss_api_key,
               unit_uuid, unit_type, session, hide_on_complete=False,
               delete_on_complete=False):
    """
--
                    unit_uuid, unit_info.get('sip_uuid'))
        # Update DB to refer to this one
        db_unit = session.query(models.Unit).filter_by(
            unit_type=unit_type, uuid=unit_uuid).one()
        db_unit.unit_type = 'ingest'
--
def call_start_transfer_endpoint(
        am_url, am_user, am_api_key, target, transfer_type, accession,
        ts_location_uuid, session):
    """Make the call to the start_transfer endpoint and return the unapproved
    directory name.
--
        new_transfer = models.Unit(
            path=target, unit_type='transfer', status='FAILED', current=False)
        session.add(new_transfer)
        return None

--
def start_transfer(ss_url, ss_user, ss_api_key, ts_location_uuid, ts_path,
                   depth, am_url, am_user, am_api_key, transfer_type,
                   see_files, session, config_file):
    """
    Starts a new transfer.
--
    :param bool see_files: If true, start transfers from files as well as
                           directories
    :param session: SQLAlchemy session with the DB
    :returns: Tuple of Transfer information about the new transfer or None on
              error.
    """
    # Start new transfer
    completed = {x[0] for x in session.query(models.Unit.path).all()}
    target = get_next_transfer(
        ss_url, ss_user, ss_api_key, ts_location_uuid, ts_path, depth,
--
        am_url=am_url, am_user=am_user, am_api_key=am_api_key, target=target,
        transfer_type=transfer_type, accession=accession,
        ts_location_uuid=ts_location_uuid, session=session)
    if not target_name:
        LOGGER.info("Cannot begin transfer with target_name: %s", target_name)
--
                uuid=result, path=target, unit_type='transfer', current=True)
            LOGGER.info('New transfer: %s', new_transfer)
            session.add(new_transfer)
            break
        LOGGER.info('Failed approve, try %s of %s', i + 1, retry_count)
--
        new_transfer = models.Unit(
            uuid=None, path=target, unit_type='transfer', current=False)
        session.add(new_transfer)
        return None

--
                    os.path.join(THIS_DIR, 'transfers.db')))

    session = models.Session()

    # Check for evidence that this is already running
--
    current_unit = None
    try:
        current_unit = session.query(models.Unit).filter_by(current=True).one()
        unit_uuid = current_unit.uuid
        unit_type = current_unit.unit_type
--
        # Get status
        status_info = get_status(am_url, am_user, am_api_key, ss_url, ss_user,
                                 ss_api_key, unit_uuid, unit_type, session,
                                 hide_on_complete, delete_on_complete)
        LOGGER.info('Status info: %s', status_info)
--
    if status == 'PROCESSING':
        LOGGER.info('Current transfer still processing, nothing to do.')
        session.commit()
        return 0
    # If waiting on input, send email, exit
--
        )
        current_unit.microservice = microservice
        session.commit()
        return 0
    # If failed, rejected, completed etc, start new transfer
--
        ss_url, ss_user, ss_api_key, ts_uuid, ts_path,
        depth, am_url, am_user, am_api_key,
        transfer_type, see_files, session, config_file)

    session.commit()
    return 0 if new_transfer else 1

[ross-spencer]

Some best practice about sessions and closing them can be found here on the Sqlalchemy site: https://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it

tl;dr;

As a general rule, keep the lifecycle of the session separate and external from functions and objects that access and/or manipulate database data. This will greatly help with achieving a predictable and consistent transactional scope.

Make sure you have a clear notion of where transactions begin and end, and keep transactions short, meaning, they end at the series of a sequence of operations, instead of being held open indefinitely.

[ross-spencer]

Resolved via: bc93ff2

[finoradin]

NOICE!