 | |
dbms_lock.allocate_unique and autonomous transactions (long)
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: dbms_lock.allocate_unique and autonomous transactions (long) -
11-01-2006
, 05:55 PM
|
DA Morgan wrote : hasta_l3 (AT) hotmail (DOT) com wrote: DA Morgan wrote : hasta_l3 (AT) hotmail (DOT) com wrote: vc wrote : hasta_l3 (AT) hotmail (DOT) com wrote: [...] More concretely, the whole list structure is being changed, and the change should be atomic with regard to other operations that may also change the structure. However, the short transactions - which dont care about the structure - should not block. So only the transactions modifying the entire list would select for update the list header and therefore be executed serially, the smaller transactions that do not care about the list structure would proceed in the usual way without locking the header first. What is the problem ? Assume a (long) restructuring operation L modifies (a) some fields of the list header row in the master table, then (b) some fields of every list line row in the children table, including -say - row R. Assume that a short transaction S modifies (a) some (other) fields of the list line row R. If L is in a single long transaction, then it will block S if S happens to want to modify R after L did it. Look up PRAGMA AUTONOMOUS_TRANSACTION. It may help. Yes, Daniel. Autonomous transactions were actually the main question of my initial (very long :-) post. But here you do suggest to (1) have the main transaction lock the list header row, and (2) modify and commit each list line row in its own autonomous transaction, right ? It will work, I think, but I dont see how it is much different from my proposed scheme to (1) lock the list header row with a dbms_lock, and (2) modify and commit each list line row in the main transaction(s). One scheme may be better than the other, but I really cannot see which or why. Incidentally, reifying the list/line relationship into its own table might perhaps be an appealling design. The short transactions would modify the list line rows, and the long restructuring transaction would modify the relationship table rows. Unfortunatly, I cannot contemplate to make *that* kind of change right now... The difference is that you can commit in the autonomous transactions and not commit the main transaction. Something that is different from what you get using DBMS_LOCK. -- Yes. While the difference is unimportant (as of now) in my context, and using a lock minimizes the amount of changes, your suggestion as a "smell" of rigor and elegance that I like a lot... I will very seriously consider it. Thanks. Any known caveat on a massive use of autonomous transactions ? (8.1.7 and later...) --- Raoul |
Also, it seems strange that you would not care about consistency of
your line rows. See
http://asktom.oracle.com/pls/ask/f?p...11504247549852
It smells of other dbms's models.
jg
--
@home.com is bogus.
"Fig bars are actually one of the seven perfect foods, the others being
SPAM, bologna, spearmint gum, Ju-Ju Bees, Cheez Whiz and lard." - Dr.
Science
#12
| |||
| |||
|
|
Any known caveat on a massive use of autonomous transactions ? (8.1.7 and later...) --- Raoul |
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#13
| |||
| |||
|
|
hasta_l3 (AT) hotmail (DOT) com wrote: DA Morgan wrote : hasta_l3 (AT) hotmail (DOT) com wrote: DA Morgan wrote : hasta_l3 (AT) hotmail (DOT) com wrote: vc wrote : hasta_l3 (AT) hotmail (DOT) com wrote: [...] More concretely, the whole list structure is being changed, and the change should be atomic with regard to other operations that may also change the structure. However, the short transactions - which dont care about the structure - should not block. So only the transactions modifying the entire list would select for update the list header and therefore be executed serially, the smaller transactions that do not care about the list structure would proceed in the usual way without locking the header first. What is the problem ? Assume a (long) restructuring operation L modifies (a) some fields of the list header row in the master table, then (b) some fields of every list line row in the children table, including -say - row R. Assume that a short transaction S modifies (a) some (other) fields of the list line row R. If L is in a single long transaction, then it will block S if S happens to want to modify R after L did it. Look up PRAGMA AUTONOMOUS_TRANSACTION. It may help. Yes, Daniel. Autonomous transactions were actually the main question of my initial (very long :-) post. But here you do suggest to (1) have the main transaction lock the list header row, and (2) modify and commit each list line row in its own autonomous transaction, right ? It will work, I think, but I dont see how it is much different from my proposed scheme to (1) lock the list header row with a dbms_lock, and (2) modify and commit each list line row in the main transaction(s). One scheme may be better than the other, but I really cannot see which or why. Incidentally, reifying the list/line relationship into its own table might perhaps be an appealling design. The short transactions would modify the list line rows, and the long restructuring transaction would modify the relationship table rows. Unfortunatly, I cannot contemplate to make *that* kind of change right now... The difference is that you can commit in the autonomous transactions and not commit the main transaction. Something that is different from what you get using DBMS_LOCK. -- Yes. While the difference is unimportant (as of now) in my context, and using a lock minimizes the amount of changes, your suggestion as a "smell" of rigor and elegance that I like a lot... I will very seriously consider it. Thanks. Any known caveat on a massive use of autonomous transactions ? (8.1.7 and later...) --- Raoul You may have issues with latching and checkpointing. |
Quote:
|
Also, it seems strange that you would not care about consistency of your line rows. See http://asktom.oracle.com/pls/ask/f?p...11504247549852 |
However, it will take me quite a time to digest all the implications,
and right now I have trouble to see why it would be relevant here.
Would you have the time to elaborate a bit ?
Short and long transactions don't change - or even look at - the
same row fields.
Quote:
|
It smells of other dbms's models. |
I would *vastly* prefer a single transaction over all this stuff.
The problem is that :
a) The response time requirements are what they are,
for very good reasons, and I have to meet them.
b) Short and long transactions are working on different fields
(different "facets") of the same row. Unfortunatly (?), the whole
row is locked.
c) I need to think "out of the box", and that is not easy :-)
Right now, to avoid breaking up the long operation into short
transactions, I can think of :
- Remove the locking issue altogether, ie split the row fields
in two tables.
- Make the long transaction lock the list line rows for a short time,
perhaps building first a temporary table and doing afterwards a
mass update of the line rows. Of course, list header row would
be locked for the whole transaction duration.
- other ???
#14
| |||
| |||
|
|
joel garry wrote : hasta_l3 (AT) hotmail (DOT) com wrote: DA Morgan wrote : hasta_l3 (AT) hotmail (DOT) com wrote: DA Morgan wrote : hasta_l3 (AT) hotmail (DOT) com wrote: vc wrote : hasta_l3 (AT) hotmail (DOT) com wrote: [...] More concretely, the whole list structure is being changed, and the change should be atomic with regard to other operations that may also change the structure. However, the short transactions - which dont care about the structure - should not block. So only the transactions modifying the entire list would select for update the list header and therefore be executed serially, the smaller transactions that do not care about the list structure would proceed in the usual way without locking the header first. What is the problem ? Assume a (long) restructuring operation L modifies (a) some fields of the list header row in the master table, then (b) some fields of every list line row in the children table, including -say - row R. Assume that a short transaction S modifies (a) some (other) fields of the list line row R. If L is in a single long transaction, then it will block S if S happens to want to modify R after L did it. Look up PRAGMA AUTONOMOUS_TRANSACTION. It may help. Yes, Daniel. Autonomous transactions were actually the main question of my initial (very long :-) post. But here you do suggest to (1) have the main transaction lock the list header row, and (2) modify and commit each list line row in its own autonomous transaction, right ? It will work, I think, but I dont see how it is much different from my proposed scheme to (1) lock the list header row with a dbms_lock, and (2) modify and commit each list line row in the main transaction(s). One scheme may be better than the other, but I really cannot see which or why. Incidentally, reifying the list/line relationship into its own table might perhaps be an appealling design. The short transactions would modify the list line rows, and the long restructuring transaction would modify the relationship table rows. Unfortunatly, I cannot contemplate to make *that* kind of change right now... The difference is that you can commit in the autonomous transactions and not commit the main transaction. Something that is different from what you get using DBMS_LOCK. -- Yes. While the difference is unimportant (as of now) in my context, and using a lock minimizes the amount of changes, your suggestion as a "smell" of rigor and elegance that I like a lot... I will very seriously consider it. Thanks. Any known caveat on a massive use of autonomous transactions ? (8.1.7 and later...) --- Raoul You may have issues with latching and checkpointing. Yes. Also, it seems strange that you would not care about consistency of your line rows. See http://asktom.oracle.com/pls/ask/f?p...11504247549852 That's a *very* interesting thread, thanks. However, it will take me quite a time to digest all the implications, and right now I have trouble to see why it would be relevant here. Would you have the time to elaborate a bit ? Short and long transactions don't change - or even look at - the same row fields. It smells of other dbms's models. I know what you mean, Joel, and that is not the culprit here. I would *vastly* prefer a single transaction over all this stuff. The problem is that : a) The response time requirements are what they are, for very good reasons, and I have to meet them. b) Short and long transactions are working on different fields (different "facets") of the same row. Unfortunatly (?), the whole row is locked. c) I need to think "out of the box", and that is not easy :-) Right now, to avoid breaking up the long operation into short transactions, I can think of : - Remove the locking issue altogether, ie split the row fields in two tables. - Make the long transaction lock the list line rows for a short time, perhaps building first a temporary table and doing afterwards a mass update of the line rows. Of course, list header row would be locked for the whole transaction duration. - other ??? |
basic Oracle architecture and concepts. I still don't quite see this
as being anything more than overcomplicating something unnecessarily.
Oracle is not SQL Server. Why is it that any locking is required?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#15
| |||
| |||
|
|
I am led, by what you've written, to question whether you understand basic Oracle architecture and concepts. I still don't quite see this as being anything more than overcomplicating something unnecessarily. Oracle is not SQL Server. Why is it that any locking is required? |
To recap the context :
Quote:
|
Assume a (long) restructuring operation L modifies (a) some fields of the list header row in the master table, then (b) some fields of every list line row in the children table, including -say - row R. Assume that a short transaction S modifies (a) some other fields of the list line row R. If L is in a single long transaction, then it will block S if S happens to want to modify R after L did it. |
in many small transactions.
I'm just enumerating ways to replace the burst of thousands of
transactions by a single long one, if profiling shows it to be
necessary.
#16
| |||
| |||
|
|
I am led, by what you've written, to question whether you understand basic Oracle architecture and concepts. I still don't quite see this as being anything more than overcomplicating something unnecessarily. Oracle is not SQL Server. Why is it that any locking is required? I never used SQL Server, Daniel. To recap the context : Assume a (long) restructuring operation L modifies (a) some fields of the list header row in the master table, then (b) some fields of every list line row in the children table, including -say - row R. Assume that a short transaction S modifies (a) some other fields of the list line row R. If L is in a single long transaction, then it will block S if S happens to want to modify R after L did it. Right now, that problem is solved by breaking the long operation in many small transactions. I'm just enumerating ways to replace the burst of thousands of transactions by a single long one, if profiling shows it to be necessary. |
single transaction, and that would simplify some peripheral issues.
#17
| |||
| |||
|
|
I am led, by what you've written, to question whether you understand basic Oracle architecture and concepts. I still don't quite see this as being anything more than overcomplicating something unnecessarily. Oracle is not SQL Server. Why is it that any locking is required? I never used SQL Server, Daniel. To recap the context : Assume a (long) restructuring operation L modifies (a) some fields of the list header row in the master table, then (b) some fields of every list line row in the children table, including -say - row R. Assume that a short transaction S modifies (a) some other fields of the list line row R. If L is in a single long transaction, then it will block S if S happens to want to modify R after L did it. Right now, that problem is solved by breaking the long operation in many small transactions. I'm just enumerating ways to replace the burst of thousands of transactions by a single long one, if profiling shows it to be necessary. |
1. Why does it have to be two processes?
2. So what if multiple processes modify a record?
Again ... I don't see the underlying business case for what you are
doing. In what way will one transaction lock another unless you
explicitly lock it and why would you do that unnecessarily?
Why wouldn't SELECT FOR UPDATE WAIT 1 solve any issues you have?
Again ... I've read what you've written but I still don't see a
business case that indicates what you are doing must be done as you
are doing it.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#18
| |||
| |||
|
|
hasta_l3 (AT) hotmail (DOT) com wrote: I am led, by what you've written, to question whether you understand basic Oracle architecture and concepts. I still don't quite see this as being anything more than overcomplicating something unnecessarily. Oracle is not SQL Server. Why is it that any locking is required? I never used SQL Server, Daniel. To recap the context : Assume a (long) restructuring operation L modifies (a) some fields of the list header row in the master table, then (b) some fields of every list line row in the children table, including -say - row R. Assume that a short transaction S modifies (a) some other fields of the list line row R. If L is in a single long transaction, then it will block S if S happens to want to modify R after L did it. Right now, that problem is solved by breaking the long operation in many small transactions. I'm just enumerating ways to replace the burst of thousands of transactions by a single long one, if profiling shows it to be necessary. I've read all this ... several times ... I still don't see the point? 1. Why does it have to be two processes? 2. So what if multiple processes modify a record? Again ... I don't see the underlying business case for what you are doing. In what way will one transaction lock another unless you explicitly lock it and why would you do that unnecessarily? Why wouldn't SELECT FOR UPDATE WAIT 1 solve any issues you have? Again ... I've read what you've written but I still don't see a business case that indicates what you are doing must be done as you are doing it. |
I cannot be too specific, but roughly and simplifying:
a) There are things to do.
b) The things to do are performed by automatic devices.
c) At some point in time, an automatic device will
ask the application whether a given thing is really to
do.
d) The application needs to answer quickly yes. It also needs
to track that the job has been assigned, because :
e) Another device might (shortly) later on ask whether
that same thing is to do, and the application needs
to answer no (because the job has been assigned already)
f) All this information is of course stored in a ThingsToDo
table.
These are the short transactions.
Now, for user convenience, it is customery to group
the things to do in lists. Thus, the ThingsToDo table
is given additional fields, such as the ThingsToDo
list it belongs to, ordering information, etc... Note
that the process handling the automatic devices does
not need nor even know this information.
It may happen that a user reorganizes the list, moving lines
around, perhaps even to another list. The ordering information
must then be recomputed, according to a non trivial algorithm
that takes time.
This is the long transaction, and may - it does - happen
while the automatic devices are querying like crazies.
How would you solve this ?
#19
| |||
| |||
|
|
DA Morgan wrote : hasta_l3 (AT) hotmail (DOT) com wrote: I am led, by what you've written, to question whether you understand basic Oracle architecture and concepts. I still don't quite see this as being anything more than overcomplicating something unnecessarily. Oracle is not SQL Server. Why is it that any locking is required? I never used SQL Server, Daniel. To recap the context : Assume a (long) restructuring operation L modifies (a) some fields of the list header row in the master table, then (b) some fields of every list line row in the children table, including -say - row R. Assume that a short transaction S modifies (a) some other fields of the list line row R. If L is in a single long transaction, then it will block S if S happens to want to modify R after L did it. Right now, that problem is solved by breaking the long operation in many small transactions. I'm just enumerating ways to replace the burst of thousands of transactions by a single long one, if profiling shows it to be necessary. I've read all this ... several times ... I still don't see the point? 1. Why does it have to be two processes? 2. So what if multiple processes modify a record? Again ... I don't see the underlying business case for what you are doing. In what way will one transaction lock another unless you explicitly lock it and why would you do that unnecessarily? Why wouldn't SELECT FOR UPDATE WAIT 1 solve any issues you have? Again ... I've read what you've written but I still don't see a business case that indicates what you are doing must be done as you are doing it. Oh ? I cannot be too specific, but roughly and simplifying: a) There are things to do. b) The things to do are performed by automatic devices. c) At some point in time, an automatic device will ask the application whether a given thing is really to do. d) The application needs to answer quickly yes. It also needs to track that the job has been assigned, because : e) Another device might (shortly) later on ask whether that same thing is to do, and the application needs to answer no (because the job has been assigned already) f) All this information is of course stored in a ThingsToDo table. These are the short transactions. Now, for user convenience, it is customery to group the things to do in lists. Thus, the ThingsToDo table is given additional fields, such as the ThingsToDo list it belongs to, ordering information, etc... Note that the process handling the automatic devices does not need nor even know this information. It may happen that a user reorganizes the list, moving lines around, perhaps even to another list. The ordering information must then be recomputed, according to a non trivial algorithm that takes time. This is the long transaction, and may - it does - happen while the automatic devices are querying like crazies. |
somethingThatHasBeenDone has stillBeenDone, correct? So, then, I would
put that whole operation under one transaction so as to not affect the
thisJobIsDone answer.
The quick ones are the ones I'd be worried about. While a thingToDo is
being performed, another process can query to see if the same thingToDo
needs to be done, correct? Then, Oracle would answer yes to that
question, until the first one marks it as thisThingIsComplete. What I'd
expect is that you mark the thingToDo as thisThingIsBeingWorkedOn, so
the subsequent processes know to ask again until they get,
thisThingNeedsToBeDone or thisThingIsAlreadyDone.
--
Galen Boyer
#20
| |||
| |||
|
|
DA Morgan wrote : hasta_l3 (AT) hotmail (DOT) com wrote: I am led, by what you've written, to question whether you understand basic Oracle architecture and concepts. I still don't quite see this as being anything more than overcomplicating something unnecessarily. Oracle is not SQL Server. Why is it that any locking is required? I never used SQL Server, Daniel. To recap the context : Assume a (long) restructuring operation L modifies (a) some fields of the list header row in the master table, then (b) some fields of every list line row in the children table, including -say - row R. Assume that a short transaction S modifies (a) some other fields of the list line row R. If L is in a single long transaction, then it will block S if S happens to want to modify R after L did it. Right now, that problem is solved by breaking the long operation in many small transactions. I'm just enumerating ways to replace the burst of thousands of transactions by a single long one, if profiling shows it to be necessary. I've read all this ... several times ... I still don't see the point? 1. Why does it have to be two processes? 2. So what if multiple processes modify a record? Again ... I don't see the underlying business case for what you are doing. In what way will one transaction lock another unless you explicitly lock it and why would you do that unnecessarily? Why wouldn't SELECT FOR UPDATE WAIT 1 solve any issues you have? Again ... I've read what you've written but I still don't see a business case that indicates what you are doing must be done as you are doing it. Oh ? I cannot be too specific, but roughly and simplifying: a) There are things to do. b) The things to do are performed by automatic devices. c) At some point in time, an automatic device will ask the application whether a given thing is really to do. d) The application needs to answer quickly yes. It also needs to track that the job has been assigned, because : e) Another device might (shortly) later on ask whether that same thing is to do, and the application needs to answer no (because the job has been assigned already) f) All this information is of course stored in a ThingsToDo table. These are the short transactions. Now, for user convenience, it is customery to group the things to do in lists. Thus, the ThingsToDo table is given additional fields, such as the ThingsToDo list it belongs to, ordering information, etc... Note that the process handling the automatic devices does not need nor even know this information. It may happen that a user reorganizes the list, moving lines around, perhaps even to another list. The ordering information must then be recomputed, according to a non trivial algorithm that takes time. This is the long transaction, and may - it does - happen while the automatic devices are querying like crazies. How would you solve this ? |
have your processes ... whatever they are ... either use a
stored procedure with AQ or if your applications are J2EE,
communicate directly using JMS with a queue.
The queue can easily handle multiple inputs and assign priorities
complete with audit trail.
As I said ... I'm not convinced your approach, with locking,
is the best possible solution.
I'm not saying you are wrong ... but using locks should be one of the
last resorts ... not the first.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.