| Author |
Message
|
| ein |
 Posted: Fri May 06, 2011 12:12 am Post subject: DB Lock in ESQL WMB |
 |
|
Centurion
Joined: 14 Mar 2009
Posts: 108
|
Hi ,
I have problem with DB lock. Here is my problem description.
I have 2 brokers in my env, i deployed the code on the both brokers. But I have one common database which access by both brokers.
My problem is , When my first broker is trying to access the Database at the same my second broker is not able access the database it thorws DB errors.
individually the code is working fine, But when it run simultaneously it creates problem for me.
I applied BEGIN ATOMIC END on where ever i execution DBSQL stmts. But still i am facing the issue.
Is there any function to apply DBLock & Release or others we need apply.
Can you please suggest Please |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Fri May 06, 2011 12:40 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
You need to talk to your DBA's to optimize locking and maybe indexing.
Are you locking per table, per Page or per Row? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| khudania |
| Posted: Fri May 06, 2011 12:57 am Post subject: |
|
|
Apprentice
Joined: 30 Nov 2004
Posts: 43
|
Are you using the same Datasource for both the brokers ?
If you define more than one ODBC connection to the same data source, you might get database locking problems.
_________________
If the doors of perception were cleansed, everything would appear as it is - infinite |
|
| Back to top |
|
|
| ein |
| Posted: Fri May 06, 2011 1:33 am Post subject: |
|
|
Centurion
Joined: 14 Mar 2009
Posts: 108
|
| yes We are using same datasource, But we have 2 brokers on two different m/c. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Fri May 06, 2011 1:41 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
The locking is in my experience very much dependant upon the types of operations you are doing on the target table.
I've had READ locking issues that were cured by changing to using the PASSTHRU function and making the SQL code used non locking/readonly.
The actual SQL is not standard and does vary between DB types.
You can also help by limiting the size of the result set returned.
From memory, and for SQL Server (This was 4+ years ago)
| Code: |
select * top 1 readonly from table where x = y
|
If you'd like to supply more details of the operation type then I'm sure you will get some more help.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| khudania |
| Posted: Fri May 06, 2011 1:48 am Post subject: |
|
|
Apprentice
Joined: 30 Nov 2004
Posts: 43
|
When the locking is due to Automatic Transaction setting where lock is released only after the flow is completed and a different connection is already waiting.
Infocenter says following for this type of locking
| Quote: |
You can use either of two options to avoid this type of locking problem:
Design your message flow so that uncommitted (automatic) operations do not lock database objects that subsequent operations access across a different ODBC connection.
Configure the lock timeout parameter of your database so that an attempt to acquire a lock fails after a specified length of time. If a database operation fails because of a lock timeout, an exception is thrown that the broker handles in the typical way.
|
http://publib.boulder.ibm.com/infocenter/wmbhelp/v7r0m0/index.jsp?topic=/com.ibm.etools.mft.doc/ac00645_.htm
_________________
If the doors of perception were cleansed, everything would appear as it is - infinite |
|
| Back to top |
|
|
| ein |
| Posted: Fri May 06, 2011 1:56 am Post subject: |
|
|
Centurion
Joined: 14 Mar 2009
Posts: 108
|
Actully my code have insert and select SQLstmts and these stmts are executed by using PASSTHRU functions.
As you said limiting the size my SELECT Query fetching One row based on some trnid.
May I Know SQL code used non locking/readonly? |
|
| Back to top |
|
|
| artykito |
| Posted: Fri May 06, 2011 1:57 am Post subject: |
|
|
Apprentice
Joined: 24 Jun 2010
Posts: 33
|
Maybe you could try the following, that I did once in a project:
1- Create a function in ESQL which calls another synchronized Java method passing the parameters (in our case the numbers of parameters are always the same, if not...maybe you could think about a solution, for example, concatenate all and separate them between an special char).
2- The synchronized Java method do the query to the DB and it returns the values to the ESQL function.
Perhaps a problem could be the number of connections (we have that problem with Oracle because we had a lot of calls to the same DB and the connections has a delay until they close).
I don't know too much Java, and don't know what happen when you do the call from 2 different brokers, but we do that doing a lot of calls at the same time to the DB and they works fine...
I hope this could help you, or at least, give you more new ideas to fix the problem... |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri May 06, 2011 2:03 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| ein wrote: |
| May I Know SQL code used non locking/readonly? |
The code for this is different for each kind of database.
You should discuss this somewhat with your DBAs. |
|
| Back to top |
|
|
| khudania |
| Posted: Fri May 06, 2011 2:08 am Post subject: |
|
|
Apprentice
Joined: 30 Nov 2004
Posts: 43
|
| ein wrote: |
Actully my code have insert and select SQLstmts and these stmts are executed by using PASSTHRU functions.
As you said limiting the size my SELECT Query fetching One row based on some trnid.
May I Know SQL code used non locking/readonly? |
What is the Transaction setting for the compute node ? Do you require your transaction to span over the entire flow (ie Automatic) ? If not, you can use transaction setting as 'commit'.
_________________
If the doors of perception were cleansed, everything would appear as it is - infinite |
|
| Back to top |
|
|
| ein |
| Posted: Fri May 06, 2011 3:23 am Post subject: |
|
|
Centurion
Joined: 14 Mar 2009
Posts: 108
|
| My transaction setting was Automatic, |
|
| Back to top |
|
|
| ein |
| Posted: Fri May 06, 2011 3:45 am Post subject: |
|
|
Centurion
Joined: 14 Mar 2009
Posts: 108
|
I jst tried with transaction setting was commit.
and i tried processing the transactions all are getting failed. |
|
| Back to top |
|
|
| khudania |
| Posted: Fri May 06, 2011 4:01 am Post subject: |
|
|
Apprentice
Joined: 30 Nov 2004
Posts: 43
|
| ein wrote: |
I jst tried with contact admin setting was commit.
and i tried processing the transactions all are getting failed. |
Failed ? you mean nothing is updated or there is some error ? Broker 1 or Broker 2 ? What happened to the initial locking error problem ?
_________________
If the doors of perception were cleansed, everything would appear as it is - infinite |
|
| Back to top |
|
|
| ein |
| Posted: Fri May 06, 2011 4:39 am Post subject: |
|
|
Centurion
Joined: 14 Mar 2009
Posts: 108
|
All transaction got failed nothing was updated with transaction settings with Commit mode.
earliar it was automatic , very few transaction failed like for 1000 transactions 2 are got failed. |
|
| Back to top |
|
|
| fschofer |
| Posted: Fri May 06, 2011 6:50 am Post subject: |
|
|
Knight
Joined: 02 Jul 2001
Posts: 524
Location: Mainz, Germany
|
|
| Back to top |
|
|
|
|
