Transaction Help

[Daniel Jeffrey]

Can only help me please - this might not be easy to explain but I will try.

I have a class that updates the database for the program.

I want to use a transaction but I am having issues.

I have a function called TableExists (Below) that you will see checks to see
if the table already exists before you try and add it again.


public static bool TableExists(string TableName, SqlConnection conns)
{
string sql = "SELECT COUNT(*) FROM SYS.OBJECTS WHERE NAME = @TABLE_NAME AND
TYPE = 'U' ";
int result;
SqlCommand command = new SqlCommand(sql, conns);
command.Parameters.Add("@TABLE_NAME", SqlDbType.VarChar).Value = TableName;
conns.Open();
result = int.Parse(command.ExecuteScalar().ToString());
conns.Close();
return result != 0;
}

I have 2 connection Objects

Conn and Conn2 - Conn is where the transaction has been created.

Conn2 is used for checking for Tables etc as above.

Command (SqlCommand) is connected to Conn;

Example

if (!DBLibrary.TableExists("BRANDS", Conn2))
{
Sql.Length = 0;
Sql.Append("CREATE TABLE BRANDS (");
Sql.Append("BRANDID CHAR(1) NOT NULL PRIMARY KEY ,");
Sql.Append("BRANDNAME VARCHAR(50) ");
Sql.Append(")");
RunNonQuery(Sql.ToString());
}

public int RunNonQuery(string sql)
{
int Result = 0;
try
{
Command.CommandType = CommandType.Text;
Command.Transaction = MainTrans;
Command.CommandText = sql;
Command.Parameters.Clear();
Result = Command.ExecuteNonQuery();
}
catch (Exception E)
{
ErrMsg = E.Message;
throw E;
}
return Result;
}

My issue is after the first table has been added, any subsequent calls to
TableExists hangs and times out due to transaction lock, however if I try
and use Conn (which has the transaction) as the connection it fails as well
but for other reasons.

I am pulling my hair out here.


Command is setup here

Command = new SqlCommand();
Command.Connection = Conn;
Command.Connection.Open();
MainTrans =
Command.Connection.BeginTransaction(IsolationLevel.ReadCommitted);

I have tried other Iso levels with no success.

Any help would be greatly appreciated.

Daniel

 

[Frans Bouma [C# MVP]]

Can only help me please - this might not be easy to explain but I
will try.

I have a class that updates the database for the program.

I want to use a transaction but I am having issues.

I have a function called TableExists (Below) that you will see checks
to see if the table already exists before you try and add it again.


public static bool TableExists(string TableName, SqlConnection conns)
{
string sql = "SELECT COUNT(*) FROM SYS.OBJECTS WHERE NAME =
@TABLE_NAME AND TYPE = 'U' "; int result;
SqlCommand command = new SqlCommand(sql, conns);
command.Parameters.Add("@TABLE_NAME", SqlDbType.VarChar).Value =
TableName; conns.Open();
result = int.Parse(command.ExecuteScalar().ToString());
conns.Close();
return result != 0;
}

I have 2 connection Objects

Conn and Conn2 - Conn is where the transaction has been created.

Conn2 is used for checking for Tables etc as above.

Command (SqlCommand) is connected to Conn;

Example

if (!DBLibrary.TableExists("BRANDS", Conn2))
{
Sql.Length = 0;
Sql.Append("CREATE TABLE BRANDS (");
Sql.Append("BRANDID CHAR(1) NOT NULL PRIMARY KEY ,");
Sql.Append("BRANDNAME VARCHAR(50) ");
Sql.Append(")");
RunNonQuery(Sql.ToString());
}

public int RunNonQuery(string sql)
{
int Result = 0;
try
{
Command.CommandType = CommandType.Text;
Command.Transaction = MainTrans;
Command.CommandText = sql;
Command.Parameters.Clear();
Result = Command.ExecuteNonQuery();
}
catch (Exception E)
{
ErrMsg = E.Message;
throw E;

don't do this, do:
throw;

because otherwise you're destroying the stacktrace.

}
return Result;
}

My issue is after the first table has been added, any subsequent
calls to TableExists hangs and times out due to transaction lock,
however if I try and use Conn (which has the transaction) as the
connection it fails as well but for other reasons.

I am pulling my hair out here.


Command is setup here

Command = new SqlCommand();
Command.Connection = Conn;
Command.Connection.Open();
MainTrans =
Command.Connection.BeginTransaction(IsolationLevel.ReadCommitted);

I have tried other Iso levels with no success.

Any help would be greatly appreciated.

Daniel

Welcome to the wonderful world of SqlServer! . In SqlServer, readers
block writers and vice versa. So if you write data to a table in a
transaction, the rows affected are locked. If you then in another
connection also want to write to these rows, the writer waits till the
locks are lifted by the previous transaction.

So that's the core reason for this deadlock: you have locks set by the
transaction on conn1, which block conn2's actions.

However you forgot to post the sequence of the code: where is conn1
and conn2 used? It's also a bit messy: your TableExists method opens
and closes the passed in connection. But what if there's a connection
on that connection?

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Daniel Jeffrey]

Thanks for you help.
Its a bit strange as Delphi ADO allows all of this to happen inside the 1
transaction.

It isnt as messy as it seems, I have just cut and pasted out of order and
out of sequence.

Conn and Conn2 are both created in the Class Constructor.
Conn2 is ONLY ever used in Exists Functions - and each of them opens and
closes the connection each time.

Conn is used inside the transaction.

Why do I get errors if I use Conn to do the exists?

This is the part I dont understand, I know about deadlocks.

But why cant I run the code in TableExists from the same connection I used
to create a table?

Dan