Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    6

    Unanswered: Select for Update

    My Issue,

    I have a Queue Table, which gets inserted from online transactions.
    There are around 4-5 process(all with same code) which polls this table for data . As soon as they find data they start processing it.

    Each process will select data depending upon Status, which is one of the columns, then update it

    We want only one process to access one particular row of the table.
    if we do SELECT FOR UPDATE , the lock which is placed on the row is IX

    Will this let other process also having 'SELECT FOR UPDATE' query pick the same row or something else ? or will the IX lock prevent the other processes to access those rows

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Another process cannot acquire an IX lock (intent to update) on a row that already has an IX lock on that row. The other process will not skip over the row that has the IX lock, it will just wait until the lock is released (usually at commit time).

    Another resource can acquire an S lock (result of a simple select) on a resource that has an IX lock.

    If the multiple processes are going after different rows, then you want to ensure that an index is used (if possible) to select the rows that will be updated. If a tablespace scan is used, then the process will have lock contention against each other, even though they are going after different rows as specified by the WHERE clause, since some of the rows in the tablespace scan will have IX locks. The best way to solve this is usually to alter the table to set it to VOLATILE, which strongly encourages DB2 to use an available index if possible, even if the table is very small. This will usually solve you lock contention issues in this situation.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I would suggest solving the contention problem at the application level. For example, a process would select an entry to work on, update its status to something like 'IN PROGRESS', locking the record momentarily, and then release the lock. Other processes would then skip those records having status 'IN PROGRESS'.

    The application-based solution has an advantage of being scalable almost infinitely, whereas the lock-based approach can stop working under some conditions. After all, there's no guarantee that the optimizer chooses index access over a table scan, there's only a probability. Besides, queue tables are usually relatively small and there's a good chance that index won't be used. Even if it is used, it won't be a unique index - more like a range scan: "select ... from queuetable where state = 'WAITING' order by created_ts desc", and the very first process will happily block all others.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by n_i
    After all, there's no guarantee that the optimizer chooses index access over a table scan, there's only a probability.
    If the table is altered to VOLATILE and there is a suitable index available (one that matches a column in the WHERE clause), it is more than a probability, it is a certainty that an index will be used.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •