-1

going by this link I should be able to isolate the rows which are being read using a select statement but when I run below steps it doesn't lock the rows

  1. create table test ( col varchar(50));
  2. INSERT INTO test values('100');
  3. select COL from mdmsysdb.test WITH RS USE AND KEEP EXCLUSIVE LOCKS or
    select COL from mdmsysdb.test WITH RR USE AND KEEP EXCLUSIVE LOCKS
  4. in a parallel application when I run update statement it goes through properly.

What is wrong with my approach why is the row getting updated in step 4 from a parallel application when the select is defined to hold exlusive lock?

mustaccio
  • 18,234
  • 16
  • 48
  • 57
Waseem Ahmed
  • 57
  • 10
  • 1) Is journaling turned on - are the tables actually valid for transactions? 2) Are you sure you aren't releasing the transaction (eg `COMMIT`/`ROLLBACK`)? Normally you want transactions (Especially of the table-locking type) as short as possible. 3) Are you **not** updating `col`? The optimizer _may_ be smart enough to realize there's no "conflict" there. 4) ... why are you locking the entire table? That's usually a terrible idea. – Clockwork-Muse May 21 '18 at 07:11
  • 1
    Which variant of DB2 are you using? What client are you using? – jmarkmurphy May 21 '18 at 13:11
  • You reference the DB2 for z/OS documentation, yet your question is tagged with [db2-luw] and [db-400]. Which is it? Their SQL dialects and locking behaviour may differ. – mustaccio May 21 '18 at 19:29
  • I have put my question across all flavours of db2, I am using db2 - luw on RHEL. I have been attempting to run the sqls via RHEL command prompt. – Waseem Ahmed May 22 '18 at 00:57
  • 4) ... why are you locking the entire table? That's usually a terrible idea -- I am not trying to lock entire table but just the row which I am reading in select and will be updating subsequently in the update statement – Waseem Ahmed May 22 '18 at 01:00

1 Answers1

1

If you are using RHEL and running the SQL statements on the shell command line (bash or ksh etc). then you need to understand the default autocommit-behaviour.

Take care to use the correct SQL syntax for the version and platform of the Db2-server. These differ between Linux/Unix/Windows and i-Series and Z/OS. Each platform can behave differently and different settings per platform can adjust the locking behaviour.

The Db2 CLP on Windows/Linux/Unix will autocommit by default. So any locks taken by the statement are immediately released on statement completion when the automatic commit happens. This explains why (in different sessions) you cannot force to wait for a lock - the lock is already gone!

So the observed behaviour is correct - working as designed, just not what you incorrectly imagined. You can change the default behaviour by selectively disabling autocommit.

To disable autocomit, you have different choices. You can do it on the command line of the CLP to impact the current command line (use the +c option) , or you can use the DB2OPTIONS environment variable to set it permanently for the session (usually a bad idea), or you can enable/disable autocommit on the fly inside a script via the update command options using c off; and update command options using c on; commands.

To disable autommit on the command-line of the Db2 CLP , just for a single statement, then use the +c option, for example:

db2 +c "select COL from mdmsysdb.test WITH RS USE AND KEEP EXCLUSIVE LOCKS"

When you disable autocommit, you become responsible for performing an explicit commit or rollback. If you have used the +c option, any db2 command that omits the option will revert to default behaviour or DB2OPTIONS if set. So you have to know what you are doing and take care to properly test.

mao
  • 11,321
  • 2
  • 13
  • 29