What is the locking behavior for Updatable/Insertable Views?

Question

Consider I have a View that is merely a simple join between 2 tables, selecting all columns.

The View is Updatable and Insertable.

What would be the locking behavior when executing an UPDATE or INSERT statement in the following situations:

1. I’m going to insert a record into the view, and only columns of table A have values.
2. I’m going to update a record in the view, but only columns from table B were modified.

My doubt is basically if on situation 1, only table A is going to be locked, and if on situation 2, only table B is going to be locked.

I’ve searched for the answer in IBM’s documentation, but couldn’t find an appropriate answer. If possible, I'd like to know where IBM is keeping this information.

Answer 1

The short answer is that it depends on the isolation level of your connection and/or statement being run.

If you are taking the default isolation level (which is usually Cursor Stability - CS) in DB2, then I would assume it only puts a lock on the actual rows being modified (as an update lock) and it puts a read lock on the row currently being held by the cursor. All other rows are not technically locked in any sort of way.

Now if you go up higher into Read Stability (RS) or Repeatable Read (RR), then you start to get into where it will lock more rows in the result set (and even the whole table in a join case)....

You may wish to check out the Isolation Level settings in the DB2 Information Center, as this will give you a better picture of how it will act.

Also note, even though there is a default isolation level for the connection, this can be overridden in the session, as well as at the statement level, including the underlying statement that builds the view. So if the default is CS, but the view was built with a SELECT that uses RR, then you are using RR and probably locking on all rows in both the result set and all rows utilized by the engine to create the result set. I'm guessing this is not likely (or at least hopefully someone didn't write the view that way....) and you are using CS, in which what I explained at the beginning is more likely how it behaves.