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();
}
}
}
}

--
Texeme Construct
http://texeme.com

[Frans Bouma [C# MVP]]

John Bailo wrote:

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?

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?

The behavior is pretty weird, you should get just 1 lock.

FB
--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

[John Bailo]

Frans Bouma [C# MVP] wrote:

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....

John Bailo wrote:

Frans Bouma [C# MVP] wrote:

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]]

John Bailo wrote:

Frans Bouma [C# MVP] wrote:

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.
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.
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

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

[Charles Wilt]

May simply be working as designed. I know the iSeries tends to keep
tables open in case they are needed again.

The important thing to check is what _records_ are locked and are the
record locks released when you do the commit?

HTH,
Charles
In article <w9********************@speakeasy.net>, ja*****@texeme.com
says...

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?

[Patrick Bielen]

Hi John,

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'm studying your code but i can't understand this...
icmd.ExecuteNonQuery();
icmd.ExecuteNonQuery();
icmd.ExecuteNonQuery();
itrans.Commit();

So you're running the same command three times ???
Cause i can see no loop or do/while lus anywhere.

Not sure if you gonna get this fixed anyway, but here is an example
of how i cleanup a database using the .net managed driver...

Private Sub cleanDB()
Dim conn As New iDB2Connection
Dim conStr As String = "DataSource = as400;UserId=" +
Environment.UserName
conn.ConnectionString = conStr
Try
conn.Open()
Catch ex As Exception
MessageBox.Show("ERROR: " + ex.Message, "Er is een fout opgetreden")
End Try
Dim acmd As New iDB2Command("SELECT ARNRVL, STLKVL FROM
ODLIBF46L.ARLOCLEEG ORDER BY ARNRVL", conn)
Dim adr As iDB2DataReader = acmd.ExecuteReader
Dim ocmd As New iDB2Command("DELETE FROM ODLIBF46L.ODVL WHERE ARNRVL =
@arnr AND STLKVL = " + _
"@loc AND FMKDVL = 'STF' AND MGNRVL = 'MHZ' AND STOKVL = 0", conn)
Dim Aantal As Integer = 0
Me.Hide()
Me.setText("Bezig met opschonen Database ODLIBF46L.ODVL...")
Me.Show()
'Application.DoEvents()
While adr.Read()
ocmd.DeriveParameters()
ocmd.Parameters("@arnr").Value = adr.GetValue(0)
ocmd.Parameters("@loc").Value = adr.GetValue(1)
ocmd.ExecuteNonQuery()
Aantal += 1
End While
Me.Hide()
MessageBox.Show(Aantal.ToString + " records doorlopen", "Opschoning
Voltooid")
Try
adr.Close()
conn.Close()
Catch ex As Exception
MessageBox.Show("ERROR: " + ex.Message, "Er is een fout opgetreden
!")
End Try
System.Environment.Exit(0)
End Sub

Okay, it is vb instead of C# but i think you can easily read the code.

Best Regards,

Patrick Bielen
MCP / SCJP

[jabailo]

Turns out the problem seemed to be related to connection pooling.

I had to step down the pooling to using only a single connection.

With multiple connections, it seemed to be leaving a connection open,
which held the lock so the second part of the transaction could not
complete.

Patrick Bielen wrote:

Hi John,

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'm studying your code but i can't understand this...

icmd.ExecuteNonQuery();
icmd.ExecuteNonQuery();
icmd.ExecuteNonQuery();
itrans.Commit();

So you're running the same command three times ???
Cause i can see no loop or do/while lus anywhere.

Not sure if you gonna get this fixed anyway, but here is an example
of how i cleanup a database using the .net managed driver...

Private Sub cleanDB()
Dim conn As New iDB2Connection
Dim conStr As String = "DataSource = as400;UserId=" +
Environment.UserName
conn.ConnectionString = conStr
Try
conn.Open()
Catch ex As Exception
MessageBox.Show("ERROR: " + ex.Message, "Er is een fout opgetreden")
End Try
Dim acmd As New iDB2Command("SELECT ARNRVL, STLKVL FROM
ODLIBF46L.ARLOCLEEG ORDER BY ARNRVL", conn)
Dim adr As iDB2DataReader = acmd.ExecuteReader
Dim ocmd As New iDB2Command("DELETE FROM ODLIBF46L.ODVL WHERE ARNRVL =
@arnr AND STLKVL = " + _
"@loc AND FMKDVL = 'STF' AND MGNRVL = 'MHZ' AND STOKVL = 0", conn)
Dim Aantal As Integer = 0
Me.Hide()
Me.setText("Bezig met opschonen Database ODLIBF46L.ODVL...")
Me.Show()
'Application.DoEvents()
While adr.Read()
ocmd.DeriveParameters()
ocmd.Parameters("@arnr").Value = adr.GetValue(0)
ocmd.Parameters("@loc").Value = adr.GetValue(1)
ocmd.ExecuteNonQuery()
Aantal += 1
End While
Me.Hide()
MessageBox.Show(Aantal.ToString + " records doorlopen", "Opschoning
Voltooid")
Try
adr.Close()
conn.Close()
Catch ex As Exception
MessageBox.Show("ERROR: " + ex.Message, "Er is een fout opgetreden
!")
End Try
System.Environment.Exit(0)
End Sub

Okay, it is vb instead of C# but i think you can easily read the code.

Best Regards,

Patrick Bielen
MCP / SCJP