Multiple record update

I have a situation in which I want the user to be able to make the same update to a number of records that can be described by a keylist. The issue is that other users may lock one or more of the records that require updating. I realize that I can use commitment control to handle situations where a record lock by one user would cause an update by another user to fail.
Is there a way use "Create View" to grab the records I want and allocate that view so that no other users can update these records? My problem is that when I attempted this, the created view would not allocate. The physical that the view was created over was in use but *SHRRD in HELD status. My other fear is that if I can allocate the view ALL the records in the file would be unupdateable (is that even a word?).
Any thoughts would be appreciated.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

Processing your response...

Discuss This Question: 6 &nbspReplies

There was an error processing your information. Please try again later.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

If I understand your question correctly you're trying to build a view which captures the records you'd like the user to be able to update, then you're trying to update those records in the table from which they came by their primary key and you're concerned about locking.
First the view. The view itself can be used for updating on most databases (including MSSQL) if the view does not reference more than one table ie. a "join." If it does contain a join you're better off avoiding the use of a view altogether as the only solution to that problem is writing an "update trigger."
Assuming the view does not contain a join and thus is updatable you can simply use an update statement on the view to do the update and normal locking contrains will protect your data integrity. If you need to do a select prior to the update to further refine the update set do the whole thing in a transaction with the transaction isolation level set to "repeatable read."
Assuming the view does contain a join and is thus not updateable, use the select on the view in a subselect to obtain the list of primary keys for updating, then using an update statement on the table itself using that list. Again to preserve data integrity do all of that in a transaction with the transaction isolation level set to "repeatable read."
In both cases normal locking contraints that are inherent in transactions with the above mentioned isolation level will ensure that the records are locked at the right time, in the right order to preserve integrity.
Someone else suggested using a cursor. Personally I like the methods above over a cursor since cursors are usually a lot slower and more difficult to implement.
Good luck.

Mouska,
When you create a view and try to allocate it, the system tries to allocate the underlying table. But the real problem you are having is that other users are locking records. You won't solve that with an allocate or commitment control; when someone else has a lock on a record, you can't get it, period. That is why it is called a lock. Only thing you can do is find out why records are being held in other jobs and try to correct that situation.
Joep Beckeringh

Just trying to summarize the good advice above:
If you are trying to lock some records, a view won't do it. You need to use a transaction with isolation level "repeatable read". This will lock the records for your user. It will also prevent other users from accessing those records.
This solution will reverse your problem - your user with the list will get to do the update but others will be locked out. It sounds like this may be what you want.
A cursor update may be the solution if you don't want to lock those records. You run your cursor, check if optimistic lock has failed on each update, if it did, report to the user, but proceed to update the rest of the records. The user will have to re-attempt updating the remainder of the records. This would take more work and only necessary if the other users cannot wait.

Thanks for the summation. It helped me understand the other responses better. It does seem to me that I need to use an isolation level of repeatable read. The cursor, forcing me to go back and re-update records, is not the situation I want to find myself in.
Thanks again for the responses.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

Processing your reply...

Ask a Question

Free Guide: Managing storage for virtual environments

Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!

To follow this tag...

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy