473,573 Members | 2,412 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SET CURRENT LOCK TIMEOUT

Hi all,
First of all, just want to let you know that I'm coming from
Informix background, and we just recently migrated to DB2. It's
been a great new learning experience. For the time being, I have
some questions regarding LOCK TIMEOUT and its compatibility with
LOCK MODE WAIT in INFOMIX.
Last week, I just found out that the following statement was
complied and built in Stored Procedure

SET LOCK MODE TO WAIT 5;

What I would like to achieve is for the application to wait for 5
seconds before it returns lock timeout error (SQLSTATE 40001).
And after I did some reading from DB2 Docs, that statement should
be equivalent to

SET CURRENT LOCK TIMEOUT 5;

But when I tested the stored procedure, I did not get the lock
timeout.
The Procedure waited indefinitely until I released the lock on the
table.
Does anyone know if this is a bug ? I am in contact with the support,
but
I would like to find out if anyone is experiencing the same thing. The
following is taken out of DB2 Information Center :

|WAIT integer-constant
| Specifies an integer value between -1 and 32767. A value of -1 is
| equivalent to specifying the WAIT keyword without an integer value.
| A value of 0 is equivalent to specifying the NOT WAIT clause. If the
| value is between 1 and 32767, the database manager will wait that
| number of seconds (if a lock cannot be obtained) before an error
|(SQLSTATE 40001 or SQLSTATE 57033) is returned.
|
|For compatibility with Informix:
|
| MODE can be specified in place of TIMEOUT.
| TO can be specified in place of the equals (=) operator.
| SET LOCK WAIT can be specified in place of
| SET CURRENT LOCK TIMEOUT WAIT.
| SET LOCK NO WAIT can be specified in place of
| SET CURRENT LOCK TIMEOUT NOT WAIT.

DB2 V.8.2 FIXPACK 8
LINUX SUSE 8

Thanks
N.

Nov 12 '05 #1
5 11076
<ha*********@gm ail.com> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .
Hi all,
First of all, just want to let you know that I'm coming from
Informix background, and we just recently migrated to DB2. It's
been a great new learning experience. For the time being, I have
some questions regarding LOCK TIMEOUT and its compatibility with
LOCK MODE WAIT in INFOMIX.
Last week, I just found out that the following statement was
complied and built in Stored Procedure

SET LOCK MODE TO WAIT 5;

What I would like to achieve is for the application to wait for 5
seconds before it returns lock timeout error (SQLSTATE 40001).
And after I did some reading from DB2 Docs, that statement should
be equivalent to

SET CURRENT LOCK TIMEOUT 5;

But when I tested the stored procedure, I did not get the lock
timeout.
The Procedure waited indefinitely until I released the lock on the
table.
Does anyone know if this is a bug ? I am in contact with the support,
but
I would like to find out if anyone is experiencing the same thing. The
following is taken out of DB2 Information Center :

Thanks
N.


The SET CURRENT LOCK TIMEOUT is an override for only for application that
invokes that command. So if you set in one application, the other
application will use the db configuration setting (see below). Of course, an
application is not blocked by locks taken by SQL statements in the same
application thread.

If you want to set it for all applications in a database, use the following
from the command line:

db2 connect to <database-name>
db2 update db cfg using locktimeout 5
Nov 12 '05 #2
Hi Mark,
Thank you for your reply. Yep, we have our db cfg SET the
LOCK TIMEOUT to 10. But one this particular application, I
just want it to wait for 5 seconds. So we issued the SET
LOCK MODE TO WAIT 5; statement for it. :)

Thanks
Mark A wrote:
<ha*********@gm ail.com> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .
Hi all,
First of all, just want to let you know that I'm coming from
Informix background, and we just recently migrated to DB2. It's
been a great new learning experience. For the time being, I have
some questions regarding LOCK TIMEOUT and its compatibility with
LOCK MODE WAIT in INFOMIX.
Last week, I just found out that the following statement was
complied and built in Stored Procedure

SET LOCK MODE TO WAIT 5;

What I would like to achieve is for the application to wait for 5
seconds before it returns lock timeout error (SQLSTATE 40001).
And after I did some reading from DB2 Docs, that statement should
be equivalent to

SET CURRENT LOCK TIMEOUT 5;

But when I tested the stored procedure, I did not get the lock
timeout.
The Procedure waited indefinitely until I released the lock on the
table.
Does anyone know if this is a bug ? I am in contact with the support,
but
I would like to find out if anyone is experiencing the same thing. The
following is taken out of DB2 Information Center :

Thanks
N.


The SET CURRENT LOCK TIMEOUT is an override for only for application that
invokes that command. So if you set in one application, the other
application will use the db configuration setting (see below). Of course, an
application is not blocked by locks taken by SQL statements in the same
application thread.

If you want to set it for all applications in a database, use the following
from the command line:

db2 connect to <database-name>
db2 update db cfg using locktimeout 5


Nov 12 '05 #3
<ha*********@gm ail.com> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.com.. .
Hi Mark,
Thank you for your reply. Yep, we have our db cfg SET the
LOCK TIMEOUT to 10. But one this particular application, I
just want it to wait for 5 seconds. So we issued the SET
LOCK MODE TO WAIT 5; statement for it. :)

Thanks

I assume you are still having a problem?

An application does block itself with DB2 locks, it only blocks other
applications. The definition of an application is a particular instance of
an program that is running and connected to DB2.

If that is not your problem, then you may have discovered a bug with the
Informix compatibility syntax using SET LOCK MODE TO WAIT 5, and you might
want to try SET CURRENT LOCK TIMEOUT 5. If that does not work, and you have
the latest fixpack (FP10) open a PMR with DB2 support.
Nov 12 '05 #4
Yep, Thank you Mark. The problem is

"SET CURRENT LOCK TIMEOUT 5;" works like a champ.
"SET LOCK MODE TO WAIT 5;" waits indefinitely. I believe
those 2 statements should be equivalent, am I correct ? I
know I can easily fix the statement and use LOCK TIMEOUT
on all of them. I just want to verify that I'm not doing anything
wrong and if this is a bug.

Thanks
N.

Nov 12 '05 #5
ha*********@gma il.com wrote:
Yep, Thank you Mark. The problem is

"SET CURRENT LOCK TIMEOUT 5;" works like a champ.
"SET LOCK MODE TO WAIT 5;" waits indefinitely. I believe
those 2 statements should be equivalent, am I correct ? I
know I can easily fix the statement and use LOCK TIMEOUT
on all of them. I just want to verify that I'm not doing anything
wrong and if this is a bug.

Thanks
N.

The two statements should be identical. I tried to repro (on Win Xp with
DB2 V8.2.3) but could not. Using SET LOCK MODE TO WAIT 5; properly times
my example out. Can you post a complete script?

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
2784
by: Antoon Pardon | last post by:
The queue and condition class allow threads to wait only a limited time. However this currently is implemented by a polling loop. Now for those who like to avoid polling I have here a Tlock class that allows for a timeout but doesn't use polling. All comments are welcome. ----------------------- Tlock.py -------------------------------
2
4816
by: john.livermore | last post by:
I am tracing a SQL Server 2000 production server that gets a query about every second. The Event I chose to watch was "Lock:Timeout". To my surprise I see many of these come through the trace. Is this normal behavior? What is "Lock:Timeout" showing me? Thanks, John
8
2564
by: Mark A | last post by:
The DB2 8.2 SQL Reference says SET CURRENT LOCK TIMEOUT is not under transaction control. I assume that means that changing it via the SQL command affects all connections to the database in the same way the db cfg parm LOCKTIMEOUT does? Anyone know if IBM is considering NO WAIT option (LOCKTIMEOUT =0) for an individual transaction or SQL...
14
6307
by: Sharon | last post by:
Hi all. I have an ArrayList and sometimes while enumerating through, i get an exception because another thread has added to the ArrayList. To solve this problem, i lock the enumeration, passing the ArrayList instance to the lock. But i still get the same exception. Thanks for your help. Sharon.
8
3999
by: Komandur Kannan | last post by:
We have a smart device application running on handhelds(Symbol MC9000G). The backend is Oracle and a middle tier web services development done in Vb.net. We use pessimistic Locking due to specific business needs When such a lock is made for a transaction (this code is in the web service) and the handhelds are terminated abnormally (cold...
2
2891
by: Kevin Frey | last post by:
Hello, I've been reading that ASP.NET serialises (ie. processes one at a time) HTTP requests if two simultaneous requests need to access the same session state. It also makes note that ASP.NET tries to reacquire a lock on the session state every 1/2 second until the timeout is reached, and then it will forcibly release the previous users...
1
3082
by: pike | last post by:
DB2 8.1 FP7a on AIX 5.2. $ db2 SET CURRENT LOCK TIMEOUT NOT WAIT DB20000I The SQL command completed successfully. $ db2 values current lock timeout 1 ----------- 0
0
11692
by: cwho.work | last post by:
Hi! We are using apache ibatis with our MySQL 5.0 database (using innodb tables), in our web application running on Tomcat 5. Recently we started getting a number of errors relating to java.sql.SQLException: Deadlock found when trying to get lock; Try restarting transaction message from server: "Lock wait timeout exceeded; try restarting...
4
6132
by: jankhana | last post by:
Hi all, I'm having an application in that i use Sql Compact 3.5 with VS2008. I'm running multiple threads in my application which contacts the compact database and accesses the row. It selects and deletes those rows in a fashion i.e selecting and giving to the application 5 rows and deleting those rows from the table. It works great...
0
7789
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7707
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8037
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
6432
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5296
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3737
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2227
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1325
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1048
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.