These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 posts ] 
Author Message
 Post subject: Pessimistic Lock not works in SQL Server 2000
PostPosted: Mon Sep 10, 2007 9:51 am 
Newbie

Joined: Mon Sep 10, 2007 9:41 am
Posts: 6
Hibernate version:NHiberate1.20GA

Mapping documents:

Code between sessionFactory.openSession() and session.close():
Code:
dim tx as Itransation=session.begintransaction
session.lock(object, lockmode.write)

..........update statement

session.save(object)
tx.commit()




Name and version of the database you are using:MSSQL 2000

I have tried 1.20GA Nhibernate, but I tried hard to get a lock from SQL2000. However, the pessimistic lock does not work and I can still use "begin tran" to update the same data row even the session is begin transaction. Can anyone help me with this? As I refer to the documentation, different approaches and different lockmode I have tried and did not work.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Sep 10, 2007 4:13 pm 
Expert
Expert

Joined: Fri May 13, 2005 11:13 am
Posts: 292
Location: Rochester, NY
Have you tried different isolation levels on your transaction?


Top
 Profile  
 
 Post subject:
PostPosted: Mon Sep 10, 2007 9:18 pm 
Newbie

Joined: Mon Sep 10, 2007 9:41 am
Posts: 6
marcal wrote:
Have you tried different isolation levels on your transaction?


Yes, I have tried different isolation level( readuncommited, readcommited, repeatableread...)
However, the pessimistic lock cannot be obtained.

I have also tried different lockmode (write, upgrade, read...........)

Code:
Dim session As ISession = NHibernateHelper.GetCurrentSession

            Dim dept As Department = session.Load(GetType(Department), 1)


            Dim tx As ITransaction = session.BeginTransaction(Data.IsolationLevel.RepeatableRead)

            Dim lm As LockMode = session.GetCurrentLockMode(dept)


            Debug.Print("Lock Mode=" & lm.ToString)

            session.Lock(dept, LockMode.Read)

            dept.Name = "transaction test app"

            session.Save(dept)


            'session.Save(uclass)

            tx.Commit()
            NHibernateHelper.CloseSession()
[/code]


Top
 Profile  
 
 Post subject:
PostPosted: Tue Sep 11, 2007 12:58 pm 
Contributor
Contributor

Joined: Wed May 11, 2005 4:59 pm
Posts: 1766
Location: Prague, Czech Republic
This should work in 1.2.0.GA. Enable SQL logging and see what SQL is being generated.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Sep 11, 2007 10:29 pm 
Newbie

Joined: Mon Sep 10, 2007 9:41 am
Posts: 6
sergey wrote:
This should work in 1.2.0.GA. Enable SQL logging and see what SQL is being generated.


I have enabled log4net and shows the follow logs for sql
Code:
10:53:20.954 [8] DEBUG NHibernate.Impl.SessionImpl - opened session
10:53:20.954 [8] DEBUG NHibernate.Impl.SessionImpl - loading [Department#1]
10:53:20.954 [8] DEBUG NHibernate.Impl.ConnectionManager - after autocommit
10:53:20.954 [8] DEBUG NHibernate.Impl.ConnectionManager - aggressively releasing database connection
10:53:20.954 [8] DEBUG NHibernate.Impl.SessionImpl - transaction completion
10:53:20.954 [8] DEBUG NHibernate.Transaction.AdoTransaction - begin
10:53:20.954 [8] DEBUG NHibernate.Connection.DriverConnectionProvider - Obtaining IDbConnection from Driver
10:53:20.954 [8] DEBUG NHibernate.Impl.SessionImpl - attempting to resolve [Department#1]
10:53:20.954 [8] DEBUG NHibernate.Impl.SessionImpl - object not resolved in any cache [nhRegistration.Department#1]
10:53:20.954 [8] DEBUG NHibernate.Persister.Entity.AbstractEntityPersister - Fetching entity: [nhRegistration.Department#1]
10:53:20.954 [8] DEBUG NHibernate.Loader.Loader - loading entity: [nhRegistration.Department#1]
10:53:20.954 [8] DEBUG NHibernate.Impl.BatcherImpl - Opened new IDbCommand, open IDbCommands: 1
10:53:20.954 [8] DEBUG NHibernate.Impl.BatcherImpl - Building an IDbCommand object for the SqlString: SELECT department0_.Deptid as Deptid3_0_, department0_.DeptName as DeptName3_0_ FROM Department department0_ WHERE department0_.Deptid=?
10:53:20.954 [8] DEBUG NHibernate.Type.Int32Type - binding '1' to parameter: 0
10:53:20.954 [8] INFO  NHibernate.Loader.Loader - SELECT department0_.Deptid as Deptid3_0_, department0_.DeptName as DeptName3_0_ FROM Department department0_ WHERE department0_.Deptid=@p0
10:53:20.954 [8] DEBUG NHibernate.SQL - SELECT department0_.Deptid as Deptid3_0_, department0_.DeptName as DeptName3_0_ FROM Department department0_ WHERE department0_.Deptid=@p0; @p0 = '1'
10:53:20.969 [8] DEBUG NHibernate.Impl.BatcherImpl - Opened IDataReader, open IDataReaders: 1
10:53:20.969 [8] DEBUG NHibernate.Loader.Loader - processing result set
10:53:20.969 [8] DEBUG NHibernate.Loader.Loader - result set row: 0
10:53:20.969 [8] DEBUG NHibernate.Loader.Loader - result row: 1
10:53:20.969 [8] DEBUG NHibernate.Loader.Loader - Initializing object from DataReader: [nhRegistration.Department#1]
10:53:20.969 [8] DEBUG NHibernate.Loader.Loader - Hydrating entity: nhRegistration.Department#1
10:53:20.969 [8] DEBUG NHibernate.Type.StringType - returning 'cs' as column: DeptName3_0_
10:53:20.969 [8] DEBUG NHibernate.Loader.Loader - done processing result set (1 rows)
10:53:20.969 [8] DEBUG NHibernate.Driver.NHybridDataReader - running NHybridDataReader.Dispose()
10:53:20.969 [8] DEBUG NHibernate.Impl.BatcherImpl - Closed IDataReader, open IDataReaders :0
10:53:20.969 [8] DEBUG NHibernate.Impl.BatcherImpl - Closed IDbCommand, open IDbCommands: 0
10:53:20.969 [8] DEBUG NHibernate.Loader.Loader - total objects hydrated: 1
10:53:20.969 [8] DEBUG NHibernate.Impl.SessionImpl - resolving associations for: [nhRegistration.Department#1]
10:53:20.969 [8] DEBUG NHibernate.Impl.SessionImpl - creating collection wrapper:[nhRegistration.Department.Classes#1]
10:53:20.969 [8] DEBUG NHibernate.Impl.SessionImpl - creating collection wrapper:[nhRegistration.Department.Professors#1]
10:53:20.969 [8] DEBUG NHibernate.Impl.SessionImpl - done materializing entity [nhRegistration.Department#1]
10:53:20.969 [8] DEBUG NHibernate.Impl.SessionImpl - initializing non-lazy collections
10:53:20.969 [8] DEBUG NHibernate.Loader.Loader - done entity load
10:53:20.969 [8] DEBUG NHibernate.Impl.SessionImpl - object already associated with session
10:53:20.969 [8] DEBUG NHibernate.Transaction.AdoTransaction - commit
10:53:20.969 [8] DEBUG NHibernate.Impl.SessionImpl - flushing session
10:53:20.969 [8] DEBUG NHibernate.Engine.Cascades - processing cascades for: nhRegistration.Department
10:53:20.969 [8] DEBUG NHibernate.Engine.Cascades - cascading to collection: nhRegistration.Department.Classes
10:53:20.969 [8] DEBUG NHibernate.Engine.Cascades - cascading to collection: nhRegistration.Department.Professors
10:53:20.969 [8] DEBUG NHibernate.Engine.Cascades - done processing cascades for: nhRegistration.Department
10:53:20.969 [8] DEBUG NHibernate.Impl.SessionImpl - Flushing entities and processing referenced collections
10:53:20.969 [8] DEBUG NHibernate.Persister.Entity.AbstractEntityPersister - nhRegistration.Department.Name is dirty
10:53:20.969 [8] DEBUG NHibernate.Impl.SessionImpl - Updating entity: [nhRegistration.Department#1]
10:53:20.985 [8] DEBUG NHibernate.Impl.AbstractVisitor - Processing collection for role nhRegistration.Department.Classes
10:53:20.985 [8] DEBUG NHibernate.Impl.SessionImpl - Collection found: [nhRegistration.Department.Classes#1], was: [nhRegistration.Department.Classes#1]
10:53:20.985 [8] DEBUG NHibernate.Impl.AbstractVisitor - Processing collection for role nhRegistration.Department.Professors
10:53:20.985 [8] DEBUG NHibernate.Impl.SessionImpl - Collection found: [nhRegistration.Department.Professors#1], was: [nhRegistration.Department.Professors#1]
10:53:20.985 [8] DEBUG NHibernate.Impl.SessionImpl - Processing unreferenced collections
10:53:20.985 [8] DEBUG NHibernate.Impl.SessionImpl - scheduling collection removes/(re)creates/updates
10:53:20.985 [8] DEBUG NHibernate.Impl.SessionImpl - Flushed: 0 insertions, 1 updates, 0 deletions to 1 objects
10:53:20.985 [8] DEBUG NHibernate.Impl.SessionImpl - Flushed: 0 (re)creations, 0 updates, 0 removals to 2 collections
10:53:20.985 [8] DEBUG NHibernate.Impl.Printer - listing entities:
10:53:20.985 [8] DEBUG NHibernate.Impl.Printer - nhRegistration.Department{Classes=uninitialized, Name=transaction test app, Professors=uninitialized, Id=1}
10:53:20.985 [8] DEBUG NHibernate.Impl.SessionImpl - executing flush
10:53:20.985 [8] DEBUG NHibernate.Impl.ConnectionManager - registering flush begin
10:53:21.001 [8] DEBUG NHibernate.Persister.Entity.AbstractEntityPersister - Updating entity: [nhRegistration.Department#1]
10:53:21.001 [8] DEBUG NHibernate.Impl.BatcherImpl - Opened new IDbCommand, open IDbCommands: 1
10:53:21.001 [8] DEBUG NHibernate.Impl.BatcherImpl - Building an IDbCommand object for the SqlString: UPDATE Department SET DeptName = ? WHERE Deptid = ?
10:53:21.001 [8] DEBUG NHibernate.Persister.Entity.AbstractEntityPersister - Dehydrating entity: [nhRegistration.Department#1]
10:53:21.001 [8] DEBUG NHibernate.Type.StringType - binding 'transaction test app' to parameter: 0
10:53:21.001 [8] DEBUG NHibernate.Type.Int32Type - binding '1' to parameter: 1
10:53:21.001 [8] DEBUG NHibernate.SQL - UPDATE Department SET DeptName = @p0 WHERE Deptid = @p1; @p0 = 'transaction test app', @p1 = '1'
10:53:21.001 [8] DEBUG NHibernate.Impl.BatcherImpl - Closed IDbCommand, open IDbCommands: 0
10:53:21.001 [8] DEBUG NHibernate.Impl.ConnectionManager - registering flush end
10:53:21.001 [8] DEBUG NHibernate.Impl.SessionImpl - post flush
10:53:21.001 [8] DEBUG NHibernate.Impl.SessionImpl - before transaction completion
10:53:21.016 [8] DEBUG NHibernate.Impl.ConnectionManager - aggressively releasing database connection
10:53:21.016 [8] DEBUG NHibernate.Connection.ConnectionProvider - Closing connection
10:53:21.016 [8] DEBUG NHibernate.Impl.SessionImpl - transaction completion
10:53:21.016 [8] DEBUG NHibernate.Transaction.AdoTransaction - running AdoTransaction.Dispose()
10:53:21.016 [8] DEBUG NHibernate.Impl.SessionImpl - closing session
10:53:21.016 [8] DEBUG NHibernate.Impl.BatcherImpl - running BatcherImpl.Dispose(true)


Is it true that this log:
NHibernate.Transaction.AdoTransaction - begin

begin the transaction? However, it seems the same for different isolation level and the sql server is not really lock for the record for update
Thanks.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Sep 14, 2007 2:27 am 
Newbie

Joined: Mon Sep 10, 2007 9:41 am
Posts: 6
Code:
  Dim session As ISession = NHibernateHelper.GetCurrentSession


            Dim tx As ITransaction = session.BeginTransaction()

            Dim dept As Department = session.Load(GetType(Department), 1, LockMode.Upgrade)


            dept.Name = "transaction test"
            session.Save(dept)
            Debug.Print("transaction is begin....")

            tx.Commit()


I have solved part of the problem, but something I would like to ask the expertise of Hibernate:

The pessimistic lock can lock the record before I commit ONLY
when I do a update statement in sql analyser
for select statement, the record still appears successfully, is it normal?

I suppose it can lock the record even I execute a select statement for the record. (Like I open 2 sql analyser can do the update in different transaction). The select statement cannot return result when there is a incomplete transaction


Top
 Profile  
 
 Post subject:
PostPosted: Mon Sep 17, 2007 3:53 am 
Newbie

Joined: Mon Sep 10, 2007 9:41 am
Posts: 6
Still seeking advice ^^

The sql analyser can select the uncommited recorded.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Sep 19, 2007 9:36 pm 
Newbie

Joined: Mon Sep 10, 2007 9:41 am
Posts: 6
Yeah, I have figured out the problem in an approach.

using a class for session control:

Code:
Public NotInheritable Class NHibernateSessionManager

    ' <summary>
    ' This is a thread-safe, lazy singleton.  See http://www.yoda.arachsys.com/csharp/singleton.html
    ' for more details about its implementation.
    ' </summary>
    Public Shared _Instance As NHibernateSessionManager

    Public Shared Property Instance() As NHibernateSessionManager
        Get
            Return Nested.nHibernateSessionManager
        End Get
        Set(ByVal value As NHibernateSessionManager)

        End Set
    End Property

    ' <summary>
    ' Initializes the NHibernate session factory upon instantiation.
    ' </summary>
    Private Sub New()
        InitSessionFactory()
    End Sub

    ' <summary>
    ' Assists with ensuring thread-safe, lazy singleton
    ' </summary>
    Private Class Nested
        Shared Sub New()

        End Sub

        Friend Shared ReadOnly nHibernateSessionManager As NHibernateSessionManager = New NHibernateSessionManager()
    End Class

    Private Sub InitSessionFactory()
        'Dim cfg As NHibernate.Cfg.Configuration = New NHibernate.Cfg.Configuration()

        '' The following makes sure the the web.config contains a declaration for the HBM_ASSEMBLY appSetting
        'If ConfigurationManager.AppSettings("HBM_ASSEMBLY") Is Nothing Or ConfigurationManager.AppSettings("HBM_ASSEMBLY") = "" Then


        '    Throw New ConfigurationErrorsException("NHibernateManager.InitSessionFactory: ""HBM_ASSEMBLY"" must be " & _
        '     "provided as an appSetting within your config file. ""HBM_ASSEMBLY"" informs NHibernate which assembly " & _
        '     "contains the HBM files. It is assumed that the HBM files are embedded resources. An example config " & _
        '     "declaration is <add key=""HBM_ASSEMBLY"" value=""MyProject.Core"" />")

        'End If

        'cfg.AddAssembly(System.Configuration.ConfigurationManager.AppSettings("HBM_ASSEMBLY"))

        sessionFactory = New NHibernate.Cfg.Configuration().Configure.BuildSessionFactory


    End Sub

    ' <summary>
    ' Allows you to register an interceptor on a new session.  This may not be called if there is already
    ' an open session attached to the HttpContext.  If you have an interceptor to be used, modify
    ' the HttpModule to call this before calling BeginTransaction().
    ' </summary>
    Public Sub RegisterInterceptor(ByVal interceptor As IInterceptor)
        Dim session As ISession = threadSession

        If (Not session Is Nothing) And session.IsOpen Then
            Throw New CacheException("You cannot register an interceptor once a session has already been opened")
        End If

        GetSession(interceptor)
    End Sub


    Public Function GetSession() As ISession
        Return GetSession(Nothing)
    End Function

    ' <summary>
    ' Gets a session with or without an interceptor.  This method is not called directly instead,
    ' it gets invoked from other public methods.
    ' </summary>
    Private Function GetSession(ByVal interceptor As IInterceptor) As ISession
        Dim session As ISession = threadSession

        If session Is Nothing Then
            If Not interceptor Is Nothing Then
                session = sessionFactory.OpenSession(interceptor)
            Else
                session = sessionFactory.OpenSession()
            End If
        End If

        threadSession = session

        Return session
    End Function

    Public Sub CloseSession()
        Dim session As ISession = threadSession
        threadSession = Nothing

        If (Not session Is Nothing) And session.IsOpen Then
            Console.WriteLine("")
            Console.WriteLine("***** Session Close")
            session.Close()
        End If

    End Sub


    Public Sub BeginTransaction()
        Dim transaction As ITransaction = _threadTransaction

        If transaction Is Nothing Then
            transaction = GetSession().BeginTransaction()
            _threadTransaction = transaction
            Console.WriteLine("")
            Console.WriteLine("***** Transaction Begin")
        End If

    End Sub

    Public Sub CommitTransaction()
        Dim transaction As ITransaction = _threadTransaction

        Try
            If (Not transaction Is Nothing) And (Not transaction.WasCommitted) And (Not transaction.WasRolledBack) Then
                transaction.Commit()
                _threadTransaction = Nothing

                Console.WriteLine("")
                Console.WriteLine("***** Transaction Commit")
            End If

        Catch ex As HibernateException
            RollbackTransaction()
            Throw ex
        End Try

    End Sub


    Public Sub RollbackTransaction()
        Dim transaction As ITransaction = _threadTransaction

        Try

            _threadTransaction = Nothing

            If (Not transaction Is Nothing) And (Not transaction.WasCommitted) And (Not transaction.WasRolledBack) Then

                transaction.Rollback()

            End If

        Catch ex As HibernateException

            Throw ex
        Finally

            CloseSession()
        End Try


    End Sub

    Friend _threadTransaction As ITransaction

    Friend Property threadTranaction() As ITransaction
        Get
            Return CallContext.GetData("THREAD_TRANSACTION")
        End Get
        Set(ByVal value As ITransaction)
            CallContext.SetData("THREAD_TRANSACTION", value)
        End Set
    End Property

    Private _threadSession As ISession

    Public Property threadSession() As ISession
        Get
            Return CallContext.GetData("THREAD_SESSION")
        End Get
        Set(ByVal value As ISession)
            CallContext.SetData("THREAD_SESSION", value)
        End Set
    End Property

    Private sessionFactory As ISessionFactory



End Class


then call load the POCO before begin transaction.
Code:



Dim session As ISession = NhibernateSessionManager.instance.GetSession


            Dim dept As Department = session.Load(GetType(Department), 1, LockMode.Upgrade)

            session.Flush()



            NHibernateSessionManager.Instance.BeginTransaction()

            dept.Name = "transaction test1"
            session.Save(dept)
       
       
                    NhibernateSessionManager.instance.CommitTransaction


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.