Cannot serialize transaction

[StJohn Kettle]

I am using manual transaction control with the Ingres ODBC data
provider for .NET. I have simulated a bank transfer example and found
that, despite setting the isolation level of the transaction to
serializable, the result of running the transfer concurrently is >not<
serialized.

I intend to retry this example using the SQL server data provider in
an attempt to narrow down the issue. But first is this the way to get
serializable transactions? Here is the heart my transaction.


/// The function Transfer reads the from id, then sleeps 5 seconds
before
/// performing the increment to To and the deduction from From:
public class ToyTx
{
public static void Transfer(string from, string to)
{
OdbcConnection xn = new
OdbcConnection(QFS.Registry.CefishConnectionString());

xn.Open();

OdbcTransaction tx =
xn.BeginTransaction(IsolationLevel.Serializable);

// create commands using this connection and tx
OdbcCommand cmdRead = new OdbcCommand(
"select id from x where name = " + Util.Quote(from), xn, tx);

OdbcCommand cmdUpdateFrom = new OdbcCommand();
cmdUpdateFrom.Connection = xn;
cmdUpdateFrom.Transaction = tx;

OdbcCommand cmdUpdateTo = new OdbcCommand();
cmdUpdateTo.Connection = xn;
cmdUpdateTo.Transaction = tx;

OdbcDataAdapter adapter = new OdbcDataAdapter(cmdRead);

try
{
DataTable tb = new DataTable("X");

adapter.Fill(tb);

System.TimeSpan period = new System.TimeSpan(0, 0, 0, 5);

System.Threading.Thread.Sleep(period);

SqlInt16 id = (short) tb.Rows[0]["id"];

cmdUpdateFrom.CommandText =
"update x set id = id - " + (id).ToString()
+ " where name = " + Util.Quote(from);

cmdUpdateTo.CommandText =
"update x set id = id + " + id.ToString()
+ " where name = " + Util.Quote(to);

cmdUpdateTo.ExecuteNonQuery();
cmdUpdateFrom.ExecuteNonQuery();

tx.Commit();
}
catch
{
tx.Rollback();
}
finally
{
// cleanup ommitted
}
}
}

 

[Angel Saenz-Badillos[MS]]

StJohn,
I am not sure I understand what you expect transaction isolation level
serializable to do based on your code. I can tell you what it does and we
can go from there...

All transactions place locks on the database tables, locking is bad for
performance and scalability but good for consistency. To allow the user to
determine the performance/consistency tradeoff we have transactions with
different Isolation levels. The default isolation level, ReadCommitted is a
fairly efficient locking mechanism that locks as little as possible to
guarantee consistency only for the changes you have made, on the other end
of the spectrum is isolation level Serializable that locks absolutely
everything in your predicate. In your case as soon as you do an update you
are going to lock x where name =<name>, you will not be able to modify,
update or insert rows into x where name=<name> from a different connection
until tx commits or rolls back. Does this make any sense?

Thanks,
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.

I am using manual transaction control with the Ingres ODBC data
provider for .NET. I have simulated a bank transfer example and found
that, despite setting the isolation level of the transaction to
serializable, the result of running the transfer concurrently is >not<
serialized.

I intend to retry this example using the SQL server data provider in
an attempt to narrow down the issue. But first is this the way to get
serializable transactions? Here is the heart my transaction.


/// The function Transfer reads the from id, then sleeps 5 seconds
before
/// performing the increment to To and the deduction from From:
public class ToyTx
{
public static void Transfer(string from, string to)
{
OdbcConnection xn = new
OdbcConnection(QFS.Registry.CefishConnectionString());

xn.Open();

OdbcTransaction tx =
xn.BeginTransaction(IsolationLevel.Serializable);

// create commands using this connection and tx
OdbcCommand cmdRead = new OdbcCommand(
"select id from x where name = " + Util.Quote(from), xn, tx);

OdbcCommand cmdUpdateFrom = new OdbcCommand();
cmdUpdateFrom.Connection = xn;
cmdUpdateFrom.Transaction = tx;

OdbcCommand cmdUpdateTo = new OdbcCommand();
cmdUpdateTo.Connection = xn;
cmdUpdateTo.Transaction = tx;

OdbcDataAdapter adapter = new OdbcDataAdapter(cmdRead);

try
{
DataTable tb = new DataTable("X");

adapter.Fill(tb);

System.TimeSpan period = new System.TimeSpan(0, 0, 0, 5);

System.Threading.Thread.Sleep(period);

SqlInt16 id = (short) tb.Rows[0]["id"];

cmdUpdateFrom.CommandText =
"update x set id = id - " + (id).ToString()
+ " where name = " + Util.Quote(from);

cmdUpdateTo.CommandText =
"update x set id = id + " + id.ToString()
+ " where name = " + Util.Quote(to);

cmdUpdateTo.ExecuteNonQuery();
cmdUpdateFrom.ExecuteNonQuery();

tx.Commit();
}
catch
{
tx.Rollback();
}
finally
{
// cleanup ommitted
}
}
}