Double lock on iseries db2 ado.net transaction

[John Bailo]

I'm seeing some odd locking behavior when using an DB2400 database and
running an ado.net transaction. My code -- in simplified form, appears
at the bottom.

I want to run several INSERT statements using the same iDB2Connection
and iDB2Command. Then I want to Commit() so that either all the data
goes in, or not.

What happens though is this:

1. After the first INSERT, a lock appears on the FILE
2. After the second INSERT, another lock appears on the FILE.
3. I can then add several more records, and no more locks appears
4. After the Commit(), one lock disappears but not the other.

I have run the code with every available IsolationLevel, including the
default, and the result is the same.

My questions are:

1. Where could this second lock come from?
2. Why does Commit() not remove it?
3. What additional properties or commands do I need to account for to
remove this lock?
4. I have read that there may be a journaling property that sets up an
'autocommit/rollback' in the event of a system crash -- could this be
what's causing it?






using System;
using IBM.Data.DB2.iSeries;
using System.Data;
using System.Diagnostics;

namespace simpleTtest
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class Class1
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
//
// TODO: Add code to start application here

string connStr= "Data Source=192.168.NNN.NNN;User
Id=XXXXXX;Password=XXXXX;Default Collection=DataLibrary;";
iDB2Connection p_iconn =
new iDB2Connection(connStr);


iDB2Transaction itrans=null;
iDB2Command icmd=new iDB2Command("INSERT INTO " +
"$MANINV.PEM610WD"+
"(" +
"W1BATCH)" +
"VALUES('100')", p_iconn);


p_iconn.Open();
itrans = p_iconn.BeginTransaction(
IsolationLevel.RepeatableRead
);
icmd.Transaction=itrans;

try
{
icmd.ExecuteNonQuery();
//after this statement a lock is generated

icmd.ExecuteNonQuery();
//after this statement a second lock appears

icmd.ExecuteNonQuery();
//no more locks; there are still two(2) at this point

itrans.Commit();
// after commit, one lock is removed -- one remains! why...?
}
catch(Exception e)
{
Debug.WriteLine(e.ToString());
itrans.Rollback();
}
finally
{
p_iconn.Close();


}


}


}
}

[Dieter Bender]

Hi,

first of all, for using other isolation levels than none, the files have to
be setup with journaling (check with DSPFD).
you should have the difference between file locks and record locks in your
mind; after the first insert a file lock appears and it might be there even
after the commit operation, because db2/400 works with lazy closes. each
insert (or update) takes a record lock and holds this untill the
transaction ends (with commit or rollback).

Dieter Bender

Post by John Bailo
I'm seeing some odd locking behavior when using an DB2400 database and
running an ado.net transaction. My code -- in simplified form, appears
at the bottom.
I want to run several INSERT statements using the same iDB2Connection
and iDB2Command. Then I want to Commit() so that either all the data
goes in, or not.
1. After the first INSERT, a lock appears on the FILE
2. After the second INSERT, another lock appears on the FILE.
3. I can then add several more records, and no more locks appears
4. After the Commit(), one lock disappears but not the other.
I have run the code with every available IsolationLevel, including the
default, and the result is the same.
1. Where could this second lock come from?
2. Why does Commit() not remove it?
3. What additional properties or commands do I need to account for to
remove this lock?
4. I have read that there may be a journaling property that sets up an
'autocommit/rollback' in the event of a system crash -- could this be
what's causing it?
using System;
using IBM.Data.DB2.iSeries;
using System.Data;
using System.Diagnostics;
namespace simpleTtest
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class Class1
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
//
// TODO: Add code to start application here
string connStr= "Data Source=192.168.NNN.NNN;User
Id=XXXXXX;Password=XXXXX;Default Collection=DataLibrary;";
iDB2Connection p_iconn =
new iDB2Connection(connStr);
iDB2Transaction itrans=null;
iDB2Command icmd=new iDB2Command("INSERT INTO " +
"$MANINV.PEM610WD"+
"(" +
"W1BATCH)" +
"VALUES('100')", p_iconn);
p_iconn.Open();
itrans = p_iconn.BeginTransaction(
IsolationLevel.RepeatableRead
);
icmd.Transaction=itrans;
try
{
icmd.ExecuteNonQuery();
//after this statement a lock is generated
icmd.ExecuteNonQuery();
//after this statement a second lock appears
icmd.ExecuteNonQuery();
//no more locks; there are still two(2) at this point
itrans.Commit();
// after commit, one lock is removed -- one remains! why...?
}
catch(Exception e)
{
Debug.WriteLine(e.ToString());
itrans.Rollback();
}
finally
{
p_iconn.Close();
}
}
}
}

[John Bailo]

Post by Dieter Bender
Hi,
first of all, for using other isolation levels than none, the files have to
be setup with journaling (check with DSPFD).
you should have the difference between file locks and record locks in your
mind; after the first insert a file lock appears and it might be there even
after the commit operation, because db2/400 works with lazy closes. each
insert (or update) takes a record lock and holds this untill the
transaction ends (with commit or rollback).

Hi Dieter.

The problem is that the file lock doesn't appear to go away even after a
.Commit() or a connection Close()

Here is what I see after the second .ExecuteNotQuery()


Work with Job Member Locks
System:
AS400
Job: QZDASOINIT User: QUSER Number: 791233



File . . . . . : PEM610WD Type . . . . . : PHY

Library . . : $MANINV ASP device . . : *SYSBAS



Type options, press Enter.

5=Work with job shared member locks 8=Work with member locks



Opt Member Lock Type Lock Status Share Scope
Thread
PEM610WD MBR *SHRRD HELD *JOB

MBR *SHRRD HELD *JOB

DATA *SHRUPD HELD *JOB

DATA *SHRUPD HELD *JOB





Here is what I see after the .Commit() and iDB2Connection .Close()




Opt Member Lock Type Lock Status Share Scope
Thread
PEM610WD MBR *SHRRD HELD *JOB

DATA *SHRUPD HELD *JOB

Post by Dieter Bender
Dieter Bender

Post by John Bailo
I'm seeing some odd locking behavior when using an DB2400 database and
running an ado.net transaction. My code -- in simplified form, appears
at the bottom.
I want to run several INSERT statements using the same iDB2Connection
and iDB2Command. Then I want to Commit() so that either all the data
goes in, or not.
1. After the first INSERT, a lock appears on the FILE
2. After the second INSERT, another lock appears on the FILE.
3. I can then add several more records, and no more locks appears
4. After the Commit(), one lock disappears but not the other.
I have run the code with every available IsolationLevel, including the
default, and the result is the same.
1. Where could this second lock come from?
2. Why does Commit() not remove it?
3. What additional properties or commands do I need to account for to
remove this lock?
4. I have read that there may be a journaling property that sets up an
'autocommit/rollback' in the event of a system crash -- could this be
what's causing it?
using System;
using IBM.Data.DB2.iSeries;
using System.Data;
using System.Diagnostics;
namespace simpleTtest
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class Class1
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
//
// TODO: Add code to start application here
string connStr= "Data Source=192.168.NNN.NNN;User
Id=XXXXXX;Password=XXXXX;Default Collection=DataLibrary;";
iDB2Connection p_iconn =
new iDB2Connection(connStr);
iDB2Transaction itrans=null;
iDB2Command icmd=new iDB2Command("INSERT INTO " +
"$MANINV.PEM610WD"+
"(" +
"W1BATCH)" +
"VALUES('100')", p_iconn);
p_iconn.Open();
itrans = p_iconn.BeginTransaction(
IsolationLevel.RepeatableRead
);
icmd.Transaction=itrans;
try
{
icmd.ExecuteNonQuery();
//after this statement a lock is generated
icmd.ExecuteNonQuery();
//after this statement a second lock appears
icmd.ExecuteNonQuery();
//no more locks; there are still two(2) at this point
itrans.Commit();
// after commit, one lock is removed -- one remains! why...?
}
catch(Exception e)
{
Debug.WriteLine(e.ToString());
itrans.Rollback();
}
finally
{
p_iconn.Close();
}
}
}
}

[John Bailo]

Post by Dieter Bender
Hi,
first of all, for using other isolation levels than none, the files have to
be setup with journaling (check with DSPFD).

Jounaling is on...I can see the Journal log.

Post by Dieter Bender
you should have the difference between file locks and record locks in your
mind; after the first insert a file lock appears and it might be there even
after the commit operation,

Is there an explicit command -- that can be issued via ADO, that would
release such a lock?

[John Bailo]

Just for testing, what if you create a second IDB2Command object,
with the same query and use that as the second insert. Does it create a
second lock then as well?

I tried that this morning -- but the behavior is exactly the same!

I also tested removing the .Commit() and just closing the connection.

That still only removes one lock, but the second persists until my
program ends.

Is there anything in Visual Studio that would show me all the 'live'
connections between my app and the database?

[John Bailo]

Ok, I found a fix for this but I don't like it.

When I set

Pooling=false

in the DB2400 connection string, then it released the second lock after
closing the connection...which I kind of what I would expect, since I
might want to have multiple transactions on the same connection.

Bottom line, our network latency is very high...and I think that having
pooling was creating orphan threads or something that couldn't be closed
by the iDB2Connection.Close() method.

If anyone has other theories, or any kinder, gentler way of managing the
connection pool....

Post by John Bailo

Just for testing, what if you create a second IDB2Command object,
with the same query and use that as the second insert. Does it create
a second lock then as well?

I tried that this morning -- but the behavior is exactly the same!
I also tested removing the .Commit() and just closing the connection.
That still only removes one lock, but the second persists until my
program ends.
Is there anything in Visual Studio that would show me all the 'live'
connections between my app and the database?

[Frans Bouma [C# MVP]]

Post by John Bailo

Just for testing, what if you create a second IDB2Command object,
with the same query and use that as the second insert. Does it create
a second lock then as well?

I tried that this morning -- but the behavior is exactly the same!

that's really strange. I then think of a setup issue in the as/400 but
I'm a complete newbie on that, so I can't help you with that. The
ADO.NET code you wrote should simply work.

Post by John Bailo
I also tested removing the .Commit() and just closing the connection.
That still only removes one lock, but the second persists until my
program ends.

Close will rollback transactions still running, and therefore locks
should be lifted, but it's the same as with the commit I think.

Post by John Bailo
Is there anything in Visual Studio that would show me all the 'live'
connections between my app and the database?

No, vs.net's just an editor, not a keeper of connections. The amount of
connections between client and server is typically transparent as
pooling is used by default by most providers (I'm not sure if the
iseries provider enables pooling by default).

FB