Re: [sqlalchemy] Multiple sessions same thread - How to?

[Mike Bayer]

On Mon, Jun 25, 2018 at 6:24 PM, HP3  wrote:
> Thank you both
>
> With `before_cursor_execute` I am able to log each Connection, Cursor and
> context instance.
>
> I see that every worker is doing its thing and the Connection instance is
> always different for each SQL statement
>
> Also, each SQL has its own distinct
> postgresql.psycopg2.PGExecutionContext_psycopg2 instance.
>
> However, I see that the "same" Cursor instance sometimes appears to be used
> by different Workers.
>
> Does that make sense?

if those are two different processes then they aren't, they just
happen to have the same id().


>
>
> [Worker-2]
> [p2server.celery_tasks.generate_version(d3f81359-d969-4bdb-a30b-0f7a3c1c7fc1)]
> p2server.celery_tasks BEFORE STMT  at 0x107e9afd0>  'INSERT INTO
> p2baseversions ... RETURNING p2baseversions.id'
>  at 0x107e9aef0>
>
>
> [Worker-3]
> [p2server.celery_tasks.generate_version(b8b7c166-a8e5-4a57-84c8-8d4990968f66)]
> p2server.celery_tasks BEFORE STMT  at 0x107b2ce48>  'SELECT
> p2baseversions.id ...'
>  at 0x107e4d7f0>
>
>
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Multiple sessions same thread - How to?

[HP3]

Thank you both

With `before_cursor_execute` I am able to log each Connection, Cursor and 
context instance.

I see that every worker is doing its thing and the Connection instance is 
always different for each SQL statement

Also, each SQL has its own distinct 
postgresql.psycopg2.PGExecutionContext_psycopg2 instance.

However, I see that the "same" Cursor instance sometimes appears to be used 
by different Workers.

Does that make sense?


[Worker-2] 
[p2server.celery_tasks.generate_version(d3f81359-d969-4bdb-a30b-0f7a3c1c7fc1)] 
p2server.celery_tasks BEFORE STMT   'INSERT INTO 
p2baseversions ... RETURNING p2baseversions.id' 



[Worker-3] 
[p2server.celery_tasks.generate_version(b8b7c166-a8e5-4a57-84c8-8d4990968f66)] 
p2server.celery_tasks BEFORE STMT   'SELECT 
p2baseversions.id ...' 






-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Multiple sessions same thread - How to?

[Mike Bayer]

you might want to build out events to log exactly what you need and where:

http://docs.sqlalchemy.org/en/latest/core/events.html#sql-execution-and-connection-events

use your own logger, look at the connection / engine / current thread
/ etc. for whatever you need to keep track of.


On Mon, Jun 25, 2018 at 2:44 PM, HP3  wrote:
>> @HP3 just to test this, i would try adding a slightly different connection
>> string or argument to the celery connection. e.g. create a different user,
>> or toss in a config argument that doesn't affect your code. if the error
>> stops, that's most-likely the reason why.
>
>
> I'll try that out and post again.
>
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Multiple sessions same thread - How to?

[HP3]

>
> @HP3 just to test this, i would try adding a slightly different connection 
> string or argument to the celery connection. e.g. create a different user, 
> or toss in a config argument that doesn't affect your code. if the error 
> stops, that's most-likely the reason why.
>

I'll try that out and post again.


 

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Multiple sessions same thread - How to?

[HP3]

Thank you Mike!

That's very useful. I can see which engine & worker is emitting each SQL 
statement (I had to bypass the usage of 'pyramid_celery` so all logging 
settings would "stick").

*Is there a way to identify every engine instance?*

*Every celery worker is creating its own engines-sessions dual pair but 
there are 8 concurrent workers.*

I see some Pool messages but I am unable to get anything useful from them.

Here is the log preceding the TransactionRollbackError:

[2018-06-25 12:51:30,373: WARNING/Worker-2] 
[p2server.celery_tasks.generate_version(dfad02fa-1282-460e-85a2-720436f7f9d8)] 
p2server.celery_tasks CHECKPOINT 
'p2server.celery_tasks.generate_version'['dfad02fa-1282-460e-85a2-720436f7f9d8']
 
UT:3 'started'


[2018-06-25 12:51:30,473: WARNING/Worker-2] celery.redirected 
/Users/henddher/.pyenv/versions/3.6.3/envs/p2server_py36/lib/python3.6/site-packages/psycopg2/__init__.py:144:
 
UserWarning: The psycopg2 wheel package will be renamed from release 2.8; 
in order to keep installing from binary please use "pip install 
psycopg2-binary" instead. For details see: 
.

  """)


... tons of sqlalchemy.mapper logs from same Worker-2 ...


[2018-06-25 12:51:31,200: INFO/Worker-2] 
[p2server.celery_tasks.generate_version(dfad02fa-1282-460e-85a2-720436f7f9d8)] 
sqlalchemy.engine.base.Engine.checkpoint-engine select version()


... rest of boilerplate SQL at transaction start checkpoint-engine ...


[2018-06-25 12:51:31,213: INFO/Worker-2] 
[p2server.celery_tasks.generate_version(dfad02fa-1282-460e-85a2-720436f7f9d8)] 
sqlalchemy.engine.base.Engine.checkpoint-engine BEGIN (implicit)


[2018-06-25 12:51:31,214: INFO/Worker-2] 
[p2server.celery_tasks.generate_version(dfad02fa-1282-460e-85a2-720436f7f9d8)] 
sqlalchemy.engine.base.Engine.checkpoint-engine SELECT usertransactions.id 


[2018-06-25 12:51:31,218: INFO/Worker-2] 
[p2server.celery_tasks.generate_version(dfad02fa-1282-460e-85a2-720436f7f9d8)] 
sqlalchemy.engine.base.Engine.checkpoint-engine UPDATE usertransaction ... 


[2018-06-25 12:51:31,220: INFO/Worker-2] 
[p2server.celery_tasks.generate_version(dfad02fa-1282-460e-85a2-720436f7f9d8)] 
sqlalchemy.engine.base.Engine.checkpoint-engine COMMIT


# domain model stuff after checkpoint 'started' is done and commit


[2018-06-25 12:51:31,232: INFO/Worker-2] 
[p2server.celery_tasks.generate_version(dfad02fa-1282-460e-85a2-720436f7f9d8)] 
sqlalchemy.engine.base.Engine.normal-engine select version()


... rest of boilerplate SQL at transaction start normal-engine (aka DOMAIN 
MODEL): current_schema, SELECT CAST ...


### Meanwhile, Worker-3 is doing its thing with its own checkpoint-engine 
and normal-engine about to trigger Concurrent Update


# Worker-2 starts to log 'failed' checkpoint


[2018-06-25 12:51:35,374: WARNING/Worker-2] 
[p2server.celery_tasks.generate_version(dfad02fa-1282-460e-85a2-720436f7f9d8)] 
p2server.celery_tasks 

CHECKPOINT 
'p2server.celery_tasks.generate_version'['dfad02fa-1282-460e-85a2-720436f7f9d8']
 
UT:3 'failed'


... EXCEPTION STACK TRACE ...

sqlalchemy.exc.OperationalError: (raised as a result of Query-invoked 
autoflush; consider using a session.no_autoflush block if this flush is 
occurring prematurely) (psycopg2.extensions.TransactionRollbackError) could 
not serialize access due to concurrent update


[2018-06-25 12:51:35,413: WARNING/Worker-2] celery.redirected 2018-06-25 
12:51:35,413 INFO sqlalchemy.pool.QueuePool Pool disposed. Pool size: 5  
Connections 
in pool: 0 Current Overflow: -5 Current Checked out connections: 0

[2018-06-25 12:51:35,413: INFO/Worker-2] 
[p2server.celery_tasks.generate_version(dfad02fa-1282-460e-85a2-720436f7f9d8)] 
sqlalchemy.pool.QueuePool Pool disposed. Pool size: 5  Connections in pool: 
0 Current Overflow: -5 Current Checked out connections: 0

[2018-06-25 12:51:35,414: WARNING/Worker-2] celery.redirected 2018-06-25 
12:51:35,414 INFO sqlalchemy.pool.QueuePool Pool recreating

[2018-06-25 12:51:35,414: INFO/Worker-2] 
[p2server.celery_tasks.generate_version(dfad02fa-1282-460e-85a2-720436f7f9d8)] 
sqlalchemy.pool.QueuePool Pool recreating



Notice that in this log, there is no UPDATE, COMMIT, ROLLBACK  for 
Worker-2. Only pool disposed message after the exception stack trace.  This 
makes me wonder if the previous logs showing ROLLBACK and whatnot came from 
other workers.












-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at 

Re: [sqlalchemy] Multiple sessions same thread - How to?

[Jonathan Vanasco]

On Monday, June 25, 2018 at 11:31:07 AM UTC-4, HP3 wrote:
>
> I'm confused about what you said about the underlined connection: I am 
> creating 2 different engines. Why would both share the same connection?
>
>
That wasn't clear from the above, however..

looking at the code you've shared, it seems you're creating two identical 
engines.  @MikeBayer - is it possible that the connection pool is detecting 
this and using the same connections across databases?

@HP3 just to test this, i would try adding a slightly different connection 
string or argument to the celery connection. e.g. create a different user, 
or toss in a config argument that doesn't affect your code. if the error 
stops, that's most-likely the reason why.


 

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Multiple sessions same thread - How to?

[Mike Bayer]

On Mon, Jun 25, 2018 at 11:31 AM, HP3  wrote:
> I'm confused about what you said about the underlined connection: I am
> creating 2 different engines. Why would both share the same connection?


just as a note, if you do have two different engines, you can apply an
individual logging name to each:


http://docs.sqlalchemy.org/en/latest/core/engines.html?highlight=logging_name#sqlalchemy.create_engine.params.logging_name

http://docs.sqlalchemy.org/en/latest/core/engines.html?highlight=logging_name#configuring-logging



>
> FYI -
>
> I just tried without zope.sqlalchemy and got a slightly different result:
>
> My concurrent transactions are no longer failing with
> TransactionRollbackError "concurrent update". I suspect the isolation level
> 'REPEATABLE READ' is not been set.
>
> The intermediate checkpoint 'did something' doesn't get persisted either
> (same as before) but I cannot verify the failure checkpoint as the
> TransactionRollbackError is not happening. Will need to force a failure and
> see.
>
>
>
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Multiple sessions same thread - How to?

[HP3]

>
>
> I verified that the failure checkpoint is not being persisted either when 
I raise an exception after 'do something' checkpoint. 

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Multiple sessions same thread - How to?

[HP3]

I'm confused about what you said about the underlined connection: I am 
creating 2 different engines. Why would both share the same connection?

FYI - 

I just tried without zope.sqlalchemy and got a slightly different result:

My concurrent transactions are no longer failing 
with TransactionRollbackError "concurrent update". I suspect the isolation 
level 'REPEATABLE READ' is not been set. 

The intermediate checkpoint 'did something' doesn't get persisted either 
(same as before) but I cannot verify the failure checkpoint as the 
TransactionRollbackError is not happening. Will need to force a failure and 
see.






-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Multiple sessions same thread - How to?

[HP3]

Thank you Jonathan,

This is how I create both engine/session pairs:

from sqlalchemy import engine_from_config
from sqlalchemy.orm import sessionmaker
import zope.sqlalchemy

class DBTask(app.task)
_engine = None
_domain_model_session = None

def checkpoint(self, label):
*# celery_state_session and engine*
settings = self.app.conf["PYRAMID_REGISTRY"].settings
eng = engine_from_config(settings, prefix='sqlalchemy.')
 # eng.update_execution_options(autocommit=True, autoflush=False)
factory = sessionmaker()
factory.configure(bind=eng)
celery_state_session = factory()
celery_state_session.query(...)
...
celery_state_session.flush()
celery_state_session.commit()

@property
def engine(self):
*# domain_model_session's engine*
if self._engine is None:
settings = self.app.conf["PYRAMID_REGISTRY"].settings
self._engine = engine_from_config(settings, 
prefix='sqlalchemy.')
return self._engine

@property
def domain_model_session(self):
   * # domain_model_session*
if self._domain_model_session is None:
factory = sessionmaker()
factory.configure(bind=self.engine)
self._domain_model_session = factory()
zope.sqlalchemy.register(self._domain_model_session, 
transaction_manager=transaction.manager)
return self._domain_model_session

@app.task(base=DBTask, bind=True)
def celery_task(self, ...):
self.checkpoint('started')
try:
with transaction.manager:
self.domain_model_session.query(...)
self.checkpoint('did something')

except Exception as e:
self.checkpoint('failed')

finally:
self.checkpoint('done')

I will test the `domain_model_session` without zope.sqlalchemy to see if 
that affects the `celery_state_session`. I have been wondering about 
psycopg2 also.

Again, thanks!

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Multiple sessions same thread - How to?

[Jonathan Vanasco]

Can you share/show how/where they engines and connections are created?  
This is odd.

FWIW, with the forking bug- the issue isn't in the transaction/session but 
in the underlying database connections. The SqlAlchemy connection pool 
isn't threadsafe, so all the commits/rollbacks/etc in different 
sessions/transactions made in different sessions end up happening on the 
same connection.


On Friday, June 22, 2018 at 8:18:51 PM UTC-4, HP3 wrote:
>
> No dice! 
>
> I verified that engines and sessions are created after fork. 
>
> By hijacking celery logging, verified each worker had its own transaction 
> and session.
>
> The SQL logs I described above are indeed accurate and belong to the same 
> worker.
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Multiple sessions same thread - How to?

[HP3]

No dice! 

I verified that engines and sessions are created after fork. 

By hijacking celery logging, verified each worker had its own transaction 
and session.

The SQL logs I described above are indeed accurate and belong to the same 
worker.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Multiple sessions same thread - How to?

[Jonathan Vanasco]

On Friday, June 22, 2018 at 1:32:15 PM UTC-4, HP3 wrote:... but I'll 2x 
check!
 

> (I recall that task-inheritance in celery makes certain things happen 
> before and others after the fork - I am using prefork) 
>

i don't use pyramid_celery, but my own pyramid and celery integration...

looking at my code, i use an event decorator to catch the fork and issue a 
dispose

@worker_process_init.connect
def mycelery_atfork(signal=None, sender=None, **named):
getengine().dispose()



i have NO idea if this will work for you.  My code hits the database before 
the fork, so I needed to do this.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Multiple sessions same thread - How to?

[HP3]

>
> Is there a chance there is a query/connection being made between the 
> initialization and worker process?  
>

Hmmm ...

I am using celery task inheritance 
(http://docs.celeryproject.org/en/latest/userguide/tasks.html#task-inheritance) 
with `@property`s for domain model engine and domain model session. I call 
these two properties only from the body of the celery task function.

For the checkpoint, I am doing all within the body of my checkpoint 
function which is only called from the body of the celery task function.

... but I'll 2x check!

(I recall that task-inheritance in celery makes certain things happen 
before and others after the fork - I am using prefork) 


 

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Multiple sessions same thread - How to?

[Jonathan Vanasco]

Is there a chance there is a query/connection being made between the 
initialization and worker process?  If so, that could screw up the 
connection pool.  To address that, you can try adding an `engine.dispose()` 
before celery forks.  

I'll take a look at the code later.  I was home sick this morning and need 
to catch0up first.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Multiple sessions same thread - How to?

[HP3]

I added logging to `session.transaction` and `session.transaction.parent` 
(til parent=None) and `session.transaction.nested` and they all seemed to 
be different, never nested, never subtransaction. No hint there.

I modified my code slightly by adding a new 
`celery_state_session.query(CeleryState)` followed by another 
`celery_state_session.commit()` right after the original 
`celery_state_session.commit()` (Remember there is no 
`celery_state_session.close()` nor `eng.dispose()` after commit).

When the TransactionRollbackError is caught, the SQL log shows the 
following:

*ROLLBACK* * can't tell but I think this is from the 
`domain_model_session` due to TransactionRollbackError*
select version()
{}
select current_schema()
{}
SELECT CAST(...)
{}
SELECT CAST(...)
{}
show standard_conforming_strings
{}
BEGIN (implicit)
SELECT celery_state ...
*< NO UPDATE even though I just changed celery_state*
COMMIT
BEGIN (implicit)
SELECT celery_state ... * SHOWS unchanged values ("of course" there was 
no UPDATE)*
COMMIT

When the domain model transaction succeeds, the SQL looks correct:

*COMMIT* * can't tell but I think this is from the 
`domain_model_session`*
select version()
{}
select current_schema()
{}
SELECT CAST(...)
{}
SELECT CAST(...)
{}
show standard_conforming_strings
{}
BEGIN (implicit)
SELECT celery_state ...
*UPDATE celery_state  Yay!*
COMMIT
BEGIN (implicit)
SELECT celery_state ... * SHOWS NEW values ("of course")*
COMMIT

Perhaps psycorpg2? 

Maybe because I create a new engine and session for each checkpoint? (and 
never dispose it nor close it?)

Seems like the ROLLBACK from the domain_model_session/engine makes the 
changes on the celery_state_session/eng be "skipped"?

Is there any way I can log the issuer (connection, engine, session, thread, 
*process-id/pid*, whatever) of each SQL statement?

The weird thing is that the checkpoint that always works is the first one 
(started). The last one only works when the domain model transaction 
succeeds. Any intermediate one never works.

SELECT * FROM pg_stat_activity shows many, some in COMMIT and some in 
ROLLBACK; all wait_event_type = Client and wait_event = ClientRead


-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Multiple sessions same thread - How to?

[HP3]

Thank you Jonathan!

I checked if my celery_state_session had been mistakenly connected to 
zope.sqlalchemy as I am using pyramid_celery. I didn't find anything.
On the other hand, `domain_model_session` is wired to transaction.manager 
by zope.sqlalchemy (zope.sqlalchemy.registry()).

I even tried creating the engine `eng` and `celery_state_session` step by 
step:

>
> from .models import get_engine 
> from sqlalchemy.orm import sessionmaker 
> settings = self.app.conf["PYRAMID_REGISTRY"].settings 
> eng = get_engine(settings) 
> eng.update_execution_options(autocommit=True, autoflush=False) 
> factory = sessionmaker() 
> factory.configure(bind=eng) 
> celery_state_session = factory() 
>
>
Nothing  - same "incorrect" behavior :(

Maybe the `domain_model_session` being wired to transaction.manager and 
threadlocal is affecting the behavior of my `celery_state_session`?

 
 

>
> On Thursday, June 21, 2018 at 9:34:33 PM UTC-5, Jonathan Vanasco wrote:
>
>
> On Thursday, June 21, 2018 at 10:02:27 PM UTC-4, HP3 wrote:
>>
>> What's the correct way to create a session that is not automatically 
>> bound to any transaction or that can be explicitly bound to an isolated 
>> transaction that can be committed whenever?
>>
>
> That's what `Session()` does by default
>
> Usually arguments to the Engine/Session configuration or framework plugins 
> are used to bind sessions to active transactions (such as 
> zope.sqlalchemy).  You probably have code that is doing that, somewhere.
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Multiple sessions same thread - How to?

[Jonathan Vanasco]

On Thursday, June 21, 2018 at 10:02:27 PM UTC-4, HP3 wrote:
>
> What's the correct way to create a session that is not automatically bound 
> to any transaction or that can be explicitly bound to an isolated 
> transaction that can be committed whenever?
>

That's what `Session()` does by default

Usually arguments to the Engine/Session configuration or framework plugins 
are used to bind sessions to active transactions (such as 
zope.sqlalchemy).  You probably have code that is doing that, somewhere.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Multiple sessions same thread - How to?

[HP3]

Thank you Mike!!!

Sorry about that - my example had errors :(

In reality, I am calling `celery_state_session.flush()` and 
`celery_state_session.commit()` right after I update any of the `cs.` 
values.

@app.task(
base=BaseTask,
bind=True
)
def my_task(self, ...):

# Persist checkpoint 'started'
cs = celery_state_session.query(CeleryTask).get(self.request.id)
cs.started = datetime.now()
celery_state_session.flush()
celery_state_session.commit()

try: 
 
with transaction.manager:
mymodel = domain_model_session.query(MyModel).one()
mymodel.value = "..."

*# Persist checkpoint 'value updated' <<< This checkpoint is 
never persisted *

cs = celery_state_session.query(CeleryTask).get(self.request.id)
cs.model_value_updated = datetime.now()
celery_state_session.flush()
celery_state_session.commit() 

except Exception as e:
 
*# Persist checkpoint 'transaction failed'  <<< This checkpoint 
is never persisted *

cs = celery_state_session.query(CeleryTask).get(self.request.id)
cs.failed_with_exception = datetime.now()
celery_state_session.flush()
celery_state_session.commit()

finally:
 
   # Persist checkpoint 'celery task completed anyhow'  

   cs = celery_state_session.query(CeleryTask).get(self.request.id)
   cs.failed_with_exception = datetime.now()
   celery_state_session.flush()
   celery_state_session.commit()


Couple of clarifications that might be worth mentioning:

The `*# persist checkpoint*` is in reality a function kinda like this:

def checkpoint(self, name):
eng = engine_from_config(self.app.conf['PYRAMID_REGISTRY'].settings)
eng.update_execution_options(autocommit=True, autoflush=False)
factory = sessionmaker()
factory.configure(bind=eng)
celery_state_session = factory()
celery_state = celery_state_session.query(..).get(..)
celery_state.value = ...
celery_state_session.flush()
celery_state_session.commit()
celery_state_session.close()

Notice I create a brand new 'eng` and a brand new `celery_state_session` 
each time I call my checkpoint function. I am not using `scoped_session` 
either. Perhaps that's all incorrect?

I have many celery tasks running at the same time changing the same 
`mymodel` row so concurrent updates are typical.

When all is good the SQL log shows a BEGIN (implicit) followed by UPDATE 
and finally a COMMIT for mymodel and my celery_state. 

But whenever `psycopg2.extensions.TransactionRollbackError` happens, I see 
a COMMIT but no UPDATE for `celery_state` preceding it.
Did the `celery_state_session` got bound to the `transaction.manager` 
automatically? because threadlocals? 

What's the correct way to create a session that is not automatically bound 
to any transaction or that can be explicitly bound to an isolated 
transaction that can be committed whenever?



On Thursday, June 21, 2018 at 7:37:52 PM UTC-5, Mike Bayer wrote:
>
> From looking at your code example, I don't see you calling 
> celery_session.flush() or celery_session.commit(), so nothing is going to 
> persist.  Just setting an attribute value does not send any SQL.
>
> On Thu, Jun 21, 2018, 7:22 PM HP3 > 
> wrote:
>
>> Hello all:
>>
>> Within a celery task, I need to have 2 unrelated sessions that can 
>> commit/rollback independently of each other:
>> One session (`domain_model_session`) performs vanilla domain model 
>> operations and the other (`celery_state_session`) is to persist the state 
>> of the celery task itself **isolated from the domain model one**. 
>>
>> *Imagine "checkpoints"*
>>
>> Something along these lines
>>
>> @task
>> def my task(...):
>>
>>*# Persist checkpoint 'started'*
>>
>> cs = celery_state_session.query(CeleryTask).get(self.request.id)
>> cs.started = datetime.now()
>> celery_state_session.commit()
>>
>> try: 
>>
>>  
>>
>> with transaction.manager:
>> mymodel = domain_model_session.query(MyModel).one()
>> mymodel.value = "..."
>>
>>*# Persist checkpoint 'value updated' *
>>
>> cs = celery_state_session.query(CeleryTask).get(self.request.id)
>> cs.model_value_updated = datetime.now()
>> 
>> except Exception as e:
>>
>>  
>>  * # Persist checkpoint 'transaction failed'  <<< This 
>> checkpoint is never persisted *
>>
>>cs = celery_state_session.query(CeleryTask).get(self.request.id)
>>cs.failed_with_exception = datetime.now()   
>>
>> finally:
>>
>>  
>>   *# Persist checkpoint 'celery task completed anyhow'  *
>>
>>cs = celery_state_session.query(CeleryTask).get(self.request.id)
>>cs.failed_with_exception = datetime.now()
>>
>>
>>
>> I've been trying multiple things but I can't get the behavior I need.
>>
>> No matter what, whenever the transaction associated to 
>> 

Re: [sqlalchemy] Multiple sessions same thread - How to?

[Mike Bayer]

>From looking at your code example, I don't see you calling
celery_session.flush() or celery_session.commit(), so nothing is going to
persist.  Just setting an attribute value does not send any SQL.

On Thu, Jun 21, 2018, 7:22 PM HP3  wrote:

> Hello all:
>
> Within a celery task, I need to have 2 unrelated sessions that can
> commit/rollback independently of each other:
> One session (`domain_model_session`) performs vanilla domain model
> operations and the other (`celery_state_session`) is to persist the state
> of the celery task itself **isolated from the domain model one**.
>
> *Imagine "checkpoints"*
>
> Something along these lines
>
> @task
> def my task(...):
>
>*# Persist checkpoint 'started'*
>
> cs = celery_state_session.query(CeleryTask).get(self.request.id)
> cs.started = datetime.now()
> celery_state_session.commit()
>
> try:
>
>
>
> with transaction.manager:
> mymodel = domain_model_session.query(MyModel).one()
> mymodel.value = "..."
>
>*# Persist checkpoint 'value updated' *
>
> cs = celery_state_session.query(CeleryTask).get(self.request.id)
> cs.model_value_updated = datetime.now()
>
> except Exception as e:
>
>
>  * # Persist checkpoint 'transaction failed'  <<< This
> checkpoint is never persisted *
>
>cs = celery_state_session.query(CeleryTask).get(self.request.id)
>cs.failed_with_exception = datetime.now()
>
> finally:
>
>
>   *# Persist checkpoint 'celery task completed anyhow'  *
>
>cs = celery_state_session.query(CeleryTask).get(self.request.id)
>cs.failed_with_exception = datetime.now()
>
>
>
> I've been trying multiple things but I can't get the behavior I need.
>
> No matter what, whenever the transaction associated to
> `domain_model_session` fails it "rolls back" all operations performed by
> `celery_state_session` as if `celery_state_session` was automatically bound
> to the same transaction.manager.
>
> How am I supposed to create a session that works isolated?
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Multiple sessions same thread - How to?

[HP3]

Hello all:

Within a celery task, I need to have 2 unrelated sessions that can 
commit/rollback independently of each other:
One session (`domain_model_session`) performs vanilla domain model 
operations and the other (`celery_state_session`) is to persist the state 
of the celery task itself **isolated from the domain model one**. 

*Imagine "checkpoints"*

Something along these lines

@task
def my task(...):

   *# Persist checkpoint 'started'*

cs = celery_state_session.query(CeleryTask).get(self.request.id)
cs.started = datetime.now()
celery_state_session.commit()

try: 

 

with transaction.manager:
mymodel = domain_model_session.query(MyModel).one()
mymodel.value = "..."

   *# Persist checkpoint 'value updated' *

cs = celery_state_session.query(CeleryTask).get(self.request.id)
cs.model_value_updated = datetime.now()

except Exception as e:

 
 * # Persist checkpoint 'transaction failed'  <<< This 
checkpoint is never persisted *

   cs = celery_state_session.query(CeleryTask).get(self.request.id)
   cs.failed_with_exception = datetime.now()   

finally:

 
  *# Persist checkpoint 'celery task completed anyhow'  *

   cs = celery_state_session.query(CeleryTask).get(self.request.id)
   cs.failed_with_exception = datetime.now()

   

I've been trying multiple things but I can't get the behavior I need.

No matter what, whenever the transaction associated to 
`domain_model_session` fails it "rolls back" all operations performed by 
`celery_state_session` as if `celery_state_session` was automatically bound 
to the same transaction.manager.

How am I supposed to create a session that works isolated?



-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Multiple sessions

[Srini]

Hi,

I am using tornado, sqlalchemy behind ngnix in reverse proxy mode. I run 4+ 
app servers and each maintaining it's own db session and connection. 

I am seeing the same problem as mentioned in the forum. One session creates 
new data and other session doesn't get updated information. As I am behind 
reverse proxy, each user request could go to different session all 
together. 

https://groups.google.com/forum/#!searchin/sqlalchemy/multiple$20sessions/sqlalchemy/MqeoIcRAdwM/egleUsQ6z-8J

How can I avoid this? I am using InnoDB. 

thanks

-- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] Multiple sessions and data conflicts.

[Jackson, Cameron]

Here is a simplified version of my scenario:
My application has two session objects. One is being used for editing certain 
data, and the other is reading this data, doing some calculations, and 
displaying the results. I've set up a callback kind of system so that whenever 
the first session does a commit, a method is called that does a rollback on the 
second session, and redoes the calculations and display.
I've had bugs crop up where the data I get from querying the second session 
object doesn't match what I know is in the database, but I haven't been able to 
reproduce them consistently yet. So my questions are:

1.   After a session.rollback(), should the data obtained from that 
session's queries match what's in the DB now? Or what was in the DB when the 
session was created? The 
docshttp://www.sqlalchemy.org/docs/orm/session.html#sqlalchemy.orm.session.Session.rollback
 are not clear on this.

2.   If a rollback is not enough to guarantee that the data from querying a 
session is up to date, then what is? Should I just create a new session 
entirely?
cheers,
Cam
Cameron Jackson
Engineering Intern
Air Operations
Thales Australia
Thales Australia Centre, WTC Northbank Wharf, Concourse Level,
Siddeley Street, Melbourne, VIC 3005, Australia
Tel: +61 3 8630 4591
cameron.jack...@thalesgroup.com.aumailto:cameron.jack...@thalesgroup.com.au | 
www.thalesgroup.com.auhttp://www.thalesgroup.com.au


-
DISCLAIMER: This e-mail transmission and any documents, files and 
previous e-mail messages attached to it are private and confidential.  
They may contain proprietary or copyright material or information that 
is subject to legal professional privilege.  They are for the use of 
the intended recipient only.  Any unauthorised viewing, use, disclosure, 
copying, alteration, storage or distribution of, or reliance on, this 
message is strictly prohibited.  No part may be reproduced, adapted or 
transmitted without the written permission of the owner.  If you have 
received this transmission in error, or are not an authorised recipient, 
please immediately notify the sender by return email, delete this 
message and all copies from your e-mail system, and destroy any printed 
copies.  Receipt by anyone other than the intended recipient should not 
be deemed a waiver of any privilege or protection.  Thales Australia 
does not warrant or represent that this e-mail or any documents, files 
and previous e-mail messages attached are error or virus free.  

-

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Multiple sessions and data conflicts.

[Michael Bayer]

On Dec 18, 2011, at 8:25 PM, Jackson, Cameron wrote:

 Here is a simplified version of my scenario:
 My application has two session objects. One is being used for editing certain 
 data, and the other is reading this data, doing some calculations, and 
 displaying the results. I've set up a callback kind of system so that 
 whenever the first session does a commit, a method is called that does a 
 rollback on the second session, and redoes the calculations and display.
 I've had bugs crop up where the data I get from querying the second session 
 object doesn't match what I know is in the database, but I haven't been able 
 to reproduce them consistently yet. So my questions are:
 1.   After a session.rollback(), should the data obtained from that 
 session's queries match what's in the DB now? Or what was in the DB when the 
 session was created? The docs are not clear on this.
 2.   If a rollback is not enough to guarantee that the data from querying 
 a session is up to date, then what is? Should I just create a new session 
 entirely?

These are the comprehensive docs on rollback:

http://www.sqlalchemy.org/docs/orm/session.html#rolling-back


A rollback expires all attributes that are database mapped, on objects that are 
persistent within the session.   Touching any of these attributes subsequent to 
the expire will cause them to emit SQL to get the new value back.

You might not see what you expect if A. you're accessing an object that isn't 
persistent within that session, such as one that was new before the rollback, 
as these get evicted after rollback, or B. you're looking at your own 
attributes which aren't directly database mapped.

As far as what's in the DB, a new transaction starts after rollback, and 
that's against whatever has been committed to that DB.  If your other 
transaction hasn't committed then you wouldn't see its data.




 cheers,
 Cam
 Cameron Jackson
 Engineering Intern
 Air Operations
 Thales Australia
 Thales Australia Centre, WTC Northbank Wharf, Concourse Level,
 Siddeley Street, Melbourne, VIC 3005, Australia
 Tel: +61 3 8630 4591
 cameron.jack...@thalesgroup.com.au | www.thalesgroup.com.au
 - 
 DISCLAIMER: This e-mail transmission and any documents, files and previous 
 e-mail messages attached to it are private and confidential. They may contain 
 proprietary or copyright material or information that is subject to legal 
 professional privilege. They are for the use of the intended recipient only. 
 Any unauthorised viewing, use, disclosure, copying, alteration, storage or 
 distribution of, or reliance on, this message is strictly prohibited. No part 
 may be reproduced, adapted or transmitted without the written permission of 
 the owner. If you have received this transmission in error, or are not an 
 authorised recipient, please immediately notify the sender by return email, 
 delete this message and all copies from your e-mail system, and destroy any 
 printed copies. Receipt by anyone other than the intended recipient should 
 not be deemed a waiver of any privilege or protection. Thales Australia does 
 not warrant or represent that this e-mail or any documents, files and 
 previous e-mail messages attached are error or virus free. 
 -
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalchemy@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

RE: [sqlalchemy] Multiple sessions and data conflicts.

[Jackson, Cameron]

OK, I think I understand. Just to make sure, how about this example:

 from SomeModule import Session, Foo
 session1 = Session()
 session2 = Session()

 data1 = session1.query(Foo).all()
 data2 = session2.query(Foo).all()

 data1[0].bar = 'Baz'
 data1.append(Foo)
 session1.add(data1[-1])
 session1.commit()

 session2.rollback()
 data2 = session2.query(Foo).all()

After all of that, data2[0].bar should equal 'Baz', and data2[-1] should be the 
new Foo that we created and added to data[1], correct? In other words, the 
contents of data1 and data2 should be identical, right?

Cheers,

Cameron Jackson
Engineering Intern
Air Operations
Thales Australia
Thales Australia Centre, WTC Northbank Wharf, Concourse Level,
Siddeley Street, Melbourne, VIC 3005, Australia
Tel: +61 3 8630 4591
cameron.jack...@thalesgroup.com.aumailto:cameron.jack...@thalesgroup.com.au | 
www.thalesgroup.com.auhttp://www.thalesgroup.com.au
From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On 
Behalf Of Michael Bayer
Sent: Monday, 19 December 2011 12:51 PM
To: sqlalchemy@googlegroups.com
Subject: Re: [sqlalchemy] Multiple sessions and data conflicts.


On Dec 18, 2011, at 8:25 PM, Jackson, Cameron wrote:


Here is a simplified version of my scenario:
My application has two session objects. One is being used for editing certain 
data, and the other is reading this data, doing some calculations, and 
displaying the results. I've set up a callback kind of system so that whenever 
the first session does a commit, a method is called that does a rollback on the 
second session, and redoes the calculations and display.
I've had bugs crop up where the data I get from querying the second session 
object doesn't match what I know is in the database, but I haven't been able to 
reproduce them consistently yet. So my questions are:
1.   After a session.rollback(), should the data obtained from that 
session's queries match what's in the DB now? Or what was in the DB when the 
session was created? The 
docshttp://www.sqlalchemy.org/docs/orm/session.html#sqlalchemy.orm.session.Session.rollback
 are not clear on this.
2.   If a rollback is not enough to guarantee that the data from querying a 
session is up to date, then what is? Should I just create a new session 
entirely?

These are the comprehensive docs on rollback:

http://www.sqlalchemy.org/docs/orm/session.html#rolling-back


A rollback expires all attributes that are database mapped, on objects that are 
persistent within the session.   Touching any of these attributes subsequent to 
the expire will cause them to emit SQL to get the new value back.

You might not see what you expect if A. you're accessing an object that isn't 
persistent within that session, such as one that was new before the rollback, 
as these get evicted after rollback, or B. you're looking at your own 
attributes which aren't directly database mapped.

As far as what's in the DB, a new transaction starts after rollback, and 
that's against whatever has been committed to that DB.  If your other 
transaction hasn't committed then you wouldn't see its data.





cheers,
Cam
Cameron Jackson
Engineering Intern
Air Operations
Thales Australia
Thales Australia Centre, WTC Northbank Wharf, Concourse Level,
Siddeley Street, Melbourne, VIC 3005, Australia
Tel: +61 3 8630 4591
cameron.jack...@thalesgroup.com.aumailto:cameron.jack...@thalesgroup.com.au | 
www.thalesgroup.com.auhttp://www.thalesgroup.com.au/
- 
DISCLAIMER: This e-mail transmission and any documents, files and previous 
e-mail messages attached to it are private and confidential. They may contain 
proprietary or copyright material or information that is subject to legal 
professional privilege. They are for the use of the intended recipient only. 
Any unauthorised viewing, use, disclosure, copying, alteration, storage or 
distribution of, or reliance on, this message is strictly prohibited. No part 
may be reproduced, adapted or transmitted without the written permission of the 
owner. If you have received this transmission in error, or are not an 
authorised recipient, please immediately notify the sender by return email, 
delete this message and all copies from your e-mail system, and destroy any 
printed copies. Receipt by anyone other than the intended recipient should not 
be deemed a waiver of any privilege or protection. Thales Australia does not 
warrant or represent that this e-mail or any documents, files and previous 
e-mail messages attached are error or virus free. 
-

--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to 
sqlalchemy@googlegroups.commailto:sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr

[sqlalchemy] Multiple Sessions

[Erich Eder]

I've found a strange behaviour when using multiple sessions: A
committed change to an object in one session does not get reflected in
the other session, even after a session.expire_all() (or expire() or
refresh() on the object. In fact, even an explicit query does not
retrieve the changed data. I'm using MySQL. Further investigation
showed that it happens only with InnoDB. Using MyISAM produces the
expected results. Looks like a caching issue with InnoDB.

Here is some test code:

from sqlalchemy import create_engine, Table, Column, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base=declarative_base()

class A(Base):
__tablename__=a
__table_args__ = {'mysql_engine':'InnoDB'}

id = Column(Integer, primary_key=True)
data = Column(Integer)

engine = create_engine('mysql://localhost/test')
Base.metadata.bind = engine
Base.metadata.drop_all()
Base.metadata.create_all()

Session = sessionmaker(bind=engine)
session1 = Session()
session2 = Session()

a1 = A()
a1.id = 1
a1.data = 123

session1.add(a1)
session1.commit()

print a1.data   # trigger query of a1.data

a2 = session2.query(A).one()

print a1,a2 # to see that a1 and a2 are different objects
print a2.data

a2.data = 999
session2.commit()

print a1.data: , a1.data
print session1.query(A.data): , session1.query(A.data).one()[0]
print session2.query(A.data): , session2.query(A.data).one()[0]

print session1.expire_all()
session1.expire_all()

print a1.data: , a1.data
print session1.query(A.data): , session1.query(A.data).one()[0]
print session2.query(A.data): , session2.query(A.data).one()[0]


The output is:
123
a1.data:  123
session1.query(A.data):  123
session2.query(A.data):  999
session1.expire_all()
a1.data:  123
session1.query(A.data):  123
session2.query(A.data):  999


When commenting out the __table_args__ line in order to use MyISAM,
the output is what one would expect:
123
a1.data:  123
session1.query(A.data):  999
session2.query(A.data):  999
session1.expire_all()
a1.data:  999
session1.query(A.data):  999
session2.query(A.data):  999


Any idea?

Thanks,
Erich

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Multiple Sessions

[Michael Bayer]

On Aug 12, 2010, at 7:36 AM, Erich Eder wrote:

 I've found a strange behaviour when using multiple sessions: A
 committed change to an object in one session does not get reflected in
 the other session, even after a session.expire_all() (or expire() or
 refresh() on the object. In fact, even an explicit query does not
 retrieve the changed data. I'm using MySQL. Further investigation
 showed that it happens only with InnoDB. Using MyISAM produces the
 expected results. Looks like a caching issue with InnoDB.

its not caching, its transaction isolation, which is why expire_all() is not 
the issue here (and why expire_all() is not really needed by itself with 
autocommit=False - rollback() and commit() handle it).   at the end, session1 
is still open in its second transaction which has loaded a1.data as 123.

http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html


 
 Here is some test code:
 
 from sqlalchemy import create_engine, Table, Column, Integer
 from sqlalchemy.orm import sessionmaker
 from sqlalchemy.ext.declarative import declarative_base
 
 Base=declarative_base()
 
 class A(Base):
__tablename__=a
__table_args__ = {'mysql_engine':'InnoDB'}
 
id = Column(Integer, primary_key=True)
data = Column(Integer)
 
 engine = create_engine('mysql://localhost/test')
 Base.metadata.bind = engine
 Base.metadata.drop_all()
 Base.metadata.create_all()
 
 Session = sessionmaker(bind=engine)
 session1 = Session()
 session2 = Session()
 
 a1 = A()
 a1.id = 1
 a1.data = 123
 
 session1.add(a1)
 session1.commit()
 
 print a1.data   # trigger query of a1.data
 
 a2 = session2.query(A).one()
 
 print a1,a2 # to see that a1 and a2 are different objects
 print a2.data
 
 a2.data = 999
 session2.commit()
 
 print a1.data: , a1.data
 print session1.query(A.data): , session1.query(A.data).one()[0]
 print session2.query(A.data): , session2.query(A.data).one()[0]
 
 print session1.expire_all()
 session1.expire_all()
 
 print a1.data: , a1.data
 print session1.query(A.data): , session1.query(A.data).one()[0]
 print session2.query(A.data): , session2.query(A.data).one()[0]
 
 
 The output is:
 123
 a1.data:  123
 session1.query(A.data):  123
 session2.query(A.data):  999
 session1.expire_all()
 a1.data:  123
 session1.query(A.data):  123
 session2.query(A.data):  999
 
 
 When commenting out the __table_args__ line in order to use MyISAM,
 the output is what one would expect:
 123
 a1.data:  123
 session1.query(A.data):  999
 session2.query(A.data):  999
 session1.expire_all()
 a1.data:  999
 session1.query(A.data):  999
 session2.query(A.data):  999
 
 
 Any idea?
 
 Thanks,
 Erich
 
 -- 
 You received this message because you are subscribed to the Google Groups 
 sqlalchemy group.
 To post to this group, send email to sqlalch...@googlegroups.com.
 To unsubscribe from this group, send email to 
 sqlalchemy+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/sqlalchemy?hl=en.
 

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.