Data Isolation and Transaction Isolation
-
Hey guys, QUESTION 1: Is this correct? I. Transaction Isolation Level Serializable = Pessimistic Locking For example: trx = conn.BeginTransaction(IsolationLevel.Serializable) ie. When two threads try to access the same record..
1. Thread 1 begins tran and select [record 123] (I presume a X-lock *Exclusive lock* is acquired) 2. Thread 2 begins tran and *attemp* to select [record 123] (but must wait before select comes thru because Transaction Isolation is set to Serializable -- ie. pessimistic -- I presume Thread 2 is also trying to acquire X-lock at this time but need to wait) 3. Thread 1 commit updates on [record 123] 4. Thread 2 select on [record 123] comes thru 5. Thread 2 update 6. Thread 2 commits updateII. Transaction Isolation Level = ??? For optimistic locking?1. Thread 1 begins tran and select [record 123] (I presume a S-lock *shared lock* is acquired) 2. Thread 2 begins tran and select [record 123] and it comes thru (Thread 2 also acquire a S-lock at this point) 3. Thread 1 commit updates on [record 123 \ file updated: firstname] (Thread 1 acquired a U-lock *update lock* at this point) 4. Thread 2 commit updates on [record 123 \ file updated: lastname] (CAUTION: firstname commited by Thread 1 overwritten by Thread 2 at this time) (Thread 2 acquired a U-lock *update lock* at this point)Is this correct? QUESTION 2: With cursor you get to specified locking, for example:Dim objRs As ADODB.Recordset Set objRs = New ADODB.Recordset Or Dim Objrs As New Adodb.Recordset objRs.Open "users", objConn, adOpenKeyset, adLockOptimistic, adCmdTableBut how with ... "System.Data.IDbConnection"? Thanks in advance. -- modified at 23:37 Thursday 1st September, 2005 -
Hey guys, QUESTION 1: Is this correct? I. Transaction Isolation Level Serializable = Pessimistic Locking For example: trx = conn.BeginTransaction(IsolationLevel.Serializable) ie. When two threads try to access the same record..
1. Thread 1 begins tran and select [record 123] (I presume a X-lock *Exclusive lock* is acquired) 2. Thread 2 begins tran and *attemp* to select [record 123] (but must wait before select comes thru because Transaction Isolation is set to Serializable -- ie. pessimistic -- I presume Thread 2 is also trying to acquire X-lock at this time but need to wait) 3. Thread 1 commit updates on [record 123] 4. Thread 2 select on [record 123] comes thru 5. Thread 2 update 6. Thread 2 commits updateII. Transaction Isolation Level = ??? For optimistic locking?1. Thread 1 begins tran and select [record 123] (I presume a S-lock *shared lock* is acquired) 2. Thread 2 begins tran and select [record 123] and it comes thru (Thread 2 also acquire a S-lock at this point) 3. Thread 1 commit updates on [record 123 \ file updated: firstname] (Thread 1 acquired a U-lock *update lock* at this point) 4. Thread 2 commit updates on [record 123 \ file updated: lastname] (CAUTION: firstname commited by Thread 1 overwritten by Thread 2 at this time) (Thread 2 acquired a U-lock *update lock* at this point)Is this correct? QUESTION 2: With cursor you get to specified locking, for example:Dim objRs As ADODB.Recordset Set objRs = New ADODB.Recordset Or Dim Objrs As New Adodb.Recordset objRs.Open "users", objConn, adOpenKeyset, adLockOptimistic, adCmdTableBut how with ... "System.Data.IDbConnection"? Thanks in advance. -- modified at 23:37 Thursday 1st September, 2005 -
What DBMS you have ? Level of isolation may differ from theory to a specific DBMS or from a DBMS to another DBMS ! --- object oriented uml oriented iconix oriented sql oriented truespace oriented --- solitare oriented :-)
-
- ok 2) Try use SQL Server SET command like this:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLEfor example --- object oriented uml oriented iconix oriented sql oriented truespace oriented --- solitare oriented :-)
No, there seems to be distinction between "Data Isolation (Serializable/Repeatable Read/Read Committed/Dirty)" and "Optimistic (or Pessimistic) Locking"... In my post I was asking: "Serializable" corresponds to "Pessimistic locking" --> Now, for "Optimistic locking", which level of data isolation does it correspond to? Repeatable Read? Norman Fung
- ok 2) Try use SQL Server SET command like this: