linq transactions

[imbirek8]

Hi!
I have some questions about Linq and transactions.
In my opinion, when I do:

DataClassesDataContext objDataClass = new
DataClassesDataContext(connectionString);
User user = new User() { UserName = "name" };
objDataClass.Users.InsertOnSubmit(user);
objDataClass.SubmitChanges();

I don't use any transaction. When I do SubmitChanges, I only sent query to
database.

When I do:

DataClassesDataContext objDataClass = new
DataClassesDataContext(connectionString);
objDataClass.Connection.Open();
System.Data.Common.DbTransaction trans =
objDataClass.Connection.BeginTransaction();
objDataClass.Transaction = trans;
User user = new User() { UserName = "name" };
objDataClass..Users.InsertOnSubmit(user);
objDataClass.SubmitChanges();
trans.Commit();

I use transactions.

Am I right ?

I try to use Profiler, and in both cases I had:
"EventClass TM:Begin Tran Completed BEGIN TRANSACTION, TM. Commit Tran
Commpleted COMMIT TRANSACTION"
Why? Why in the first case?
I would like to use linq without any transactions, how to do it ?

Thank you for help

 

[Marc Gravell]

I try to use Profiler, and in both cases I had:

"EventClass TM:Begin Tran Completed BEGIN TRANSACTION, TM. Commit Tran
Commpleted COMMIT TRANSACTION"
Why? Why in the first case?
I would like to use linq without any transactions, how to do it ?

The DataContext makes the reasonable assumption that you want your
commits to be atomic, so uses a transaction automatically. Out of
interest, why *wouldn't* you want a transaction here? You could try
passing in an existing connection, but I suspect that even then it
will create one... One final thing you could try is using an explicit
ambient transaction with a low isolation level: add a reference to
System.Transactions.dll, and do:

TransactionOptions options = new TransactionOptions();
options.IsolationLevel = IsolationLevel.Chaos; // pick
your poison...
using (TransactionScope tran =
new
TransactionScope(TransactionScopeOption.RequiresNew, options))
{
// your code
}

However, even if it works (which I haven't investigated), I *strongly*
suggest you don't do this... the default is "serializable" for good
reasons...

Marc

 

[Peter Morris]

I may be wrong here, but I always thought that SQL Server executed all
queries within a transaction whether explicit or implicit. I believe this
was the case with Interbase which is where I first learned about it. In
which case all you are doing in the 2nd example is explicitly controlling
when the transaction should start or end.

 

[imbirek8]

[...]

The DataContext makes the reasonable assumption that you want your
commits to be atomic, so uses a transaction automatically. Out of
interest, why *wouldn't* you want a transaction here? You could try
passing in an existing connection, but I suspect that even then it
will create one... One final thing you could try is using an explicit
ambient transaction with a low isolation level: add a reference to
System.Transactions.dll, and do:

TransactionOptions options = new TransactionOptions();
options.IsolationLevel = IsolationLevel.Chaos; // pick
your poison...
using (TransactionScope tran =
new
TransactionScope(TransactionScopeOption.RequiresNew, options))
{
// your code
}

However, even if it works (which I haven't investigated), I *strongly*
suggest you don't do this... the default is "serializable" for good
reasons...

[...]

Hi,

I have some questions:
-can I have many transactions on the same connection in linq ?
-when I have DataClassesDataContext with connection, can I do something like
this:
open and close transactionScope with isolationLevel - Chaos
on the same DataClassesDataContext do operations with isolationLevel -
ReadCommited
open and close another transactionScope with islolationLevel - Chaos
do some logic operations
and open another transactionScope with isolation level - ReadCommited
[all this operations on the same DataClassesDataContext on the same
Connection ? can I ? ]

Thanks for help

 

[Marc Gravell]

Well, probably not on the same connection... it would probably work if
you are using the (default) lazy connection mode...

but first... *why*... what are you trying to do here? Personally, I'd
almost prefer to elevate everything to serializable... it doesn't
matter how fast something is if it gives invalid results... if you
have some specific code that needs to run with specific logic, then
consider used a SPROC for that (with your choice of NOLOCK, UPDLOCK,
etc hints...) - but leave the rest alone...

Perhaps if you can clarify what you are trying to do and why?

Marc

 

[imbirek8]

[...]

Perhaps if you can clarify what you are trying to do and why?

[...]

I'd like to read dictionaries from data base (for example, types of
products. etc) in no blocking way.
Sometimes I'd like to do some single operation on data base and if it is
failed it's still ok, because it was single operation.

I'm afraid that transactions are very slow.

 

[imbirek8]

[...]

I have another question about linq. I have simple code and I would like to
use it in ASP.NET aplication.

DataClassesDataContext objDataClass = new
DataClassesDataContext(connectionString);
var select = from u in dataClassesDataContext.Users select u;
List<User> tmp = new List<User>(select).ToList();
....
Where the connection with data base is closed ?
Is it timeout or what ? Because I don't close the conection by myself.

Thanks for help

 

[Marc Gravell]

I'm afraid that transactions are very slow.

Is that "you've tested it with meaningful timings and it is very
slow", or "I think it is very slow"...?

Transactions add overhead, but are rarely /that/ bad... but again, it
doesn't matter how fast you can get the wrong answer....

But I repeat: you might be able to do what you want via a SPROC or UDF
using the NOLOCK hint; the UDF has the advantage of keeping
composability (so you can do some level of paging/sorting etc at the
database).

Marc

 

[Marc Gravell]

IIRC, when passing in a connection-string, the SqlConnection object
should be opened and closed for the short duration required for the
work. However, you might not see this in trace logs, because by
default SQL Server connections are pooled and re-used.

Marc

 

[imbirek8]

Is that "you've tested it with meaningful timings and it is very
slow", or "I think it is very slow"...?

Transactions add overhead, but are rarely /that/ bad... but again, it
doesn't matter how fast you can get the wrong answer....

But I repeat: you might be able to do what you want via a SPROC or UDF
using the NOLOCK hint; the UDF has the advantage of keeping
composability (so you can do some level of paging/sorting etc at the
database).

[...]

I didn't tested it and maybe it is not problem at all in my case.

But I thought that I can do everything like in ADO.NET. I feel that I should
be able to do it anyway (in linq not in stored procedures).