I think there are a couple of problems with this.

1. You are calling scoped_session and sessionmaker every time the
decorated function is called, which is unnecessary. sessionmaker
returns a factory function for creating sessions, so you typically
only have one sessionmaker() call in your application. You could
either make it a module-global, or do it during application
configuration.

2. You are never calling session.close(). This means that the
transaction started by a web request will stay open after the request
ends. Since you are using scoped_session as well, the session is
maintained as a thread-local object, so when the thread handles
another web request, it will use the same session and the same
transaction. Your long-running transactions are probably the reason
why you are getting blocked requests.

You might like to read
http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#session-frequently-asked-questions.

I was going to say that you need to add a "finally" section to your
session_decorator that calls session.close(), but if you ever have one
decorated function calling another decorated function, they are going
to interfere with each other. If you are *absolutely certain* that
will never happen, adding the "finally" section will probably improve
things, but I would recommend that you try to structure your app a
little differently, so that the session creation and teardown happen
at the beginning and end of the web request.

Hope that helps,

Simon


On Wed, Feb 14, 2018 at 2:14 PM,  <eugene.de...@gmail.com> wrote:
> Decorator like this
>
> engine = create_engine(
>
> 'mssql+pymssql://{LOGIN}:{PASSWORD}@{HOST}/{DATABASE}?charset={CHARSET}&timeout={TIMEOUT}'.format(**DATABASE),
>     isolation_level='READ COMMITTED'
> )
>
>
> def decorator_with_args(decorator_to_enhance):
>     """
>     https://habrahabr.ru/post/141501/
>     """
>     def decorator_maker(*args, **kwargs):
>         def decorator_wrapper(func):
>             return decorator_to_enhance(func, *args, **kwargs)
>         return decorator_wrapper
>     return decorator_maker
>
>
> def scope_func():
>     return scoped_session(sessionmaker(bind=engine, autocommit=True))()
>
>
> @decorator_with_args
> def session_decorator(func, default=None, **kwargs):
>     def wrapper(*a, **kw):
>         session = scope_func()
>         if 'session' not in kw:
>             kw['session'] = session
>         try:
>             return func(*a, **kw)
>         except Exception as e:
>             session.rollback()
>
>             print('#####FUNCTION NAME: {}'.format(func.__name__))
>
>             print(e)
>             logging.error(e)
>             return default
>     return wrapper
>
>
>
>
> среда, 14 февраля 2018 г., 17:06:54 UTC+3 пользователь Simon King написал:
>>
>> The pattern you should be aiming for is one in which a fresh
>> transaction is started for every web request that touches the
>> database, and that the transaction is closed at the end of the
>> request. How are you ensuring that at the moment?
>>
>> Simon
>>
>> On Wed, Feb 14, 2018 at 12:51 PM,  <eugene...@gmail.com> wrote:
>> > If I run tests where all functions run one-by-one - all tests passed.
>> > But when i run web app and functions can call almost in parallel then i
>> > have
>> > a problem, then there are problems - transactions can block each other.
>> > I tried to set the isolation level of SNAPSHOT and READ COMMITTED, but
>> > it
>> > did not help.
>> >
>> > среда, 14 февраля 2018 г., 14:58:37 UTC+3 пользователь
>> > eugene...@gmail.com
>> > написал:
>> >>
>> >> Hello, Mike!
>> >> In my web app i have many selects like
>> >> session.execute(select([table1]).where(condition))
>> >> and not so much updates, inserts and deletes like
>> >> session.execute(update(table1).where(condition).values(**values))
>> >> session.execute(insert(table1).values(**values))
>> >> session.execute(delete(table1).where(condition))
>> >>
>> >> What better way to create session for web application?
>> >> Without additional components like flask-sqlalchemy.
>> >>
>> >> суббота, 27 января 2018 г., 20:23:05 UTC+3 пользователь Mike Bayer
>> >> написал:
>> >>>
>> >>> On Sat, Jan 27, 2018 at 5:49 AM, Евгений Рымарев
>> >>> <rymarev...@gmail.com> wrote:
>> >>> > I receive this error:
>> >>> > This result object does not return rows. It has been closed
>> >>> > automatically.
>> >>>
>> >>> there's a lot of weird situations which can cause that error, usually
>> >>> when using a connection that has had some failure condition occur upon
>> >>> it which renders the connection unusable until either a transaction is
>> >>> rolled back or sometimes the connection needs to be discarded.      We
>> >>> can't diagnose it without a full example that reproduces it as well as
>> >>> the complete stack trace.
>> >>>
>> >>>
>> >>> >
>> >>> >
>> >>> >
>> >>> > суббота, 27 января 2018 г., 1:09:53 UTC+3 пользователь Mike Bayer
>> >>> > написал:
>> >>> >>
>> >>> >> On Fri, Jan 26, 2018 at 4:21 PM, Евгений Рымарев
>> >>> >> <rymarev...@gmail.com> wrote:
>> >>> >> > Hello, everyone!
>> >>> >> > Engine:
>> >>> >> > engine = create_engine(
>> >>> >> >
>> >>> >> >
>> >>> >> >
>> >>> >> >
>> >>> >> > 'mssql+pymssql://{LOGIN}:{PASSWORD}@{HOST}/{DATABASE}?charset={CHARSET}'.format(**DATABASE),
>> >>> >> >     isolation_level='READ COMMITTED'
>> >>> >> > )
>> >>> >> >
>> >>> >> >
>> >>> >> > My first decorator for session:
>> >>> >> > @decorator_with_args
>> >>> >> > def session_decorator(func, default=None):
>> >>> >> >    def wrapper(*a, **kw):
>> >>> >> >        s = Session(engine)
>> >>> >> >        kw['session'] = s
>> >>> >> >        try:
>> >>> >> >            return func(*a, **kw)
>> >>> >> >        except Exception as e:
>> >>> >> >            func(*a, **kw)
>> >>> >> >            s.rollback()
>> >>> >> >            s.close()
>> >>> >> >            logging.error(e)
>> >>> >> >            return default
>> >>> >> >        finally:
>> >>> >> >            s.commit()
>> >>> >> >            s.close()
>> >>> >> >    return wrapper
>> >>> >> >
>> >>> >> > My second decorator for session:
>> >>> >> > session = scoped_session(sessionmaker(bind=engine))
>> >>> >> >
>> >>> >> >
>> >>> >> > @decorator_with_args
>> >>> >> > def session_decorator(func, default=None):
>> >>> >> >    def wrapper(*a, **kw):
>> >>> >> >        kw['session'] = session
>> >>> >> >        try:
>> >>> >> >            return func(*a, **kw)
>> >>> >> >        except Exception as e:
>> >>> >> >            session.remove()
>> >>> >> >            logging.error(e)
>> >>> >> >            return default
>> >>> >> >        finally:
>> >>> >> >            session.remove()
>> >>> >> >    return wrapper
>> >>> >> >
>> >>> >> > In both attempts, I came to the conclusion that sessions can
>> >>> >> > block
>> >>> >> > other
>> >>> >> > sessions.
>> >>> >> > How correctly to create a session once, so that it can be used
>> >>> >> > throughout
>> >>> >> > the web application and no locks were created?
>> >>> >>
>> >>> >> The Session doesn't create locks, transactions and your database
>> >>> >> does.
>> >>> >>
>> >>> >> What is the nature of these "blocks", are they deadlocks between
>> >>> >> tables or rows?  What operations are prevented from proceeding?
>> >>> >> Are
>> >>> >> the locks timing out?   SQL Server has graphical consoles that can
>> >>> >> show you this.
>> >>> >>
>> >>> >> What version of SQL Server is this?   Do you have
>> >>> >> ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT turned on?
>> >>> >>
>> >>> >>
>> >>> >> >
>> >>> >> > --
>> >>> >> > 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+...@googlegroups.com.
>> >>> >> > To post to this group, send email to sqlal...@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+...@googlegroups.com.
>> >>> > To post to this group, send email to sqlal...@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+...@googlegroups.com.
>> > To post to this group, send email to sqlal...@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 - 
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.

Reply via email to