Improve optimistic locking performance

Hi Vinod,

We have reviewed your enhancement suggestion and acknowledge its benefit. However, other features are taking priority in our planning at this time. We feel that if optimistic locking was being designed for a new "green-field" SPM application right now, the design as proposed would be an option but it would not be worth the investment to retrofit this solution into the current logic. We would also need some real figures on the performance and other advantages that such an approach would result in before an investment would be made in changing this.

Thank you for taking the time to share your ideas with us. We are committed to involving our users in building our product roadmap and appreciate your suggestions.

Regards,
Shane McFadden, SPM Offering Management team
You can find more information on the request process here.

Hi Vinod,
Thank you for providing the additional information requested. Within the next 30 days, we will review the details you have provided and inform you of our analysis and decision.
Regards,
Shane McFadden, SPM Offering Management team
You can find more information on the request process here

The select for update query is unnecessary. If we are able to check how many records are updated using update tablename where versionno=:versionno and primarykey=:primarykey then we can avoid the select query

Basically the current code is mixing pessimistic locking and optimistic locking approaches. Below is the standard way of implementing optimistic locking

------------------------------------------------------------------------------------------------------------------------------------------------------------
There are two general approaches to locking.

First, you have pessimistic locking. In this approach, you lock the row (SELECT ... FOR UPDATE) which prevents anyone else from changing the row. Then you do the UPDATE. When you commit your change, the lock is released. There is no need in this case to have a version number/ timestamp column (at least not to support locking) and the code is relatively easy.

The downside to pessimistic locking is that you need to hold the lock the entire time a user is sitting on a page potentially editing data. This is technically really hard if you're building a web-based application since HTTP is a stateless protocol. The request that initially renders the page would normally get a connection from the connection pool, do the SELECT, and then return the connection to the pool once the page was done. The subsequent request to update the data would generally happen on a different connection with a different database session so you can't lock the row in the first session and update it in the second. If you wanted to pessimistically lock the row, you'd need to do a lot of work on the back end to ensure that the one database connection was tied to a particular middle tier session until the user was done editing the data. This generally has very negative impacts on scalability and introduces all sorts of session management problems-- how do you know, for example, whether I requested a page, locking a row, and then closed my browser without ever logging out or making a change? How long are you going to leave the record locked in the database? What happens if some other session is trying to lock the row? How long are you going to let that session block waiting for a lock if the first person went out to lunch? Generally, people don't implement pessimistic locking in web-based apps because managing sessions and session state is just too impractical.

The second option is optimistic locking. In this approach, you add a version number/ timestamp to the row. You select this version number/ timestamp when you query the data. Then you use this in your WHERE clause when you later do the update and check how many rows were actually modified. If you modify exactly one row, you know the row hasn't changed since you read it. If you modify 0 rows, you know that the row did change and you can handle the error.

So, for example, you'd select the data along with the version number

SELECT address_line1, city, state, zip, version
FROM addressTable
WHERE address_id = `<>`
When you were ready to do the update, you'd do something like this where you use the version in your UPDATE and throw an error if the row changed

UPDATE addressTable
SET address_line1 = `<>`,
city = `<>`,
state = `<>`,
zip = `<>`,
version = version + 1
WHERE address_id = `<>`
AND version = `<>`

IF( SQL%ROWCOUNT = 0 )
THEN
-- Darn. The row must have changed since you read it. Do something to
-- alert the user. Most likely, the application will need to re-query the
-- data to see what the address has been changed to and then ask the user
-- whether they want to re-apply the changes.
RAISE_APPLICATION_ERROR( -20001, 'Oops, the row has changed since you read it.' );
END IF;
Your application would then do something useful with the error. Normally, that would mean doing something like querying the data again, presenting the changes to the user, and asking them whether they still wanted to apply their changes. If, for example, I read an address and start editing it, go to lunch, my colleague logs in, reads the same address, makes some edits and saves it, then I return and try to save my changes, it would generally make sense to show me something telling me that my colleague already changed the address to something new-- do I want to continue making edits or do I want to abandon them.

Hi Vinod,

We have reviewed your enhancement suggestion and require more information to properly understand the issue and the business scenario you are trying to support.

Can you expand on why you feel the suggested update would be more beneficial/better than the current mechanism for optimistic locking?

In particular, can you provide more information on this statement, "If no records are updated by the above query then throw RecordChangedException" and how you think that would work (i.e. determining if "no records are updated")?

Please provide the requested information within 30 days so we may proceed with our evaluation. If we do not hear from you within that timeframe we will have to close the request due to insufficient information.

Thank you,
Shane McFadden, SPM Offering Management team
You can find more information on the request process here.

Hi Vinod,

Thank you for taking the time to share your ideas with us. We are committed to involving our users in building our product roadmap and appreciate your suggestions.

We will review the information you have provided and get back to you within 30 days. If additional details are required in order to complete our evaluation, we will send you a request for more information.

Thank you,
Shane McFadden, SPM Offering Management team
You can find more information on the request process here.