Flask SqlAlchemy read lock the a row

Question

I would like to have a code to be able to lock the row and restart the transaction. I only need this code for a specific call.

class Mapper(db.Model):
    __tablename__ = 'dns_mappers'
    id = db.Column(db.Integer, primary_key=True)
    services_id = db.Column(db.Integer, db.ForeignKey('services.id'), nullable=False)
    eni = db.Column(db.String(80), unique=False, index=True, nullable=True, default="Primary")
    interface_type = db.Column(db.Integer, nullable=True, default=InterfaceType.secondary.value)
    name = db.Column(db.String(80), unique=True, nullable=True)
    type = db.Column(db.Integer, nullable=True, default=DnsType.route53.value, index=True)
    enabled = db.Column(db.Integer, nullable=True, default=0)
    instance_id = db.Column(db.String(80), nullable=True, index=True)
    created_at = db.Column(db.DateTime(timezone=True), server_default=func.now())
    updated_at = db.Column(db.DateTime(timezone=True), onupdate=func.now())
    last_assignment_completed_at = db.Column(db.DateTime(timezone=True), onupdate=func.now())
    last_released_at = db.Column(db.DateTime(timezone=True), nullable=True)
    subnet_id = db.Column(db.String(80), nullable=True, index=True)
    zone = db.Column(db.String(80), nullable=True, index=True)
    dns_group_id = db.Column(db.Integer, nullable=True, default=None, index=True)


#---
def available_by_service_id_dnstype_zone_subnet_group_id(user):
    return Mapper.query.filter_by(services_id=service_id, enabled=EnableStatus.enabled.value, type=type,
                                                 zone=zone,
                                                 subnet_id=subnet_id,
                                                 interface_type=interface_type,
                                                 dns_group_id=dns_group_id) \
                    .order_by(DnsMapper.created_at.asc(), DnsMapper.updated_at.asc()).limit(1).with_for_update().one()

However, I am getting dead-lock expectations. Not sure how to avoid this lock. I am getting a sql-alchemy deadlock exception.

EDIT-2

Documenting the exception received: (pymysql.err.OperationalError) (1213, 'Deadlock found when trying to get lock; try restarting transaction')

A deadlock in MySQL happens when two or more transactions mutually hold and request for locks, creating a cycle of dependencies. ... InnoDB automatically detects transaction deadlocks, rollbacks a transaction immediately and returns an error. It uses a metric to pick the easiest transaction to rollback.This means your accessing/modifying the same data simultaneously. They state not to use LOCKS on tables and to use transactions only. https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks.html. — Alex, Apr 16 '20 at 13:17

score 2 · Accepted Answer · answered Apr 22 '20 at 22:21

Finally, I think I have found a basic solution. Based on https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks.html and suggestion from Dan Grossman on https://stackoverflow.com/questions/6925769/restarting-transaction-in-mysql-after-deadlock. I think I should write a wrapper; however, this is the only place in my code where I need this behavior.

MAXIMUM_RETRY_ON_DEADLOCK=5

def available_by_service_id_dnstype_zone_subnet_group_id(user):
  attempt_count = 0
  lock_messages_error = ['Deadlock found', 'Lock wait timeout exceeded']
  while attempt_count < MAXIMUM_RETRY_ON_DEADLOCK:
    try:
      return Mapper.query.filter_by(services_id=service_id, enabled=EnableStatus.enabled.value, type=type,
                                                 zone=zone,
                                                 subnet_id=subnet_id,
                                                 interface_type=interface_type,
                                                 dns_group_id=dns_group_id) \
                    .order_by(DnsMapper.created_at.asc(), DnsMapper.updated_at.asc()).limit(1).with_for_update().one()
    except OperationalError as e:
      if any(msg in str(e) for msg in lock_messages_error) \
              and attempt_count <= MAXIMUM_RETRY_ON_DEADLOCK:
          print('Deadlock detected. Trying sql transaction once more. Attempts count: %s'
                        % (attempt_count + 1))
          logger.error('Deadlock detected. Trying sql transaction once more. Attempts count: %s'
                        % (attempt_count + 1))
          time.sleep(1)
          db.session.rollback()
          db.session.close()
      else:
          raise
    attempt_count += 1
    if attempt_count >= MAXIMUM_RETRY_ON_DEADLOCK:
        raise Exception("Deadlock issue retried :{}".format(MAXIMUM_RETRY_ON_DEADLOCK))

Flask SqlAlchemy read lock the a row

1 Answers1