why it is exclusive lock

hi, i am using db2 udb v8.1 on windows, i create a index on a unique
value column on a table to try to create row lock, here is what i do ,

sql = select value from table where id=1 for update, the auto commit
is true. if the table has more than one row, i got row U lock, but if
the table has only one row , i get row X lock, is this right, why is
that? i would like this only create row U lock, not row X lock. thanks
for advice

re: why it is exclusive lock

"xixi" <dai_xi@yahoo.com> wrote in message
news:c0f33a17.0404130921.32648ce3@posting.google.c om...[color=blue]
> hi, i am using db2 udb v8.1 on windows, i create a index on a unique
> value column on a table to try to create row lock, here is what i do ,
>
> sql = select value from table where id=1 for update, the auto commit
> is true. if the table has more than one row, i got row U lock, but if
> the table has only one row , i get row X lock, is this right, why is
> that? i would like this only create row U lock, not row X lock. thanks
> for advice[/color]

Even when a U lock occurs for rows to be updated, an X lock is taken when
the specific row is actually updated.

I don't know if you are confusing an X lock with a table lock. An X lock is
an exclusive lock, either at the row or table level, depending on the
locksize. Locksize depends on whether lock escalation has occurred, or
whether a lock table SQL statement has been issued, or whether the table has
been altered to set the locksize.

re: why it is exclusive lock

i found more info, when there is only row in the table and auto
commit is true, select ... for update only create a row U lock and
table IX lock, then when try to do update sql …on this row , after
statement.execute(sql), the row U lock becomes a row X lock, but this
doesn't happen to the table with more than one records. e.g. table 2
has 10 records, same kine of index created, auto commit is true,
update sql won't escalate the row U lock to row X lock. please advise.
this is a strange behavior i didn't know from DB2, does the table size
matter as regards to row lock?

re: why it is exclusive lock

"xixi" <dai_xi@yahoo.com> wrote in message
news:c0f33a17.0404140952.749f7653@posting.google.c om...[color=blue]
> i found more info, when there is only row in the table and auto
> commit is true, select ... for update only create a row U lock and
> table IX lock, then when try to do update sql .on this row , after
> statement.execute(sql), the row U lock becomes a row X lock, but this
> doesn't happen to the table with more than one records. e.g. table 2
> has 10 records, same kine of index created, auto commit is true,
> update sql won't escalate the row U lock to row X lock. please advise.
> this is a strange behavior i didn't know from DB2, does the table size
> matter as regards to row lock?[/color]

I don't understand your concern. The locks are at the row level, even the X
lock is at the row level, so there should be no problem. According to the
documentation, the U lock is converted to a X lock when the row is updated,
but you just don't see it happen.

re: why it is exclusive lock

my concern is the behavior of DB2 is different on different size of
table, if i have only one row in the table, and while this row being
updated, simply after stm.executeUpdate(sql), the row U lock becomes X
lock, instead of release the lock, i have to do cursor.close() (cursor
is the resultset which create the row U lock ) and connection.commit()
to release the X lock (even auto commit is true) , but if the table
has more than one row, after stm.executeUpdate(sql) will release the
row U lock right away, so that is the problem i try to understand.

re: why it is exclusive lock

"xixi" <dai_xi@yahoo.com> wrote in message
news:c0f33a17.0404160808.3552ae51@posting.google.c om...[color=blue]
> my concern is the behavior of DB2 is different on different size of
> table, if i have only one row in the table, and while this row being
> updated, simply after stm.executeUpdate(sql), the row U lock becomes X
> lock, instead of release the lock, i have to do cursor.close() (cursor
> is the resultset which create the row U lock ) and connection.commit()
> to release the X lock (even auto commit is true) , but if the table
> has more than one row, after stm.executeUpdate(sql) will release the
> row U lock right away, so that is the problem i try to understand.[/color]

If you're using Perl DBI or CLI to access the database, your cursor is
probably being created implicitly using the WITH HOLD option. This means
that the locks are released when you close the cursor, instead of when you
commit.

You will want to look at the CURSORHOLD configuration option, described
here:
http://publib.boulder.ibm.com/infoce...d/r0008781.htm

The locking characteristics of DB2 also change depending on your isolation
level, especially with cursors. CS isolation is the default when using Perl
DBI or CLI.

You can read more about isolation levels here:
http://publib.boulder.ibm.com/infoce...n/c0004121.htm

--
Matt Emmerton
DB2 OLTP Performance
IBM Toronto Lab