Why one thread can't not detect the changed value updated by the other thread?

Question

I am writing a program using SQLAlchemy, python, and multithreading.

In my design, Thread A uses a while True loop. In each loop, it gets the queried object from database by SQLAlchemy, then check a field of the object. If it meets the condition, break the while loop. The field of the record in database will be updated by Thread B.

My Thread-A code:

    engine = create_engine('postgresql://postgres:passw0rd@localhost:5432/mini_amazon')
    Session = sessionmaker(bind=engine, expire_on_commit=False)

    @contextmanager
    def session_scope():
        """
        Provide a transactional scope around a series of operations.
        """
        session = Session()
        try:
            yield session
            session.commit()
        except:
            session.rollback()
            raise
        finally:
            session.close()

    with session_scope() as session:
        while True:
            print('Waiting')
            order = session.query(models.Order).get(arrived_message.packageid)
            time.sleep(1)
            if order.status == 'packed':
                break

        order.status = 'loading'

The result turned out that the record in the database has been updated by Thread-B to the break-condition value of the while loop in Thread-A. However, Thread-A keeps waiting in the while loop and did not break.

Could someone give some insight?

Thanks!

Transactions are isolated. What happens in one (in another thread or not) doesn't affect another (at least until commit). — Michael Butscher, Apr 25 '19 at 00:39
In my case, Thread-B update the database and immediately commit(). If I look into the database, the value is changed — OEurix, Apr 25 '19 at 00:41
But Thread-A holds and uses an open transaction which isn't affected by that. — Michael Butscher, Apr 25 '19 at 00:44

score 1 · Accepted Answer · answered Apr 25 '19 at 00:41

1

This cause by the isolated nature of transactions

A transaction with an average degree of isolation will hold onto the state that it has loaded thus far, and keep giving you that same state local to the transaction even though the real data has changed - this is called repeatable reads in transaction isolation parlance.

How to disable SQLAlchemy caching?

answered Apr 25 '19 at 00:41

QIFENG LI

111
3

My Thread-B commit() after updates the value, is this still the case as you mentioned? – OEurix Apr 25 '19 at 00:45
2

Thread-B’s transactions is complete, update the database. But Thread-A’s transactions not. Thread-A will still read the old value. – QIFENG LI Apr 25 '19 at 00:47

Why one thread can't not detect the changed value updated by the other thread?

1 Answers1