Multiple record update

pts.
Tags:
SQL
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Use a cursor with the FOR UPDATE clause. This locks the records and puts the other users in a holding pattern until you excercise the UPDATE WHERE CURRENT OF cursorname.

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
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
  • Musicmikem
    First of all need a little background on platform, database, ect. Are you using Sql statements or some programming language?
    0 pointsBadges:
    report
  • Amigus
    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.
    0 pointsBadges:
    report
  • Mouska
    Thanks for the help. I will try the suggestions. To answer the questions. Imbeeded SQL in RPGLE on as AS400. View is not a join, it is over a single table. Thanks again.
    0 pointsBadges:
    report
  • Joepbeckeringh
    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
    0 pointsBadges:
    report
  • Gabnaim
    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.
    0 pointsBadges:
    report
  • Mouska
    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.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

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

REGISTER or login:

Forgot Password?
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

Thanks! We'll email you when relevant content is added and updated.

Following