I'd recommend keeping the writes to a single thread if you can as that will
be easiest. If you can't do that then you could tie them together using the
transaction module's two phase commit and zope.sqlalchemy. As you want the
same transaction shared across threads you'll want to create your own
transaction manager rather than rely on the default threadlocal one.
Session = scoped_session(sessionmaker(twophase=True))
tm = transaction.TransactionManager()
zope.sqlalchemy.register(Session, transaction_manager=tm)
http://zodb.readthedocs.org/en/latest/transactions.html
https://pypi.python.org/pypi/zope.sqlalchemy
Depending on the isolation level your separate threads may start out with
slightly different views of the database. On Postgres you can synchronize
snapshots to work around this, but each connection still has its own
database transaction which is isolated from the others and won't see
subsequent updates from the other threads.
http://www.postgresql.org/docs/9.4/static/functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION
Laurence
On Tuesday, 17 February 2015 19:58:33 UTC-8, Christian Lang wrote:
>
> Hi,
>
> I have a question regarding multi-threading and transactions in the
> non-ORM case.
>
> Assume, we have a global Connection c (with transaction), on which a
> number of insert/update queries are executed and some insert/update queries
> are executed in threads:
>
> Main thread: Thread 1: Thread 2:
> ---------------- -------------
> -------------
> c.execute(q1)
> c.execute(q2)
> c'.execute(q3)
> c''.execute(q5)
> c'.execute(q4)
> c''.execute(q6)
> c.execute(q7)
> ...
>
> If any of the queries fails, all changes should be rolled back, otherwise
> committed.
>
> Since each thread will need to open its own connection (c' and c'') to the
> DB, how can these "thread-local" transactions be tied to the global
> transaction?
> On failure of q3/q4/q5/q6, the local transaction could be rolled back and
> failure signaled to the main thread. However, on success of q3 and q4 (or
> q5 and q6), the local transaction cannot be committed yet until the global
> transaction commits.
>
> Is there some easy mechanism that SQLAlchemy provides for such
> "multi-thread transactions"?
>
> Thanks in advance,
> Christian
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
