Transaction Question.....

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

I am using SQL Server 2000 and .NetFramework 1.1.

Here is my transaction based database implementation

gtrnSQLDB = gcnnSQLDB.BeginTransaction(IsolationLevel.ReadUncommitted)



Since I am saying ReadUncommited, If any other application tries to access
the data in-between BEGIN and COMMIT. Will it be allowed to read the data?
or will it be in queue so that only when the lock is released by other
application will be read?

Thanks,

Frank
 
Other applications can read the data but not change it. If they try they are
blocked for CommandTimeout seconds after which they get a timeout exception.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Yes, I noticed that other application can read using SELECT statement. But
when the data is read using a STORED PROCEDURE I am getting the error. Is
there any reason for this?
Thanks,
Frank
 
What error are you getting?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I am getting the following error message.

Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding
Frank.
 
I noticed that the data I was reading had uncommitted data at that point of
time. For stored procedure I have added the following statement


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED



It started working fine.



But If I replace this stored procedure with SQL statement in the code it
works fine.



Thanks for your analysis and reply.

Frank
 
Frank said:
I am getting the following error message.

Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding
Click to expand...

This is because of a deadlock.
Imagine this:
SQL Query one in a transaction with updates a given set of rows. With each
update, the connection holding the transaction will hold an exclusive lock on
the row. This means that no other query from another connection can read the
row.

Sql query two in another connection tries to read a couple of rows. One of
them is a row updated with the transaction of query one. This means that
query two will wait because one has the exclusive lock on the row.

Now, this will be ok, if one finishes its job and the lock is released. But
what happens when you have in your C# code:

try
{
StartTransaction();
PerformQueryOne();
PerformQueryTwo();
CommitTransaction();
}
catch(Exception ex)
{
RollbackTransaction();
}

The transaction will be committed once BOTH query will be finished. The
locks added due to the transaction of query one will only be released when
commit or rollback is called. Which means query two will never succeed as it
waits for something which will never take place.

The only remedy to this is that you use the SAME open (!) connection object
for all your SQL calls between the time you start a transaction and when you
commit a transaction.

Frans.
 
Back
Top