Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1437
  • Last Modified:

Table locking problem

I have a VB.NET application that uses OLEDB to connect to DB2.  the DB2 database is also used by another application that uses UNIFACE to establish connection and manage the locking state of the various tables.

When the application that uses uniface is open, I get deadlock errors.

Below is the connection string I am using:

connection.ConnectionString = "Location=""" & IPaddress_tbox.Text & """;Provider=""IBMDADB2.1"";Mode=readwrite;User ID=" & user_tbox.Text & ";Data" & 
" Source=" & database_tbox.Text & ";Password=" & password_tbox.Text

It appears that I am locking exclusive on any table I open for output.  How can I make it so that the sharing mode is such that other applications (not .NET) can be reading/writing at the same time?
0
robertjmackay
Asked:
robertjmackay
  • 4
  • 3
1 Solution
 
ghp7000Commented:
can u post the sqlcode/reason code you are receiving?
sql0911 can have different reason codes, if the reason code is 68, then its lock timeout. If its 2, then its deadlock. I have no idea what uniface does, but looking at the uniface site on Compuware, I dont see how it manages locking. In any case, if you are getting deadlock errors, it means another user has exclusive lock on row that you are trying to read/write. So the problem is with the locking of the other user, not you
0
 
robertjmackayAuthor Commented:
Actually,

I am not trying to do an update.  I am executing either a delete or insert statement, which kinda makes me believe that the table is being locked, not the row.

I set the imeout of my executenonquery statement to 60 seconds and I get one of the two errors:

Unhandled Exception: System.Data.OleDb.OleDbException: [DB2/NT] SQL0952N  Processing was cancelled due to an interrupt.  SQLSTATE=57014

or

Unhandled Exception: System.Data.OleDb.OleDbException: [DB2/NT] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "2". SQLSTATE=40001
0
 
ghp7000Commented:
same response, its the other user that has the lock, not you. If you had the table lock, then your statements would be able to complete. Try running your app when the UNIFACE app is not connected to the database.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
robertjmackayAuthor Commented:
It seems that the VB.NET program is triggering the DB2 table into a lock.  How do I change the default isolation level for teh OLEDB provider?
0
 
ghp7000Commented:
couple of ways you can do this
if practical, change you sql statements in the application to use the 'with CS' or with 'RS' or with 'UR' or with 'RR' clause at the end of the SELECT statement, or use the for read only clause
update the db2cli.cfg file with the default isolation level for the database
db2 update cli cfg for <dbname> using TXNISOLATION 4 on the machine where the app will be calling the database from
0
 
ghp7000Commented:
db2 update cli cfg for SECTION <dbname> using TXNISOLATION 4 on the machine where the app will be calling the database from

      
0
 
robertjmackayAuthor Commented:
Thanks,

We have determined that there was a setting in DB2 that was causing the table lock.  Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now