c# - SqlDataReader.Read and large records -

i weird behaviour when using sqldatareader.read() when result set contains large column.

what happens is:

• the entire record not read
• the record remains locked after reader , connection closed.

unless explicitly call reader.get* on large column.

here test code show mean.

create table testxml (     id int not null    ,number int not null    ,data xml not null );  insert testxml values (1, 0, (select top 100 * sys.objects xml path('objects')))       ,(2, 0, (select top 1000 * sys.objects xml path('objects')))       ,(3, 0, (select top 10000 a.* sys.objects a, sys.objects b xml path('objects'))) 

the code fetch data

const int testid = 1;  using (var connection1 = new sqlconnection(connectionstring)) using (var connection2 = new sqlconnection(connectionstring)) using (var command1 = new sqlcommand()) using (var command2 = new sqlcommand()) {     command1.connection = connection1;     command1.commandtext =         "select id, number, data " +         "from testxml " +         "where id = @id";      command2.connection = connection2;     command2.commandtext =          "update testxml " +          "set number = number + 1 " +         "where id = @id";      connection1.open();     connection2.open();      command1.parameters.add("@id", sqldbtype.int).value = testid;     using (var reader = command1.executereader()) {         if (reader.read()) {             var id = reader.getint32(0);              //// reader.getvalue(2);             command2.parameters.add("@id", sqldbtype.int).value = id;             command2.executenonquery();         }     } } 

when id = 1 or 2 => update works fine.
when id = 3 => timeout exception.

if uncomment reader.getvalue(2), id = 3 works fine well.

is expected behaviour of reader?
shouldn't closing reader cancel query , release locks?

i know can use commandbehavior.sequentialaccess stream result in case large object not planned.

edit: show don't need update have lock remain after reader closed. exception close reader keep lock on record, e.g.

using (var connection1 = new sqlconnection(connectionstring)) using (var command1 = new sqlcommand())     command1.connection = connection1;     command1.commandtext =         "select id, number, data " +         "from testxml " +         "where id = 3";      connection1.open();      using (var reader = command1.executereader()) {         if (reader.read()) {             var id = reader.getint32(0);             throw new exception("whatever");         }     } } 

(row 3 still locked)