CODEWITHSUNDEEP
mysqlmultithreadingsqlalchemy
Behoston
Behoston

Reputation: 151

SQL Alchemy multithread SELECT FOR UPDATE doesn't work

I have in MySQL database table containing "tasks". Each task have flag (if it's taken or not).
And now for example 3 threads do:

query_base = session.query(PredykcjaRow).filter(
    PredykcjaRow.predyktor == predictor,
    PredykcjaRow.czy_wziete == False
)
query_disprot = query_base.join(NieustrRow, NieustrRow.fastaId == PredykcjaRow.fastaId)

query_pdb = query_base.join(RawBialkoRow, RawBialkoRow.fasta_id == PredykcjaRow.fastaId)
response = query_pdb.union(query_disprot)
response = response.with_for_update()
response = response.first()
if response is None:
    return None
response.czy_wziete = True
try:
    session.commit()
    return response
except:
    return None

each thread have own session (ScopedSession) but all 3 threads get the same object.

In configuration

tx_isolation..... REPEATABLE-READ

Upvotes: 0

Views: 643

Answers (2)

Behoston
Behoston

Reputation: 151

The problem is union statement. MySQL does not provide accumulative SELECTS with FOR UPDATE - it execute without warning, but row is not locked.
I found this information in official documentation but now I can't. If anyone can, please post comment.

Upvotes: 0

RazerM
RazerM

Reputation: 5482

Assuming the scoped session is created like this:

Session = scoped_session(sessionmaker(bind=engine))

Make sure you aren't doing this

session = Session()
give_to_thread1(session)
give_to_thread2(session)

With a scoped session, you can use it directly, e.g.

Session.query(...)

So your threads should do this:

def runs_in_thread():
    Session.add(...)
    # or
    session = Session()
    session.add(...)

Upvotes: 1

Related Questions