Re: computational model of transactions
From: Brian Selzer <brian_at_selzer-software.com>
Date: Sun, 06 Aug 2006 16:22:12 GMT
Message-ID: <UGoBg.5286$uo6.5204_at_newssvr13.news.prodigy.com>
>> "J M Davitt" <jdavitt_at_aeneas.net> wrote in message
>> news:LnaBg.63487$Eh1.25115_at_tornado.ohiordc.rr.com...
>>
>>>Brian Selzer wrote:
>>>
>>>>"J M Davitt" <jdavitt_at_aeneas.net> wrote in message
>>>>news:AG6Bg.53697$u11.51832_at_tornado.ohiordc.rr.com...
>>>>
>>>>
>>>>>Brian Selzer wrote:
>>>>>
>>>>>
>>>>>>"J M Davitt" <jdavitt_at_aeneas.net> wrote in message
>>>>>>news:3d2Bg.44572$vl5.12370_at_tornado.ohiordc.rr.com...
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Brian Selzer wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>"J M Davitt" <jdavitt_at_aeneas.net> wrote in message
>>>>>>>>news:QVSAg.63281$Eh1.62802_at_tornado.ohiordc.rr.com...
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>Brian Selzer wrote:
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>>"Brian Selzer" <brian_at_selzer-software.com> wrote in message
>>>>>>>>>>news:voHAg.4447$uo6.79_at_newssvr13.news.prodigy.com...
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>>"Erwin" <e.smout_at_myonline.be> wrote in message
>>>>>>>>>>>news:1154689817.830401.130180_at_75g2000cwc.googlegroups.com...
>>>>>>>
>>>>>>>[snip]
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>>>>>>The semantics of the update involve modification, not
>>>>>>>>>>>>>replacement
>>>>>>>>>>>>
>>>>>>>>>>>>You obviously see a difference between modification and
>>>>>>>>>>>>replacement. I
>>>>>>>>>>>>don't. So please explain.
>>>>>>>
>>>>>>>[snip]
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>>>I'm back. I agree that the updates need to be isolated, but I
>>>>>>>>>>disagree with the idea that the entire transaction needs to be
>>>>>>>>>>isolated or serialized. It is only necessary to obtain an
>>>>>>>>>>exclusive lock on the affected row at the time that the update to
>>>>>>>>>>the shared resource occurs, so it's possible to have several other
>>>>>>>>>>intervening transactions commit between the time that the
>>>>>>>>>>transaction starts and the time that the update starts. My point
>>>>>>>>>>is that it is not necessary to isolate the entire transaction,
>>>>>>>>>>only that portion from the start of the update until the commit.
>>>>>>>>>
>>>>>>>>>Are we to understand that "it's possible to have several other
>>>>>>>>>intervening transactions commit between the time that the
>>>>>>>>>transaction starts and the time that the update starts" means
>>>>>>>>>that you believe that at "the time the update starts" the value
>>>>>>>>>of whatever attribute is being changed isn't the same as it was
>>>>>>>>>when the transaction started?
>>>>>>>>>
>>>>>>>>
>>>>>>>>Yes. The nature of the update makes this possible. An update that
>>>>>>>>simply decreases inventory by 5 need not know the state of the
>>>>>>>>inventory at the time that the transaction started. If you issue,
>>>>>>>
>>>>>>>[snip]
>>>>>>>
>>>>>>>It would appear that you view "modification" and "replacement"
>>>>>>>as two different sorts of updates. To the database engines
>>>>>>>that are providing concurrency and correctness, those are
>>>>>>>indistinguishable, AFAIK.
>>>>>>>
>>>>>>
>>>>>>
>>>>>>Yes, I do. Modification depends on the current state of the
>>>>>>attribute; whereas replacement doesn't.
>>>>>>Database engines can provide concurrency and consistency, not
>>>>>>correctness, so in a replacement, the assumption is that the new value
>>>>>>is correct, and it's up to the application to correctly calculate the
>>>>>>new value; whereas with modification, the new value is calculated by
>>>>>>the database engine. This means that for replacement it's also up to
>>>>>>the application to request the correct level of concurrency, which can
>>>>>>be more restrictive for replacement than for modification.
>>>>>
>>>>>Well, you're right about the consistent v. correct part, at
>>>>>least in the sense that the system has no way to determine
>>>>>whether or not what it's being asked to store is true in
>>>>>the real world.
>>>>>
>>>>>But you seem to have completely avoided my point that
>>>>>"replacement" and "modification" are the same thing for the
>>>>>database. How do you think the system can tell the difference?
>>>>>
>>>>
>>>>
>>>>Here's an example of a replacement:
>>>>
>>>>UPDATE Inventory
>>>> SET QOH = 35
>>>> WHERE PartNo = '123'
>>>> AND Location = 'ABC'
>>>>
>>>>Here's an example of a modification
>>>>
>>>>UPDATE Inventory
>>>> SET QOH = QOH - 5
>>>> WHERE PartNo = '123'
>>>> AND Location = 'ABC'
>>>>
>>>>I think it's pretty clear which is which. I think that the system
>>>>should be able to detect the difference just as you can.
>>>
>>>Well, each of these specifies a value for a
>>>column in a row in a table. You think
>>>the expression denoting the value makes these
>>>different and that the system should be able
>>>to detect the difference. I'm still
>>>wondering, "How?"
>>>
>>
>>
>> A compiler can tell the difference between x = 10 and x = x + 5, why
>> can't a dbms?
>>
>>
>>>Exploring this a bit further: how many types
>>>of UPDATEs is the system supposed to detect?
>>>I mean, if we start by saying the number's
>>>greater than one, how do we know when they
>>>are all covered? Are you sure that
>>>modification and replacement are all there
>>>are?
>>>
>>
>>
>> The system should be able to detect whether or not the new value depends
>> on the previous value. The first UPDATE statement above does not, the
>> second does.
>>
>> As an aside, it is not really necessary that the system detect this: but
>> the developer must, because in a concurrent environment the difference in
>> the semantics of replacement and modification has ramifications that can
>> affect the appropriate choice of transaction isolation level.
>>>
>>
>>
>> I haven't given this much attention, but at first glance, no, I don't
>> think so.
>>
>>
>>>>>>>Also, your transactions seem like accounting system
>>>>>>>concepts rather than database concepts.
>>>>>>>
>>>>>>>While, in accounting, it seems to be possible to simply dump
>>>>>>>all the debits and credits in a hopper and allow them to be
>>>>>>>processed in random order, there comes a time when activity
>>>>>>>must be serialized. The bookkeeper that's cross-footing
>>>>>>>a page isn't going to be very happy with the clerk who wants
>>>>>>>to change an entry that's been footed in one column but not
>>>>>>>another.
>>>>>>
>>>>>>
>>
Received on Sun Aug 06 2006 - 18:22:12 CEST
Date: Sun, 06 Aug 2006 16:22:12 GMT
Message-ID: <UGoBg.5286$uo6.5204_at_newssvr13.news.prodigy.com>
"J M Davitt" <jdavitt_at_aeneas.net> wrote in message news:BZjBg.56521$u11.18671_at_tornado.ohiordc.rr.com...
> Brian Selzer wrote:
>> "J M Davitt" <jdavitt_at_aeneas.net> wrote in message
>> news:LnaBg.63487$Eh1.25115_at_tornado.ohiordc.rr.com...
>>
>>>Brian Selzer wrote:
>>>
>>>>"J M Davitt" <jdavitt_at_aeneas.net> wrote in message
>>>>news:AG6Bg.53697$u11.51832_at_tornado.ohiordc.rr.com...
>>>>
>>>>
>>>>>Brian Selzer wrote:
>>>>>
>>>>>
>>>>>>"J M Davitt" <jdavitt_at_aeneas.net> wrote in message
>>>>>>news:3d2Bg.44572$vl5.12370_at_tornado.ohiordc.rr.com...
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Brian Selzer wrote:
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>"J M Davitt" <jdavitt_at_aeneas.net> wrote in message
>>>>>>>>news:QVSAg.63281$Eh1.62802_at_tornado.ohiordc.rr.com...
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>Brian Selzer wrote:
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>>"Brian Selzer" <brian_at_selzer-software.com> wrote in message
>>>>>>>>>>news:voHAg.4447$uo6.79_at_newssvr13.news.prodigy.com...
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>>"Erwin" <e.smout_at_myonline.be> wrote in message
>>>>>>>>>>>news:1154689817.830401.130180_at_75g2000cwc.googlegroups.com...
>>>>>>>
>>>>>>>[snip]
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>>>>>>The semantics of the update involve modification, not
>>>>>>>>>>>>>replacement
>>>>>>>>>>>>
>>>>>>>>>>>>You obviously see a difference between modification and
>>>>>>>>>>>>replacement. I
>>>>>>>>>>>>don't. So please explain.
>>>>>>>
>>>>>>>[snip]
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>>>I'm back. I agree that the updates need to be isolated, but I
>>>>>>>>>>disagree with the idea that the entire transaction needs to be
>>>>>>>>>>isolated or serialized. It is only necessary to obtain an
>>>>>>>>>>exclusive lock on the affected row at the time that the update to
>>>>>>>>>>the shared resource occurs, so it's possible to have several other
>>>>>>>>>>intervening transactions commit between the time that the
>>>>>>>>>>transaction starts and the time that the update starts. My point
>>>>>>>>>>is that it is not necessary to isolate the entire transaction,
>>>>>>>>>>only that portion from the start of the update until the commit.
>>>>>>>>>
>>>>>>>>>Are we to understand that "it's possible to have several other
>>>>>>>>>intervening transactions commit between the time that the
>>>>>>>>>transaction starts and the time that the update starts" means
>>>>>>>>>that you believe that at "the time the update starts" the value
>>>>>>>>>of whatever attribute is being changed isn't the same as it was
>>>>>>>>>when the transaction started?
>>>>>>>>>
>>>>>>>>
>>>>>>>>Yes. The nature of the update makes this possible. An update that
>>>>>>>>simply decreases inventory by 5 need not know the state of the
>>>>>>>>inventory at the time that the transaction started. If you issue,
>>>>>>>
>>>>>>>[snip]
>>>>>>>
>>>>>>>It would appear that you view "modification" and "replacement"
>>>>>>>as two different sorts of updates. To the database engines
>>>>>>>that are providing concurrency and correctness, those are
>>>>>>>indistinguishable, AFAIK.
>>>>>>>
>>>>>>
>>>>>>
>>>>>>Yes, I do. Modification depends on the current state of the
>>>>>>attribute; whereas replacement doesn't.
>>>>>>Database engines can provide concurrency and consistency, not
>>>>>>correctness, so in a replacement, the assumption is that the new value
>>>>>>is correct, and it's up to the application to correctly calculate the
>>>>>>new value; whereas with modification, the new value is calculated by
>>>>>>the database engine. This means that for replacement it's also up to
>>>>>>the application to request the correct level of concurrency, which can
>>>>>>be more restrictive for replacement than for modification.
>>>>>
>>>>>Well, you're right about the consistent v. correct part, at
>>>>>least in the sense that the system has no way to determine
>>>>>whether or not what it's being asked to store is true in
>>>>>the real world.
>>>>>
>>>>>But you seem to have completely avoided my point that
>>>>>"replacement" and "modification" are the same thing for the
>>>>>database. How do you think the system can tell the difference?
>>>>>
>>>>
>>>>
>>>>Here's an example of a replacement:
>>>>
>>>>UPDATE Inventory
>>>> SET QOH = 35
>>>> WHERE PartNo = '123'
>>>> AND Location = 'ABC'
>>>>
>>>>Here's an example of a modification
>>>>
>>>>UPDATE Inventory
>>>> SET QOH = QOH - 5
>>>> WHERE PartNo = '123'
>>>> AND Location = 'ABC'
>>>>
>>>>I think it's pretty clear which is which. I think that the system
>>>>should be able to detect the difference just as you can.
>>>
>>>Well, each of these specifies a value for a
>>>column in a row in a table. You think
>>>the expression denoting the value makes these
>>>different and that the system should be able
>>>to detect the difference. I'm still
>>>wondering, "How?"
>>>
>>
>>
>> A compiler can tell the difference between x = 10 and x = x + 5, why
>> can't a dbms?
>>
>>
>>>Exploring this a bit further: how many types
>>>of UPDATEs is the system supposed to detect?
>>>I mean, if we start by saying the number's
>>>greater than one, how do we know when they
>>>are all covered? Are you sure that
>>>modification and replacement are all there
>>>are?
>>>
>>
>>
>> The system should be able to detect whether or not the new value depends
>> on the previous value. The first UPDATE statement above does not, the
>> second does.
>>
>> As an aside, it is not really necessary that the system detect this: but
>> the developer must, because in a concurrent environment the difference in
>> the semantics of replacement and modification has ramifications that can
>> affect the appropriate choice of transaction isolation level.
> > > Holy bat, Crapman! "As an aside" you've either abandoned > the points you made earlier in this thread or have been > using terminology inconsistently. When you started with >
So the discussion diverged. I think it was you who introduced the system into the argument, not me. My original argument is that there is a difference in semantics between replacement and modification, and that that difference can affect concurrency. Whether any implementation is involved or not doesn't change that, nor have any of the statements I've made been at odds with it. I noticed the divergence of the discussion in the last post, so I thought I'd try to bring it back on track with the aside. I certainly didn't expect to be tarred and feathered as a result.
> I disagree with the idea that the entire transaction > needs to be isolated or serialized. > > followed by > > it's possible to have several other intervening > transactions commit between the time that the > transaction starts and the time that the update > starts > > and the hint at different types of UPDATE > > in this case intervening transactions cannot > have occurred, otherwise the values set by the > intervening transactions will be overwritten. > Hence with replacement, the row to be updated > must be reserved for exclusive use (at least for > writing) throughout the transaction. > > which the system is supposed to detect > > I think it's pretty clear which is which. I > think that the system should be able to detect > the difference just as you can. > > by examining both sides of the assignment, I thought > you were trying to describe some computational model > of transactions in which the system, based on the > nature of the UPDATEs in the transaction, serialized > parts of "replacement" transactions and other > transactions that read "replaced" or "modified" data. > > I was about to move on to the question, "If the > system can detect that a 'replacement' UPDATE is > in the mix, why not require it to optimize the > workload and discard all the 'modify' UPDATEs?" >>>>differently in transaction context?
>>>While we're talking manipulations: what about
>>>INSERT and DELETE? Are there variants of
>>>those, too? Are those supposed to be handled
>>>
>>
>>
>> I haven't given this much attention, but at first glance, no, I don't
>> think so.
>>
>>
>>>>>>>Also, your transactions seem like accounting system
>>>>>>>concepts rather than database concepts.
>>>>>>>
>>>>>>>While, in accounting, it seems to be possible to simply dump
>>>>>>>all the debits and credits in a hopper and allow them to be
>>>>>>>processed in random order, there comes a time when activity
>>>>>>>must be serialized. The bookkeeper that's cross-footing
>>>>>>>a page isn't going to be very happy with the clerk who wants
>>>>>>>to change an entry that's been footed in one column but not
>>>>>>>another.
>>>>>>
>>>>>>
>>
Received on Sun Aug 06 2006 - 18:22:12 CEST