Transactions and multiple clients

[Manfred]

Hello,
I have the following problem:
There is an application that has many clients that connect directly with the ORM to the same database.
The problem is that if one client starts a transaction and modifies data then the according tables are locked and cannot be read by the other clients until a commit is performed. Microsoft SQL Server does provide the isolation level "Snapshot" but I did not found this or a similar level in Open Access. This level would do exactly what I want namely that the table is locked and all readers get the last version before the transaction started.
I thought also to open the transaction only as short as possible but that would mean that I cannot modify a persistent object directly and I would have to copy it, modify the copy and then applying the modifications to the persistent object; I would like to avoid this copying.
How can I resolve this issue??

Regards
Manfred

[Jan Blessenohl]

Hello Manfred,
The default concurrency control setting for OpenAccess is to work optimistical. This will not lock and table.

We first have to find out why you are getting table locks. Which settings are you changing at the ObjectScope?

Greetings,
Jan Blessenohl
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Manfred]

Hello,
thanks for the reply. I found now the reason for my locks; it is the Flush() method. I have to call it because I am using AUTOINC with interfaces and after the call of this some tables are locked. I did it now this way that I call the flush method before I insert the interface objects and immediately after I call committ to keep the lock as short as possible.

Regards
Manfred

[Jan Blessenohl]

Hello Manfred,
That is the problem with flush, we are starting the server transaction and that usually locks the touched rows and if you use autoinc also the complete table for inserts.

Why do you need the flush? You can use scope.GetObjectId() to get the autinc id value as well, this might lock less resources like flush() but the server side transaction is nevertheless running.

Best wishes,
Jan Blessenohl
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[Manfred]

Hello,
You told me to do so in the this ticket ...
I will try if I could avoid the flush with the GetObjectId method and let You know the result of my tests.

Regards
Manfred

[Manfred]

Hello,
I made now some tests.
I used the GetObjectID method to get the ids before I use the objects for my entity connection which holds two instances of a persistent interface. I found out that these calls lock the table also for reads i.e. if I try to perform a select of all the rows over a different database connection a deadlock occurs.
This is the code I used for the test:

using System;

using System.Data;

using System.Data.SqlClient;

using Telerik.OpenAccess;

namespace VOATest

{

class Program

{

static void Main(string[] args)

{

Person person = new Person();

person.Name = "John";

Car car = new Car();

car.Name = "Ferrari";

IObjectScope scope = ObjectScopeProvider1.GetNewObjectScope();

EntityConnection entityConnection = new EntityConnection();

entityConnection.Parent = person;

entityConnection.Child = car;

scope.Transaction.Begin();

scope.Add(person);

scope.Add(car);

person.ID = int.Parse(scope.GetObjectId(person).ToString());

car.ID = int.Parse(scope.GetObjectId(car).ToString());

//scope.Transaction.Flush();

SqlConnection sqlConnection = new SqlConnection(@"Data Source=localhost\sqlexpress;Initial Catalog=temp;Integrated Security=SSPI;");

sqlConnection.Open();

SqlDataAdapter adapter = new SqlDataAdapter("select * from car", sqlConnection);

DataTable table = new DataTable();

adapter.Fill(table); //here does the deadlock occur

Console.WriteLine("Number of rows in the table: {0}", table.Rows.Count);

sqlConnection.Close();

scope.Add(entityConnection);

scope.Transaction.Commit();

Console.WriteLine("Successfully inserted!");

Console.ReadLine();

}

}

[Telerik.OpenAccess.Persistent()]

public interface IEntity

{

int ID { get; set; }

}

[Telerik.OpenAccess.Persistent(IdentityField = "connectionId")]

public class EntityConnection

{

private int connectionId;

[FieldAlias("connectionId")]

public int ID

{

get { return connectionId; }

set { connectionId = value; }

}

private IEntity parent;

[FieldAlias("parent")]

public IEntity Parent

{

get { return parent; }

set { parent = value; }

}

private IEntity child;

[FieldAlias("child")]

public IEntity Child

{

get { return child; }

set { child = value; }

}

}

[Telerik.OpenAccess.Persistent(IdentityField = "id")]

public class Person : IEntity

{

private int id;

public int ID

{

get { return id; }

set { id = value; }

}

private string name;

public string Name

{

get { return name; }

set { name = value; }

}

}

[Telerik.OpenAccess.Persistent(IdentityField = "id")]

public class Car : IEntity

{

private int id;

public int ID

{

get { return id; }

set { id = value; }

}

private string name;

public string Name

{

get { return name; }

set { name = value; }

}

}

}

Regards
Manfred

[Manfred]

Hello,
we switched now the key generation from AUTOINC to HIGHLOW and that resolved many of our problems.
Thanks for the good support.

Regards
Manfred