prevent read from 2. process (concurrency)

  • Thread starter Thread starter flemming eriksen
  • Start date Start date
F

flemming eriksen

I have made testprograms, consulted articles, but don't get it
right(SQL2000, C#, ADO.NET)

The following simple problem:

Conn.BeginTransaction()
select * from table1 where key = '1'
//
// record is treated - and now should NOBODY be able to update the
Reacord
//
UPDATE table1 SET field1='bla' where key='1'
Transaction.Commit();


But when I go to the debugger after the record is read in, and fire an
QA-Update on the record, it's updated.

conn==new SqlConnection(" connect string here");
SqlCommad cmd=new SqlCommand("select * from table1 where key = @key",conn);
conn.Open();
Trans = conn.BeginTransaction();
cmd.Transaction=Trans;
cmd.Parameters.Add("@key",SqlDbType.Int);
cmd.Parameters["key"].Value = 1; // the parameter
myReader=cmd.ExecuteReader();
myReader.Read()
// here I set the breakpoint and fire the UpdateCommand in QA, which to my
disunderstanding updated the record in table1.
/
What am I doing wrong?

Regards,
Flemming
 
Hi,

Try setting trans.IsolationLevel to IsolationLevel.RepeatableRead or
IsolationLevel.Serializable.
Your IsolationLevel is probably set to ReadCommited.
 
I have tried that without sucess.

I thought when doing the first SELECT, it would put a lock on the record,
but this doesn't seem to be the semantics.
But IF I include a HOLDLOCK keyword in the SELECT line, and stop the program
in the debugger before .Commit, then is the second updating process (here
QA) delayed until I let the debugger continue. (=wanted semantics)

I don't like the HOLDLOCK (not usable in Oracle) , so I tried with setting
the IsolationLevel, but didn't succeed here.

What is wrong in the code/arguments?

flemming

Miha Markic said:
Hi,

Try setting trans.IsolationLevel to IsolationLevel.RepeatableRead or
IsolationLevel.Serializable.
Your IsolationLevel is probably set to ReadCommited.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

flemming eriksen said:
I have made testprograms, consulted articles, but don't get it
right(SQL2000, C#, ADO.NET)

The following simple problem:

Conn.BeginTransaction()
select * from table1 where key = '1'
//
// record is treated - and now should NOBODY be able to update the
Reacord
//
UPDATE table1 SET field1='bla' where key='1'
Transaction.Commit();


But when I go to the debugger after the record is read in, and fire an
QA-Update on the record, it's updated.

conn==new SqlConnection(" connect string here");
SqlCommad cmd=new SqlCommand("select * from table1 where key = @key",conn);
conn.Open();
Trans = conn.BeginTransaction();
cmd.Transaction=Trans;
cmd.Parameters.Add("@key",SqlDbType.Int);
cmd.Parameters["key"].Value = 1; // the parameter
myReader=cmd.ExecuteReader();
myReader.Read()
// here I set the breakpoint and fire the UpdateCommand in QA, which to my
disunderstanding updated the record in table1.
/
What am I doing wrong?

Regards,
Flemming
Click to expand...
Click to expand...
 
Miha,

thanks, with the IsolationLevel.RepeatableRead in BeginTransaction it worked
now as desired.
flemming
flemming eriksen said:
I have tried that without sucess.

I thought when doing the first SELECT, it would put a lock on the record,
but this doesn't seem to be the semantics.
But IF I include a HOLDLOCK keyword in the SELECT line, and stop the program
in the debugger before .Commit, then is the second updating process (here
QA) delayed until I let the debugger continue. (=wanted semantics)

I don't like the HOLDLOCK (not usable in Oracle) , so I tried with setting
the IsolationLevel, but didn't succeed here.

What is wrong in the code/arguments?

flemming

Miha Markic said:
Hi,

Try setting trans.IsolationLevel to IsolationLevel.RepeatableRead or
IsolationLevel.Serializable.
Your IsolationLevel is probably set to ReadCommited.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

flemming eriksen said:
I have made testprograms, consulted articles, but don't get it
right(SQL2000, C#, ADO.NET)

The following simple problem:

Conn.BeginTransaction()
select * from table1 where key = '1'
//
// record is treated - and now should NOBODY be able to update the
Reacord
//
UPDATE table1 SET field1='bla' where key='1'
Transaction.Commit();


But when I go to the debugger after the record is read in, and fire an
QA-Update on the record, it's updated.

conn==new SqlConnection(" connect string here");
SqlCommad cmd=new SqlCommand("select * from table1 where key = @key",conn);
conn.Open();
Trans = conn.BeginTransaction();
cmd.Transaction=Trans;
cmd.Parameters.Add("@key",SqlDbType.Int);
cmd.Parameters["key"].Value = 1; // the parameter
myReader=cmd.ExecuteReader();
myReader.Read()
// here I set the breakpoint and fire the UpdateCommand in QA, which
Click to expand...
Click to expand...
to
my
Click to expand...
Click to expand...
 
Back
Top