ActiveRecord/Nhibernate locking table

[chitech]

Alo

I have setup our web application environment with ActiveRecord &
NHibernate. See web.config

<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/
v2.0">
<configSections>
<section name="activeRecord"
type="Castle.ActiveRecord.Framework.Config.ActiveRecordSectionHandler,
Castle.ActiveRecord"/>
...
</configSections>
<connectionStrings>
<add name="Jobcenter" connectionString="$
{Jobcenter_connectionString}" providerName="System.Data.SqlClient" />
<add name="JobcenterLog" connectionString="$
{JobcenterLog_connectionString}" providerName="System.Data.SqlClient" /
>
</connectionStrings>
<activeRecord isWeb="true" isDebug="${Debug}">
<config>
<add key="connection.driver class"
value="NHibernate.Driver.SqlClientDriver"/>
<add key="dialect" value="NHibernate.Dialect.MsSql2005Dialect"/>
<add key="connection.provider"
value="NHibernate.Connection.DriverConnectionProvider"/>
<add key="connection.connection_string" value="$
{Jobcenter_connectionString}"/>
<add key="proxyfactory.factory_class"
value="NHibernate.ByteCode.Castle.ProxyFactoryFactory,
NHibernate.ByteCode.Castle"/>
<add key="show_sql" value="true" />
</config>
</activeRecord>
<httpModules>
<add name="ar.sessionscope"
type="Castle.ActiveRecord.Framework.SessionScopeWebModule,
Castle.ActiveRecord" />
...
</httpModules>
...
</configuration>

When I try to debug inside a SessionScope (SessionScopeWebModule)
after a Repository.Save() I can't access the table from SQL Management
Studio. I get a timeout.Should it work this way? or is it only in
debug mode it behavior like this. This ConnectionStrings section in
Web.Config is the legacy ADO.net

[Markus Zywitza]

This is because SessionScope has to open a NH transaction implicitly (I stopped counting how often I cursed NH2.x for making transactions mandatory...). NH opens a DB transaction for it. If your isolation level is repeatable read, SSMS is locked out because SQLServer waits for the transaction to be commited or rolled back. This one happens only when an exception is thrown or the session scope is disposed.

To work around it, set isolation level to read uncommitted.

-Markus

2009/4/28 chitech <khuo...@gmail.com>

[chitech]

Does NH open a TransactionScope inside the SessionScope for all Update
() and Save() implicitly? TransactionScope open a DB transaction and
the TransactionScope/DB transaction is first commited when the
SessionScope is disposed. Using SessionScopeWebModule it will be on
void OnEndRequest

Can the DB transaction be committed before the sessionScope is
disposed? i.e. just after Save() or Update()
We have isolation level set to committed today so with this setup we
can not set it in production environment because of locking of tables.
Is the only workaround to set the isolation level to read uncommited?

On Apr 28, 5:19 pm, Markus Zywitza <markus.zywi...@gmail.com> wrote:
> This is because SessionScope has to open a NH transaction implicitly (I
> stopped counting how often I cursed NH2.x for making transactions
> mandatory...). NH opens a DB transaction for it. If your isolation level is
> repeatable read, SSMS is locked out because SQLServer waits for the
> transaction to be commited or rolled back. This one happens only when an
> exception is thrown or the session scope is disposed.
>
> To work around it, set isolation level to read uncommitted.
>
> -Markus
>

> 2009/4/28 chitech <khuon...@gmail.com>

[Markus Zywitza]

The transaction is opened when the SessionScope is created and committed when it is disposed. If you need to commit or rollback transactions in between, you can put the code into a (AR) TransactionScope.

-Markus

2009/4/28 chitech <khuo...@gmail.com>

[chitech]

Am I right that one solution is to commit the transaction (that is
open when the SessionScope was created) i.e. after a update() or save
(). So the DB transaction would not last too long and the same with
locks the rows if using isolation level "Read committed"

Is that possible? and do you how to to achive that?

On Apr 29, 3:42 pm, Markus Zywitza <markus.zywi...@gmail.com> wrote:
> The transaction is opened when the SessionScope is created and committed
> when it is disposed. If you need to commit or rollback transactions in
> between, you can put the code into a (AR) TransactionScope.
>
> -Markus
>

> 2009/4/28 chitech <khuon...@gmail.com>

[Markus Zywitza]

What do you want to achieve?

If you simply have to ensure that a long running SessionScope does not block the table, you can use a TransactionScope like this:

using (new TransactionScope())
{
  entity.Save();
}
// transaction committed

However, if you are in for debugging an implicit transaction, you are out of luck: AR cannot magically override your DB's isolation level.

It is also not practical to commit the NHibernate transaction on every call to Save(): Neither the called method, the session, the sessionfactory holder or the SessionScope are aware of another scope, namely the abovementioned TransactionScope is currently in use. This would mean committing an ongoing explicit transaction in between and violate ACID.

-Markus

2009/4/29 chitech <khuo...@gmail.com>