linq transactions

[imbirek8]

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

DataClassesData Context objDataClass = new
DataClassesData Context(connect ionString);
User user = new User() { UserName = "name" };
objDataClass.Us ers.InsertOnSub mit(user);
objDataClass.Su bmitChanges();

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

When I do:

DataClassesData Context objDataClass = new
DataClassesData Context(connect ionString);
objDataClass.Co nnection.Open() ;
System.Data.Com mon.DbTransacti on trans =
objDataClass.Co nnection.BeginT ransaction();
objDataClass.Tr ansaction = trans;
User user = new User() { UserName = "name" };
objDataClass..U sers.InsertOnSu bmit(user);
objDataClass.Su bmitChanges();
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]

Re: linq transactions

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.Transact ions.dll, and do:

TransactionOpti ons options = new TransactionOpti ons();
options.Isolati onLevel = IsolationLevel. Chaos; // pick
your poison...
using (TransactionSco pe tran =
new
TransactionScop e(TransactionSc opeOption.Requi resNew, options))
{
// your code
}

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

Marc

>
Thank you for help

[Peter Morris]

Re: linq transactions

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.




--
Pete
====

http://mrpmorris.blogspot.com

http://www.capableobjects.com

[imbirek8]

Re: linq transactions

"Marc Gravell" <marc.gravell@g mail.comwrote in message
news:54d03c10-8325-4d95-8e9c-8d8f1d863550@t6 5g2000hsf.googl egroups.com...
[...]

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.Transact ions.dll, and do:
>
TransactionOpti ons options = new TransactionOpti ons();
options.Isolati onLevel = IsolationLevel. Chaos; // pick
your poison...
using (TransactionSco pe tran =
new
TransactionScop e(TransactionSc opeOption.Requi resNew, options))
{
// your code
}
>
However, even if it works (which I haven't investigated), I *strongly*
suggest you don't do this... the default is "serializab le" for good
reasons...

[...]

Hi,

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

Thanks for help

[Marc Gravell]

Re: linq transactions

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]

Re: linq transactions

"Marc Gravell" <marc.gravell@g mail.comwrote in message
news:4f2b246d-9faf-4792-bdbb-c806cc013b72@l7 6g2000hse.googl egroups.com...
[...]

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]

Re: linq transactions

"imbirek8" <imbirek8@op.pl wrote in message
news:gdesl9$a82 $1@news.onet.pl ...
[...]

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

DataClassesData Context objDataClass = new
DataClassesData Context(connect ionString);
var select = from u in dataClassesData Context.Users select u;
List<Usertmp = new List<User>(sele ct).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]

Re: linq transactions

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]

Re: linq transactions

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]

Re: linq transactions

"Marc Gravell" <marc.gravell@g mail.comwrote in message
news:28c5ab06-458d-4d74-bec8-70936e63ffc0@k3 0g2000hse.googl egroups.com...

>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).

[...]

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).