By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,795 Members | 1,666 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,795 IT Pros & Developers. It's quick & easy.

why it is exclusive lock

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
"xixi" <da****@yahoo.com> wrote in message
news:c0**************************@posting.google.c om...
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


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.
Nov 12 '05 #2

P: n/a
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?
Nov 12 '05 #3

P: n/a
"xixi" <da****@yahoo.com> wrote in message
news:c0**************************@posting.google.c om...
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?


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.
Nov 12 '05 #4

P: n/a
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.
Nov 12 '05 #5

P: n/a

"xixi" <da****@yahoo.com> wrote in message
news:c0**************************@posting.google.c om...
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.


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
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.