Search code examples
db2db2-luw

DB2 select row isolation not working

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?

Solution

  • 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.