I've implemented transactions in my dotNet 2.0 project using
System.Transact ions and TransactionScop e.
---
TransactionOpti ons options = new TransactionOpti ons();
options.Isolati onLevel = System.Transact ions.IsolationL evel.Serializab le;
options.Timeout = new TimeSpan(0, 5, 0);
using (TransactionSco pe transactionScop e = new
TransactionScop e(TransactionSc opeOption.Requi red, options))
{
DoWork();
transactionScop e.Complete(); //Tell the transaction to commit
}
---
When I debug this code and stop debugging inside the DoWork after it has
inserted some rows in some tables but before the transaction has completed,
selection database queries to the tables updated time out, (other users can
still run selects against the database). The selects run fine again when I
run the transaction to completion.
Is this because of my IsolationLevel. Serializable? And how do I avoid this
locking?
Is there some online documentation of isolation levels, that explains them
thoroughly?
Kind Regards,
Allan Ebdrup
8  10941 
Summary is here: http://msdn2.microsoft.com/en-us/lib...tionlevel.aspx
do you mean you are blocking yourself? normally ADO.Net commands
should enlist inside the ambiant transaction (if one), so presumably
one of the following is the problem:
* The connections are not compatible for enlisting - is it the same
user account?
* Are you explicitely creating a null (chaos) transaction scope around
the select?
* Do your select commands / connections perhaps exist before the
transaction scope?
TransactionScop e works best when the connection pool is utilised and
commands are created on-demand, rather than existing for an age
independently (in which case they may struggle to enlist).
Serializable isolation can cause blocking; this is expected (but it
reduces conflicting data movement during transactions, improving
ACIDity); in my experience, "range" locks are the most unanticipated
ones. Serializable isolation can also (when used incorrectly) cause
deadlocks if 2 serializable connections read data then both attempt to
escalate to a write lock; this can be reduces by using (UPDLOCK) when
you read the data to take out an exclusive lock at the start, rather
than escalate to exclusive from read.
Marc
(chaos)
I possibly meant ReadUncommitted ...
This is because the state of the MSDTC on the local machine is inconsistent
with you stopping debugging half way through a transaction.
If you stop and restart the MSDTC on the local machine everything will be
fine.
I wonder if this a bug in VS 2005 and\or System.Transact ion namespace
HTH
Ollie Riches
"Allan Ebdrup" <eb****@noemail .noemailwrote in message
news:Oj******** *****@TK2MSFTNG P05.phx.gbl...
I've implemented transactions in my dotNet 2.0 project using
System.Transact ions and TransactionScop e.
---
TransactionOpti ons options = new TransactionOpti ons();
options.Isolati onLevel = System.Transact ions.IsolationL evel.Serializab le;
options.Timeout = new TimeSpan(0, 5, 0);
using (TransactionSco pe transactionScop e = new
TransactionScop e(TransactionSc opeOption.Requi red, options))
{
DoWork();
transactionScop e.Complete(); //Tell the transaction to commit
}
---
When I debug this code and stop debugging inside the DoWork after it has
inserted some rows in some tables but before the transaction has
completed, selection database queries to the tables updated time out,
(other users can still run selects against the database). The selects run
fine again when I run the transaction to completion.
Is this because of my IsolationLevel. Serializable? And how do I avoid this
locking?
Is there some online documentation of isolation levels, that explains them
thoroughly?
Kind Regards,
Allan Ebdrup
"Ollie Riches" <ol**********@h otmail.comwrote in message
news:u7******** ******@TK2MSFTN GP02.phx.gbl...
This is because the state of the MSDTC on the local machine is
inconsistent with you stopping debugging half way through a transaction.
If you stop and restart the MSDTC on the local machine everything will be
fine.
I wonder if this a bug in VS 2005 and\or System.Transact ion namespace
So it's only a problem when debugging? My concern is that selects are
blocked while the transaction is in progress, will this not happen in
production when I'm not running in debug mode?
Kind Regards,
Allan Ebdrup.
Yes it will only happen when debugging from VS, it won't happen in
production
HTH
Ollie Riches
"Allan Ebdrup" <eb****@noemail .noemailwrote in message
news:%2******** **********@TK2M SFTNGP03.phx.gb l...
"Ollie Riches" <ol**********@h otmail.comwrote in message
news:u7******** ******@TK2MSFTN GP02.phx.gbl...
>This is because the state of the MSDTC on the local machine is
inconsistent with you stopping debugging half way through a transaction.
If you stop and restart the MSDTC on the local machine everything will be
fine.
I wonder if this a bug in VS 2005 and\or System.Transact ion namespace
So it's only a problem when debugging? My concern is that selects are
blocked while the transaction is in progress, will this not happen in
production when I'm not running in debug mode?
Kind Regards,
Allan Ebdrup.
"Marc Gravell" <ma**********@g mail.comwrote in message
news:ev******** ******@TK2MSFTN GP02.phx.gbl...
Summary is here:
http://msdn2.microsoft.com/en-us/lib...tionlevel.aspx
do you mean you are blocking yourself?
I'm running the transaction in debug mode in VS2005 and pausing inside the
transaction, when I connect to the database using the Query Analyzer,
selects form the tables touched by the transaction simply time out and never
complete. Users on other machinces can still connect to the database and run
the queries that time out for me. When I run the transaction to completion
everything is back to normal.
I can't see how a transaction could ever cause selects of tables involved in
the transaction to time out, the transaction should be atomic so I don't see
it's updates until it completes, but I can't see how it should cause selects
not to run while the transaction is in progress.
Kind Regards,
Allan Ebdrup
when doing a selct using the 'with' keyword as in:
Select * from Order with(nolock)
Sql Server can choose to ignore the 'with' keyword.
HTH
Ollie Riches
"Allan Ebdrup" <eb****@noemail .noemailwrote in message
news:e2******** *****@TK2MSFTNG P06.phx.gbl...
"Marc Gravell" <ma**********@g mail.comwrote in message
news:ev******** ******@TK2MSFTN GP02.phx.gbl...
>Summary is here:
http://msdn2.microsoft.com/en-us/lib...tionlevel.aspx
do you mean you are blocking yourself?
I'm running the transaction in debug mode in VS2005 and pausing inside the
transaction, when I connect to the database using the Query Analyzer,
selects form the tables touched by the transaction simply time out and
never complete. Users on other machinces can still connect to the database
and run the queries that time out for me. When I run the transaction to
completion everything is back to normal.
I can't see how a transaction could ever cause selects of tables involved
in the transaction to time out, the transaction should be atomic so I
don't see it's updates until it completes, but I can't see how it should
cause selects not to run while the transaction is in progress.
Kind Regards,
Allan Ebdrup
"Atomic" is only one part of ACID; "Isolated" is also a key one. That
means that another client should not be adversely affected by you...
Scenario: your transaction is open, and you have manipulated data;
somebody else wants to read that data (or a "range" that you impacted,
etc)... three options:
1: wait for your transaction to end, and then read the data in its
final form
2: return your original changes
3: return your as yet uncommitted changes
"NOLOCK" and low isolation levels work using option 3; this has the
unfortunate side-effect that if you rollback your change, they have a
copy of data *that never existed*. Whoops! Serializable works using 1,
which protects you from dirty / non-repeatable reads, but does it by
queuing competing access. Different callers with different isolations
will see different versions of the truth.
There are many scenarios eto cover the permutations of read/write
versus read/write.
Marc
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Heikki Tuuri |
last post by:
Hi!
Many people have complained over years that Borland's dbExpress driver does
not work with MySQL and transactions, because it disconnects from mysqld
after each SQL statement. The postings below suggests that this problem
might now be fixed by Borland.
Best regards,
Heikki Tuuri
|
by: Chris Travers |
last post by:
Hi All;
I may be able to do this in Perl, but if there is enough interest in doing
something like this in C, maybe I can still help (however, consider your
self warned about my skill at coding C).
I am looking at the following design. The clustering daemon either need to
run on separate computer systems or separate interfaces of the same database
servers. The program would basically pretend to be Postmaster and intercept
the...
|
by: JS |
last post by:
Can somebody explain how to get the tablespace out of quiesce mode?
I am executing on each node of my EEE Win2K system:
db2 quiesece tablespace for table schema.tablename RESET
I get the confirmation that the SQL command has executed successfully
on each node.
But, it doesnt seem to do anything because when I try and query the
table, I get "TABLE SPACE ACCESS NOT ALLOWED"
This all resulted from trying to execute a load with the...
|
by: TD |
last post by:
I am trying to add transactions to my code. The original code worked
fine until I followed an example to setup transactions, now the code
does strange things, but no error messages.
Could someone please review my before and after code and tell me the
proper way to add transactions to my code?
Thanks,
TD
|
by: Ace Calhoon |
last post by:
Hello,
I have a VBA/Database application which reads files, analyzes them,
updates a database, and then moves them to an archive. I would like to
make this an atomic transaction -- that is, if the move to the archive
fails, I don't want the database to be updated (and if the database
update fails, I don't want the file moved...)
The logic I want is as follows:
Begin transaction
| | |
by: Mike P |
last post by:
I've been using C# transactions for a while and had no problems with
them. Using try catch blocks I can trap basically all possible errors
and rollback all necessary data.
Over the last few days I've been trying to convert some of this code to
SQL Server stored procedures, but it seems to lack many of the benefits
of C# transactions - a lot of the errors don't seem to be trapped by the
SQL error trapping (e.g. if I do an update on a row...
|
by: Brian Foree |
last post by:
I am developing an ASP.NET application that uses Access 2000 as its backend,
and have just started getting the following error on 2 ASP.NET pages that
had been working until late last week (and I don't think I made any changes
to either page other than changing the user control that creates the
header).
Server Error in '/myApp' Application.
----------------------------------------------------------------------------
----
|
by: Marco Castro |
last post by:
Im trying to create a system service that will read some records within an
access file. From these values I will then be able to see if other software
in the system is up to date and install the new versions if they are not.
The problem im running into is that I can't get a connection to the file.
I keep on getting the error "The Microsoft Jet database engine cannot open
the file 'c:\DatabasesInfo.mdb'. It is already opened exclusively...
|
by: RAM |
last post by:
Hello,
I am learning .NET 2.0 (ASP.NET, ADO.NET). I would like to ask experienced
programmers when to use System.Transactions. One programmer told me that he
never used System.Transactions, he only uses database transaction
(SqlTransaction) and recommended me this method but he did not explained
why. I have read a few pages about both transaction types in MSDN but I have
little experience I don't know what are practical usages of...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| | |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
