Placing a Lock at record level in a table.

[chintalas]

Here when many users are trying to update the same table the dead lock
situation is arising. I like to know how i can put a lock at record
level in my table, so that the dead lock situation will not arise.
pls send me a CC to ks********@yahoo.com also

[Rhino]

<ch*******@gmail.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

Here when many users are trying to update the same table the dead lock
situation is arising. I like to know how i can put a lock at record
level in my table, so that the dead lock situation will not arise.
pls send me a CC to ks********@yahoo.com also

What version of DB2 are you using and what operating system are you on?
Also, are you worried only about applications conflicting or are dynamic
tools like SPUFI and QMF also a factor? It's hard to advise you without
knowing the answers to those questions.

Rhino

[Philip Sherman]

Try the SQL Reference V2. Lookup "ALTER TABLE" to get the exact syntax.

I'd be surprised if row level locking fixes your problem. Deadlocks are
frequently the result of other issues that arise when multiple programs
are attempting to update the same table. You'll need to do some lock
analysis to determine exactly what is happening.

Phil Sherman

ch*******@gmail.com wrote:

Here when many users are trying to update the same table the dead lock
situation is arising. I like to know how i can put a lock at record
level in my table, so that the dead lock situation will not arise.
pls send me a CC to ks********@yahoo.com also

[Thiru]

Hi,
The simple newbie solution is

ALTER TABLE <table-name> LOCKSIZE ROW

Make sure u have enough lock buffer to avoid lock escalation, which
again leads to the same problem.

Correct me if i am wrong.

Cheers,
Thiru.
WantedToBeDBA.

[Matt Emmerton]

"Thiru" <Wa***********@gmail.com> wrote in message
news:11*********************@l41g2000cwc.googlegro ups.com...

Hi,
The simple newbie solution is

ALTER TABLE <table-name> LOCKSIZE ROW

Make sure u have enough lock buffer to avoid lock escalation, which
again leads to the same problem.

Correct me if i am wrong.

While this is correct, "LOCKSIZE ROW" is the default locking mode in DB2, so
no extra steps are neccessary.

The problem that the OP is encountering are deadlocks, and there are only
two ways around this:
1) Use table locking (ALTER TABLE <table-name> LOCKSIZE TABLE), so that only
one query can use the table at a time. This is not recommended!
2) Rewrite the application to avoid deadlock situations.

--
Matt

[Mark A]

"Matt Emmerton" <me******@nospam.yahoo.com> wrote in message
news:sY********************@rogers.com...

While this is correct, "LOCKSIZE ROW" is the default locking mode in DB2,
so
no extra steps are neccessary.

The problem that the OP is encountering are deadlocks, and there are only
two ways around this:
1) Use table locking (ALTER TABLE <table-name> LOCKSIZE TABLE), so that
only
one query can use the table at a time. This is not recommended!
2) Rewrite the application to avoid deadlock situations.
Matt

Locksize row is the default on DB2 for LUW, but not necessarily for other
platforms like z/OS, which only had page or tablespace locking for many
years, (except for the SQL lock table statement).

DB2 does not allow explicit locking of objects in SQL except for the lock
table statement. DB2 employs what is called pessimistic locking, whereby the
database locks for you based on the SQL statement issued, and releases locks
based on the isolation level and/or commit time.

As Matt noted, deadlocks can be cured by looking at the application SQL
code, and with a knowledge of how DB2 works with respect to locking, you can
almost always resolve the problem. It is a bit difficult for the totally
un-initiated to figure it out, but with a knowledgeable person it could be
figured out and you could get some skills transfer in the process.

The other option is to post in this forum all the exact DDL and all the
exact SQL of the offending applications involved in the deadlocks. The
deadlock event monitor will help identify some of the information you would
need to post.