CODEWITHSUNDEEP
postgresqlflasksqlalchemyflask-sqlalchemy
Sviatoslav
Sviatoslav

Reputation: 26

Lock a table or rows to check a condition and insert a row

I need to do the sequential steps:

  1. to lock a table or specific rows (with a condition) for writing;
  2. to check some rows with specific conditions;
  3. if there are no rows with the conditions, add a new row
  4. unlock

How can I do that in SQLAlchemy, Flask, PostgreSQL? What ways are correct?

Upvotes: 0

Views: 622

Answers (2)

ionheart
ionheart

Reputation: 311

I'm using Flask-Sqlalchemy, so try to make adjustments.

You can use with_for_update() here. docs

An example would be.

class TestTable(db.Model):
    __tablename__ = "test_table"
    id = Column(db.Integer(), primary_key=True)
    filename = Column(db.String(1024))
    is_complete = Column(db.Boolean(), default=False, server_default="f")

You can lock them by doing:

for table in TestTable.query.with_for_update().filter_by(is_complete=False).all():
    # Example Check condition
    if table.filename != 'bar' and table.id >= 10:
       # Add a new row
       newtable = TestTable(filename='foo')
       db.session.add(newtable)

# Save and release lock
db.session.commit()

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 248305

If you want that to be safe from race conditions, you need to use a transaction with SERIALIZABLE isolation level. Perform the whole operation in that transaction.

Upvotes: 0

Related Questions