In a nutshell, I am trying to update a table using a column in the where clause that is non-unique and NOT the primary index of the table. I need to find a way to avoid a table lock in this scenario. This is what I have tried or witnessed in trying to avoid a table lock and achieve a rowhash lock.
1. If I create a NUSI I automatically get a table lock
2. If I create a single table join index on the base table redefining the index as a NUPI using the non-unique column, the base table update is still a table lock.
3. I cannot create a USI as the column is non-unique
4. I cannot create a partition as this is in an oltp environment and the partition would not be included in many workloads.
Is using the NUPI the ONLY alternative to get a rowhash lock on a table if trying to update using a column in the where clause that is non-unique and NOT the NUPI?
We have multiple update access paths to a table in existing code. I wanted to find a way to solve this at the database level without a code change but the NUSI and STJI do not solve my problem. Any other suggestions or feedback?
Sorry, but if you want a rowhash lock then the WHERE clause must have equality constraint against every column in the Primary Index of the table. If there are multiple columns then these equality constraints must be AND'd together.
If the table has a PPI then you must also have equality constraint(s) against all columns in the partitioning scheme.
You say "We have multiple update access paths to a table in existing code". Sorry of this bit comes across as pedantic but I think you mean "We have multiple update selection criteria (WHERE clauses) to a table in existing code".
In this case you really need to have a common selection column - which from what you've said I'm guessing that you don't.
The question doesn't really have anything to do with anding together columns to make sure the NUPI is used. In this case the NUPI is only one column.
What I mean by multiple update access paths to the table is one workload wants to update the table on the NUPI while another workload wants to update the table on a different non-indexed column. The workload that updates on the NUPI is good. We get an expected rowhash lock. The other workload, as expected, gets a table lock. I have tried to solve the table lock a couple of different ways including defining a STJI on the table with a different NUPI column ( The column the second workload uses). All other alternatives produce a table lock.
At this point, given the above scenario, I've concluded updating a table on anything other than the NUPI will issue a table lock which is not good for us. Many of the developers assume updating through a NUSI is good but that creates a table lock as well. We can solve this other ways but will require a code change. I just wanted to see if anyone else has ever been able to get around this issue. It is a bit difficult to explain so sorry if I confused things.
Keep in mind I am not updating any indexes just using in the selection criteria.
Sounds you are in OLTP processing mode.
You could extract the PI and PK for all rows which need to be updated and update the rows based on the PI and PK afterwards.
Two things, one for clarification and one possible way forward (although it will require code changes - possibly lots of them).
We may be talking at cross purposes here, but what I mean about "AND'ing columns together" is independent of how many columns in a PI.
As an example, assume you have the following table definition:
CREATE TABLE t1 (col1 INTEGER ,col2 INTEGER ,col3 INTEGER ... ) PRIMARY INDEX (col1, col2);
In order to get a row hash lock when running an UPDATE command you have to code something like:
UPDATE t1 SET col3 = new_value WHERE col1 = value_1 AND col2 = value_2 ...;
- all columns in the PI are in the WHERE clause
- all columns are tested with an '=' comparison
- all tests in the WHERE clause are connected by an AND (not using OR)
@ulrichmakes a good point in that if you can build a list of values that contain all of the PI values (to get a row hash lock) and all of the PK values (to ensure update happens to only the correct row) then you can run an OLTP like application.
The above is effectively the approach used by utilities such as TPT Update or TPT Stream.These require an input set of data usually in an external file which are then processed sequentially. I'm wondering if you can mimic that approach.
How about using a stored procedure (SP) instead of running a 'raw' UPDATE command from your application?
As I said, this will entail a number of code changes but I think in principle it should work.
Worth a try?
Thanks to both of you for taking the time out of your day to take a look at this! I would have liked to have solved this problem at the database level with no code changes. At first the NUSI sounded good until I realized it is ALWAYS a table lock. The STJI sounded promising until the explain showed me the index table is updated with with a rowhash lock but the base table is still updated with a table lock. In an OLTP environment it is so unfortunate that the NUSI update always applies a table lock. There are many ways to solve this programmatically in which one of the options is exactly what Ulrich presented. I read somewhere that an aggregate STJI may produce a rowhash lock but don't think it would work in our scenario. Thank you all again!