Devart Forums

Deefken

Hi,

we have some odd behavior when using optimistic concurrency in combination with datetime properties. Saving the context for the second time throws an OptimisticConcurrencyException.

Case:
(1) We have an object context with an entity type 'MASTER' and entity set 'MASTERSet'
(2) This MASTER entity has several properties, one of these properties is of a DateTime type. The property is mapped to an Oracle column of DATE type.
(3) All properties of the MASTER entity have been configured with a 'Fixed' concurrency mode.
(4) When we first set the the DateTime property and save the context everything goes wel. When we change some other property of the entity, the save fails with an OptimisticConcurrencyException. When we do not set the DateTime property the first time, the 2nd save doesn't fail.

Can anyone help us out with this strange problem?

This is the (VB.NET) code that demonstrates the problem:

Code: Select all

        
Using ctx As New SomeContext()
  Try
    Dim master As MASTER = (From m In ctx.MASTERSet Select m).First
    master.SOMETEXTFIELD= "Test"
    master.SOMEDATEFIELD= DateTime.Now
    ctx.SaveChanges()
    master.SOMETEXTFIELD= "TestUpdate"
    ctx.SaveChanges() ' This save fails
  Catch ex As Exception
     System.Console.Out.WriteLine(ex.Message)
  End Try
End Using

Thanks!
David

Deefken

Thank you for the information...

However, it is not completely clear to me if using a Transactionscope first creates a local transaction that is only promoted to a distributed transaction if needed?

For instance, using a Transactionscope in the context of a SQL server 2005 will first create a local (lightweight) transaction and will only create a distributed transaction if a second connection is opened to another server. As such, I would assume that in the context of SQL Server

Code: Select all

Using txScope As New TransactionScope() 
  Using conn As New OracleConnection("Data Source=SOMESERVER;...") 
     conn.Open() 
     conn.Close() 
   End Using 
   ...
   entityContext.SaveChanges() '  where entityContext connects to the same SOMSERVER
End Using

will only use a lightweight transaction since two connections are opened to the same server. Is this also the case when using the Devart provider for Oracle?

Deefken

Hi,

I have a question about the use of System.Transactions.TransactionScope:
does it create a distributed transaction or a lightweight transaction? What happens if multiple connections are opened (in the scope) to the same Oracle server?

For instance:

Code: Select all

Using txScope As New TransactionScope()
  Using conn As New OracleConnection("Data Source=SOMESERVER;...")
   conn.Open()
   Using conn2 As New OracleConnection("Data Source=SOMESERVER;...")
     conn.Open()
     conn.Close()
   End Using
  conn.Close()
  End Using
End Using

I would assume that the transaction remains local (lightweight)?

Thanks in advance for your answer,
Deefken

Deefken

Hi,

Our purpose is to develop a console application that moves (migrates) data from an old Oracle schema to a new Oracle schema. To this end, we made an OldObjectContext with all tables that need to be moved and a NewObjectContext with all tables that have to be filled. Eventually, NewObjectContext.SaveChanges() is called to insert all data in the new schema.

The problem we face with that approach is that every record is inserted by means of one INSERT statement (this can be seen by using the OracleMonitor). Since the NewObjectContext contains +/- 180.000 records, this operation is very slow.

To fix that problem, we tried to build a ‘generic’ solution that reads out the ObjectContext and that loads all objects by means of an OracleLoader. This solution works for tables that do not have foreign keys to other tables. However, rows that belong to tables with foreign keys cannot be inserted since System.Data.Objects.ObjectStateEntry.CurrentValues contains NULL values for columns that participate in the foreign key. Is there a way to solve this problem?

We realize that this approach is not ideal in any way, but it boosts the performance of a save operation (eg. 60.000 records are saved in 3 seconds instead of 23 seconds). Can you suggest any alternatives for performing such batch inserts that work with foreign key columns or – even better – do you plan to build such optimizations into DevArt’s Entity Framework? I think that such an optimization would also benefit other situations, even CRUD applications?

Since it is not possible to attach files tot this post we can send you via email a test case application (Visual Studio 2008 project) that illustrates our problem and the 'generic' solution we made for it.

Thanks in advance for your answer,
David

PS: a problem with OracleLoader is that it has some prerequisites (not all column types are supported, transactions cannot be used, …). An alternative would be to use OracleCommand.ExecuteArray().

PPS: We are using dotConnect for Oracle v5.0.40 in combination with the Entity Framework.

Devart Forums

Search found 4 matches

OptimisticConcurrencyException and DateTime properties

TransactionScope: lightweight or distributed transaction

Entity Framework insert performance