Singe thread timeout

D. Hecht

Singe thread timeout
If a single thread updates a row, doesn't commit, then issues a select
against that row, will the thread timeout, waiting for the X lock to be
released, which it never will, because the SQL is serialized.

In other words, can a thread timeout against itself.

We're seeing timeouts with one thread running against the database.

Row Level locking is in effect for the tablespace(s) in question, and the
Dynamic SQL is using a matching index scan. This is DB2 V8.

Thanks.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Mike Turner

Re: Singe thread timeout
(in response to D. Hecht)
No. A thread that owns a lock is never blocked by that lock. The thread that did the update owns the X-lock and will not even attempt to acquire an S-lock on the same resource (row) for the Select because it already has a more restrictive lock. I assume no commit between update and select.

Regards
Mike Turner
Email: [login to unmask email]
Tel: +44 (0)1565 873702
web: www.michael-turner.ltd.uk
----------------------------------------------------------------------------------------
Michael Turner Ltd. Registered in England and Wales No. 1433722. Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT Reg. No. GB 338 4159 44.

-----Original Message-----
From: [login to unmask email]
[mailto:[login to unmask email]On Behalf Of D. Hecht
Sent: Wednesday, 23 January 2008 02:28
To: [login to unmask email]
Subject: [DB2-L] Singe thread timeout



If a single thread updates a row, doesn't commit, then issues a select
against that row, will the thread timeout, waiting for the X lock to be
released, which it never will, because the SQL is serialized.

In other words, can a thread timeout against itself.

We're seeing timeouts with one thread running against the database.

Row Level locking is in effect for the tablespace(s) in question, and the
Dynamic SQL is using a matching index scan. This is DB2 V8.

Thanks.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Phil Grainger

Re: Singe thread timeout
(in response to Mike Turner)
The only time I have seen this is when a thread causes an index page
split - THEN it IS possible for a thread to get in it's own way

Otherwise, you are definitely allowed to read your own uncommitted data


Phil Grainger
CA
Product Management Director
Phone: +44 (0)1753 577 733
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]

Ditton Park
Riding Court Road
Datchet
Slough
SL3 9LL

CA plc a company registered in England and Wales under company
registration number 1282495 with its registered office at the address
set out above. VAT number 697904179.



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Mike Turner
Sent: 23 January 2008 10:14
To: [login to unmask email]
Subject: Re: [DB2-L] Singe thread timeout

No. A thread that owns a lock is never blocked by that lock. The thread
that did the update owns the X-lock and will not even attempt to acquire
an S-lock on the same resource (row) for the Select because it already
has a more restrictive lock. I assume no commit between update and
select.

Regards
Mike Turner
Email: [login to unmask email]
Tel: +44 (0)1565 873702
web: www.michael-turner.ltd.uk
------------------------------------------------------------------------
----------------
Michael Turner Ltd. Registered in England and Wales No. 1433722.
Registered Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT
Reg. No. GB 338 4159 44.

-----Original Message-----
From: [login to unmask email]
[mailto:[login to unmask email]On Behalf Of D. Hecht
Sent: Wednesday, 23 January 2008 02:28
To: [login to unmask email]
Subject: [DB2-L] Singe thread timeout



If a single thread updates a row, doesn't commit, then issues a select
against that row, will the thread timeout, waiting for the X lock to be
released, which it never will, because the SQL is serialized.

In other words, can a thread timeout against itself.

We're seeing timeouts with one thread running against the database.

Row Level locking is in effect for the tablespace(s) in question, and
the Dynamic SQL is using a matching index scan. This is DB2 V8.

Thanks.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

tim malamphy

Re: Singe thread timeout
(in response to Phil Grainger)
Is your timeout because of an application or catalog
object? I have a faint recollection of dealing with
something like this, and it had to do with "next key
locking", and I believe the timeout occurred on a
catalog resource. Can't remember the details, though
. Maybe this will jog someone else's memory, or
perhaps you could post the error message which names
the resource.

--- "Grainger, Phil" <[login to unmask email]> wrote:

> The only time I have seen this is when a thread
> causes an index page
> split - THEN it IS possible for a thread to get in
> it's own way
>
> Otherwise, you are definitely allowed to read your
> own uncommitted data
>
>
> Phil Grainger
> CA
> Product Management Director
> Phone: +44 (0)1753 577 733
> Mobile: +44 (0)7970 125 752
> eMail: [login to unmask email]
>
> Ditton Park
> Riding Court Road
> Datchet
> Slough
> SL3 9LL
>
> CA plc a company registered in England and Wales
> under company
> registration number 1282495 with its registered
> office at the address
> set out above. VAT number 697904179.
>
>
>
> -----Original Message-----
> From: DB2 Data Base Discussion List
> [mailto:[login to unmask email] On
> Behalf Of Mike Turner
> Sent: 23 January 2008 10:14
> To: [login to unmask email]
> Subject: Re: [DB2-L] Singe thread timeout
>
> No. A thread that owns a lock is never blocked by
> that lock. The thread
> that did the update owns the X-lock and will not
> even attempt to acquire
> an S-lock on the same resource (row) for the Select
> because it already
> has a more restrictive lock. I assume no commit
> between update and
> select.
>
> Regards
> Mike Turner
> Email: [login to unmask email]
> Tel: +44 (0)1565 873702
> web: www.michael-turner.ltd.uk
>
------------------------------------------------------------------------
> ----------------
> Michael Turner Ltd. Registered in England and Wales
> No. 1433722.
> Registered Office: 131 King Street, Knutsford,
> Cheshire, WA16 6EJ. VAT
> Reg. No. GB 338 4159 44.
>
> -----Original Message-----
> From: [login to unmask email]
> [mailto:[login to unmask email]On Behalf Of
> D. Hecht
> Sent: Wednesday, 23 January 2008 02:28
> To: [login to unmask email]
> Subject: [DB2-L] Singe thread timeout
>
>
>
> If a single thread updates a row, doesn't commit,
> then issues a select
> against that row, will the thread timeout, waiting
> for the X lock to be
> released, which it never will, because the SQL is
> serialized.
>
> In other words, can a thread timeout against itself.
>
>
> We're seeing timeouts with one thread running
> against the database.
>
> Row Level locking is in effect for the tablespace(s)
> in question, and
> the Dynamic SQL is using a matching index scan. This
> is DB2 V8.
>
> Thanks.
>
> The IDUG DB2-L Listserv is only part of your
> membership in IDUG. DB2-L
> list archives, the FAQ, and delivery preferences are
> at
> http://www.idug.org/lsidug under the Listserv tab.
> While at the site,
> you can also access the IDUG Online Learning Center,
> Tech Library and
> Code Place, see the latest IDUG conference
> information, and much more.
> If you have not yet signed up for Basic Membership
> in IDUG, available at
> no cost, click on Member Services at
> http://www.idug.org/lsms
>
> The IDUG DB2-L Listserv is only part of your
> membership in IDUG. DB2-L
> list archives, the FAQ, and delivery preferences are
> at
> http://www.idug.org/lsidug under the Listserv tab.
> While at the site,
> you can also access the IDUG Online Learning Center,
> Tech Library and
> Code Place, see the latest IDUG conference
> information, and much more.
> If you have not yet signed up for Basic Membership
> in IDUG, available at
> no cost, click on Member Services at
> http://www.idug.org/lsms
>
> The IDUG DB2-L Listserv is only part of your
> membership in IDUG. DB2-L list archives, the FAQ,
> and delivery preferences are at
> http://www.idug.org/lsidug under the Listserv tab.
> While at the site, you can also access the IDUG
> Online Learning Center, Tech Library and Code Place,
> see the latest IDUG conference information, and much
> more. If you have not yet signed up for Basic
> Membership in IDUG, available at no cost, click on
> Member Services at http://www.idug.org/lsms
>



____________________________________________________________________________________
Looking for last minute shopping deals?
Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

D. Hecht

Re: Singe thread timeout
(in response to tim malamphy)
Thanks for the replies.

First, RTS shows the index used has 0 pseudodeletes, 20 deletes, and 1391
inserts since the last reorg. REORGLEAFFAR and REORGLEAFNEAR are both at 0,
indicating no page splits. (An Explain indicates the index used, and the
Dynamic Statement Cache explain indicates actual run-time index usage.)

Since index pages are latched and not locked, how can a thread 'get in it's
own way' when there are index page splits?

Secondly, the timeout occurred against an application tablespace.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms