How to deal with the sqlalchemy's DB table blocking policy?

Can someone explain, how can I avoid application freezing when I, for example, have a list of entities and an ability to move to detail pages. So, I open the list, and one sqlalchemy session starts, then I open one detail page and another one goes, then another one, and application freeze, because one session blocks another. I cannot use one session for whole application, because I then can't say, that something was edited on form by just checking out session.dirty, new, deleted attributes and application state handling becomes the hell of fragile unreadable code.

Do I need to implement some another kind of 开发者_运维知识库session handling policy? Do I need to tune sqlalchemy mapping or sql server?

Here is the minimal working example:

from sqlalchemy import MetaData, Table, Column, FetchedValue, ForeignKey, create_engine
from sqlalchemy.types import BigInteger, String
from sqlalchemy.orm import mapper, relationship, sessionmaker, Session

class Ref(object):
    id = None
    name = None
    id_parent = None

class TableMapper(object):

    def __init__(self, metadata, mapped_type):
        self._table = None
        self._mapped_type = mapped_type

    def get_table(self):
        return self._table

    def set_table(self, table):
        assert isinstance(table, Table)
        self._table = table

class RefTableMapper(TableMapper):

    def __init__(self, metadata):
        TableMapper.__init__(self, metadata, Ref)
        self.set_table(Table('Ref', metadata,
                             Column('id', BigInteger,
                                    primary_key = True, nullable = False),
                             Column('name', String),
                             Column('id_parent', BigInteger,
                                    ForeignKey('Ref.id'))
                             ))
    def map_table(self):
        r_parent = relationship(Ref,
                            uselist = False,
                            remote_side = [self._table.c.id],
                            primaryjoin = (
                                self._table.c.id_parent == self._table.c.id))
        mapper(Ref, self._table,
               properties = {'parent': r_parent})
        return self._table

class Mapper(object):

    def __init__(self, url, echo = False):
        self._engine = create_engine(url, echo = echo)
        self._metadata = MetaData(self._engine)
        self._Session = sessionmaker(bind = self._engine, autoflush = False)
        ref_t = RefTableMapper(self._metadata).map_table()

    def create_session(self):
        return self._Session()

if __name__ == '__main__':
    mapp = Mapper(r'mssql://username:pwd@Server\SQLEXPRESS/DBName', True)
    s = mapp.create_session()

    rr = s.query(Ref).all()

    s1 = mapp.create_session()
    merged = s1.merge(rr)
    merged.flush()

    s2 = mapp.create_session()
    rr1 = s2.query(Ref).all() #application freezes! 

SQL Server's default isolation mode locks entire tables very aggressively. (The above example seems like perhaps you're emitting an UPDATE and then emitting SELECT in a different transaction while the previous transaction is pending, though session.merge() does not accept a list and the contents of the table aren't specified above so its difficult to say).

Anyway, it's typical practice to enable multi-version concurrency control (SQL server calls it "row versioning") so that it has reasonable ability to lock individual rows against each other instead of full tables:

ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON

Detail on this is available at http://msdn.microsoft.com/en-us/library/ms175095.aspx .