Solved

Table locking problem

Posted on 2006-11-09
7
1,394 Views
Last Modified: 2008-01-09
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
Comment
Question by:robertjmackay
  • 4
  • 3
7 Comments
 
LVL 13

Expert Comment

by:ghp7000
ID: 17906523
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
 

Author Comment

by:robertjmackay
ID: 17906673
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
 
LVL 13

Expert Comment

by:ghp7000
ID: 17908601
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:robertjmackay
ID: 17914007
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
 
LVL 13

Expert Comment

by:ghp7000
ID: 17930332
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
 
LVL 13

Accepted Solution

by:
ghp7000 earned 500 total points
ID: 17932879
db2 update cli cfg for SECTION <dbname> using TXNISOLATION 4 on the machine where the app will be calling the database from

      
0
 

Author Comment

by:robertjmackay
ID: 17933044
Thanks,

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

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question