Ash_EP3
Member
Registered: 15th May 07
Location: Melksham, Wiltshire
User status: Offline
|
As above.
I have a quick question in regards to Oracle and locked rows/sessions...
Thanks in advance 
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
Post it.
|
Ash_EP3
Member
Registered: 15th May 07
Location: Melksham, Wiltshire
User status: Offline
|
The scenario is simple... the application has a front end which communicates with the DB and can be updated from the back office or manually via SQLdeveloper or SQLplus.... the obvious issue is the exclusivity of rows and the problem lies when 1 or 2 users (or even a scheduled script) is updating a record at the same time (thus the last committed change will be the only update).
I just wanted to know what the options are for this type of scenario? It seems apparent that there are 2 choices... either lock the record down so no changes can be made whilst the record is being updated or just allow the record to stay unlock but run the risk of multiple changes at the same time being lost...
|
Gaz
Member
Registered: 24th Aug 03
Location: Widnes, Cheshire
User status: Offline
|
I used Oracle in my previous job with user's constantly locking out customers records. The solution completed by the support guys was to run a batch job that looked for records locked or stuck in session older that 45minutes as one of our user's didn't spend that long updating one record.
I'm unsure how it was done. or what effects it had (e.g lost data) and how they got around it but its what happened - just another avenue for you to think about.
|
Ash_EP3
Member
Registered: 15th May 07
Location: Melksham, Wiltshire
User status: Offline
|
quote:
Originally posted by Gaz
I used Oracle in my previous job with user's constantly locking out customers records. The solution completed by the support guys was to run a batch job that looked for records locked or stuck in session older that 45minutes as one of our user's didn't spend that long updating one record.
I'm unsure how it was done. or what effects it had (e.g lost data) and how they got around it but its what happened - just another avenue for you to think about.
Ah ok, thanks for the info mate
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
Your options are correct, either lock or not.
Curious as to why the commit should take over a few seconds? CS has to lock for updates but it's over and done very quickly so you generally wouldn't notice.
Is this to ensure the integrity of the read as well? Is there any scope to alter the table structures?
My first thoughts is that if you're offering people that amount of time to read, decide upon an update and then make it that you should have another table for the writes that inserts new records and you introduce a foreign key to that record to tie it to the other table.
You may introduce anomalies in terms of concurrent writes but you would have then been able to store them all and you can discriminate on date/time or something to decide which is the best one.
Might also be some scope to notify the users if a write has taken place and tell them they're too late to attempt a commit?
Really depends on the scenario.
|
