Loading ...
Sorry, an error occurred while loading the content.

DB Design - Web vs client server applications

Expand Messages
  • Jayanthi Venugopal
    Are there any distinct features that separate a Database Design that is used for web based applications vis-a-vis client server applications. Thanks Jayanthi
    Message 1 of 18 , Apr 11, 2003
    • 0 Attachment
      Are there any distinct features that separate a Database Design that
      is used for web based applications vis-a-vis client server
      applications.

      Thanks
      Jayanthi
    • Scott W. Ambler
      I would say not. The real issues are: 1. How is the DB going to be used. 2. What transactions, if any, will it need to support. 3. What s the throughput, ...
      Message 2 of 18 , Apr 11, 2003
      • 0 Attachment
        I would say not. The real issues are:
        1. How is the DB going to be used.
        2. What transactions, if any, will it need to support.
        3. What's the throughput, ...

        You'll likely have greater security and privacy issues to deal with,
        assuming you're deploying to the Internet.

        - Scott
        =====================================================
        Scott W. Ambler scott.ambler@...
        Senior Consultant, Ronin International, Inc.
        www.ronin-intl.com/company/scottAmbler.html

        The Elements of UML Style is out! www.ambysoft.com/elementsUMLStyle.html

        www.agiledata.org * www.agilemodeling.com * www.ambysoft.com *
        www.enterpriseunifiedprocess.info * www.modelingstyle.info *
        www.ronin-intl.com

        ----- Original Message -----
        From: "Jayanthi Venugopal" <j_venugopal@...>
        To: <agileDatabases@yahoogroups.com>
        Sent: Friday, April 11, 2003 6:54 AM
        Subject: [agileDatabases] DB Design - Web vs client server applications


        > Are there any distinct features that separate a Database Design that
        > is used for web based applications vis-a-vis client server
        > applications.
        >
        > Thanks
        > Jayanthi
        >
        >
        >
        > To unsubscribe from this group, send an email to:
        > agileDatabases-unsubscribe@yahoogroups.com
        >
        >
        >
        > Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
        >
        >
        >
      • Malte Finsterwalder
        ... Talking about Web-Applications... How long are your transactions? e.g.: You give the user the possibility to change some data. Do you start the transaction
        Message 3 of 18 , Apr 11, 2003
        • 0 Attachment
          Scott W. Ambler wrote:
          > I would say not. The real issues are:
          > 1. How is the DB going to be used.
          > 2. What transactions, if any, will it need to support.

          Talking about Web-Applications... How long are your transactions?
          e.g.: You give the user the possibility to change some data.
          Do you start the transaction when the user requests the data and commit
          it, after he submitted the change?
          Or do you start the transaction when you receive the submit and commit
          it right away?

          The first solution can lead to long lasting transactions. You also have
          to handle timeouts, when a user just closes the browser.
          The second solution can lead to overwriting intermediate changes: lost
          updates.

          What is the prefered method? Why? Or even better: What are your criteria
          for choosing either one method?

          Greetings,
          Malte
        • rquinn@web.de
          Yes, I would say there is one major difference. Many Web-Apps maintain SessionState in the database, often with an entity for current sessions and another,
          Message 4 of 18 , Apr 11, 2003
          • 0 Attachment
            Message
            Yes, I would say there is one major difference.
             
            Many Web-Apps maintain SessionState in the database, often with an entity for current sessions and another, dependent entity for session data. Some webapps get away with this, relying on the Web or App Server to manage session state data, I would suggest that the majority of enterprise web solutions need to manage session data persistence in a database, not least because of the complexities involved when dealing with session - server mapping on web farms.
             
            Client Server programs, with rich clients, do not succumb to the limitations of HTTP and so need do nothing more than declare program variables to store session state data.
             
            Cheers,
            --------------------------------
            richard quinn           t. 0761 20758412
            virtual-identity ag     f. 0761 20758401
            grünwälderstr. 10-14, 79098 freiburg
            http://www.virtual-identity.com/
             
             
            -----Original Message-----
            From: sentto-7758546-171-1050058487-rquinn=web.de@... [mailto:sentto-7758546-171-1050058487-rquinn=web.de@...] On Behalf Of Jayanthi Venugopal
            Sent: Freitag, 11. April 2003 12:55
            To: agileDatabases@yahoogroups.com
            Subject: [agileDatabases] DB Design - Web vs client server applications

            Are there any distinct features that separate a Database Design that
            is used for web based applications vis-a-vis client server
            applications.

            Thanks
            Jayanthi

            To unsubscribe from this group, send an email to:
            agileDatabases-unsubscribe@yahoogroups.com



            Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
          • Tim Andersen
            We are in the process of developing a web application to replace our existing client server application. These changes are forced because of restricitons of a
            Message 5 of 18 , Apr 11, 2003
            • 0 Attachment
              We are in the process of developing a web application
              to replace our existing client server application.
              These changes are forced because of restricitons of a
              web browser.

              One major database design change is the user
              persistance is kept in the database instead of an ini
              file on the client machine (this could have been done
              in the client server application, but with the web
              application we were forced to make this design change)

              another major change is the possibility of overwriting
              updates if more than one person tries to update the
              same record at the same time. to handle this, we are
              planning to add a timestamp to every table and compare
              it on the retrieve and update and if the timestamps
              are different it will not overwrite changes someone
              else made. if anyone else had a better way to handle
              this issue i would be extremely interested.
              thanks, tim.


              --- rquinn@... wrote:
              > Yes, I would say there is one major difference.
              >
              > Many Web-Apps maintain SessionState in the database,
              > often with an
              > entity for current sessions and another, dependent
              > entity for session
              > data. Some webapps get away with this, relying on
              > the Web or App Server
              > to manage session state data, I would suggest that
              > the majority of
              > enterprise web solutions need to manage session data
              > persistence in a
              > database, not least because of the complexities
              > involved when dealing
              > with session - server mapping on web farms.
              >
              > Client Server programs, with rich clients, do not
              > succumb to the
              > limitations of HTTP and so need do nothing more than
              > declare program
              > variables to store session state data.
              >
              > Cheers,
              > --------------------------------
              > richard quinn t. 0761 20758412
              > virtual-identity ag f. 0761 20758401
              > gr�nw�lderstr. 10-14, 79098 freiburg
              > http://www.virtual-identity.com/
              >
              >
              > -----Original Message-----
              > From:
              >
              sentto-7758546-171-1050058487-rquinn=web.de@...
              >
              [mailto:sentto-7758546-171-1050058487-rquinn=web.de@...
              > .com] On Behalf Of Jayanthi Venugopal
              > Sent: Freitag, 11. April 2003 12:55
              > To: agileDatabases@yahoogroups.com
              > Subject: [agileDatabases] DB Design - Web vs client
              > server applications
              >
              >
              >
              > Are there any distinct features that separate a
              > Database Design that
              > is used for web based applications vis-a-vis client
              > server
              > applications.
              >
              > Thanks
              > Jayanthi
              > To unsubscribe from this group, send an email to:
              > agileDatabases-unsubscribe@yahoogroups.com
              >
              >
              >
              > Your use of Yahoo! Groups is subject to the Yahoo!
              > Terms of Service
              > <http://docs.yahoo.com/info/terms/> .
              >
              >
              >


              __________________________________________________
              Do you Yahoo!?
              Yahoo! Tax Center - File online, calculators, forms, and more
              http://tax.yahoo.com
            • Rich
              Hello, Well, this is a little off topic, but traditionally one prevents concurrent updates by holding an update lock on the row / page / table holding the
              Message 6 of 18 , Apr 11, 2003
              • 0 Attachment
                Hello,

                Well, this is a little off topic, but traditionally one prevents
                concurrent updates by holding an update lock on the row / page / table
                holding the record.

                Then you would use some form of server side, row based cursor to access,
                hold locks and then update the record. No one else can update because of
                the lock. It kills your concurrency but you do get the required
                isolation...

                - Richard

                -----Urspr�ngliche Nachricht-----
                Von:
                sentto-7758546-175-1050077755-rquinn=web.de@...
                [mailto:sentto-7758546-175-1050077755-rquinn=web.de@...
                .com] Im Auftrag von Tim Andersen
                Gesendet: Freitag, 11. April 2003 18:16
                An: agileDatabases@yahoogroups.com
                Betreff: RE: [agileDatabases] DB Design - Web vs client server
                applications


                We are in the process of developing a web application
                to replace our existing client server application.
                These changes are forced because of restricitons of a
                web browser.

                One major database design change is the user
                persistance is kept in the database instead of an ini
                file on the client machine (this could have been done
                in the client server application, but with the web
                application we were forced to make this design change)

                another major change is the possibility of overwriting
                updates if more than one person tries to update the
                same record at the same time. to handle this, we are
                planning to add a timestamp to every table and compare
                it on the retrieve and update and if the timestamps
                are different it will not overwrite changes someone
                else made. if anyone else had a better way to handle
                this issue i would be extremely interested.
                thanks, tim.


                --- rquinn@... wrote:
                > Yes, I would say there is one major difference.
                >
                > Many Web-Apps maintain SessionState in the database,
                > often with an
                > entity for current sessions and another, dependent
                > entity for session
                > data. Some webapps get away with this, relying on
                > the Web or App Server
                > to manage session state data, I would suggest that
                > the majority of
                > enterprise web solutions need to manage session data persistence in a
                > database, not least because of the complexities
                > involved when dealing
                > with session - server mapping on web farms.
                >
                > Client Server programs, with rich clients, do not
                > succumb to the
                > limitations of HTTP and so need do nothing more than
                > declare program
                > variables to store session state data.
                >
                > Cheers,
                > --------------------------------
                > richard quinn t. 0761 20758412
                > virtual-identity ag f. 0761 20758401
                > gr�nw�lderstr. 10-14, 79098 freiburg http://www.virtual-identity.com/
                >
                >
                > -----Original Message-----
                > From:
                >
                sentto-7758546-171-1050058487-rquinn=web.de@...
                >
                [mailto:sentto-7758546-171-1050058487-rquinn=web.de@...
                > .com] On Behalf Of Jayanthi Venugopal
                > Sent: Freitag, 11. April 2003 12:55
                > To: agileDatabases@yahoogroups.com
                > Subject: [agileDatabases] DB Design - Web vs client
                > server applications
                >
                >
                >
                > Are there any distinct features that separate a
                > Database Design that
                > is used for web based applications vis-a-vis client
                > server
                > applications.
                >
                > Thanks
                > Jayanthi
                > To unsubscribe from this group, send an email to:
                > agileDatabases-unsubscribe@yahoogroups.com
                >
                >
                >
                > Your use of Yahoo! Groups is subject to the Yahoo!
                > Terms of Service
                > <http://docs.yahoo.com/info/terms/> .
                >
                >
                >


                __________________________________________________
                Do you Yahoo!?
                Yahoo! Tax Center - File online, calculators, forms, and more
                http://tax.yahoo.com


                To unsubscribe from this group, send an email to:
                agileDatabases-unsubscribe@yahoogroups.com



                Your use of Yahoo! Groups is subject to
                http://docs.yahoo.com/info/terms/
              • David Waters
                Hi This is known as pessimistic locking and can have some advantages over update locks, particularly for online transaction processing systems, either for thin
                Message 7 of 18 , Apr 11, 2003
                • 0 Attachment
                  Hi
                  This is known as pessimistic locking and can have some
                  advantages over update locks, particularly for online
                  transaction processing systems, either for thin client
                  or client server.

                  A record may be locked in an update lock and not be
                  updated for a large (ish) period of time. This is bad
                  for performance.

                  The disadvantage with pessimistic locking is that
                  every update must have a where clause, containing the
                  original time stamp (and a nice catch for the
                  resulting error).

                  I worked on a system in Australia, spread all over
                  Queensland, which is a state even bigger than Texas.
                  Some of the users worked at remote locations and can
                  close the office for an hour and have lunch and so on.
                  Pessimistic locking was the only option for us (other
                  than a very busy DBA).

                  We found it worked well...

                  David
                  --- Rich <rquinn@...> wrote:
                  > Hello,
                  >
                  > Well, this is a little off topic, but traditionally
                  > one prevents
                  > concurrent updates by holding an update lock on the
                  > row / page / table
                  > holding the record.
                  >
                  > Then you would use some form of server side, row
                  > based cursor to access,
                  > hold locks and then update the record. No one else
                  > can update because of
                  > the lock. It kills your concurrency but you do get
                  > the required
                  > isolation...
                  >
                  > - Richard
                  >
                  > -----Urspr�ngliche Nachricht-----
                  > Von:
                  >
                  sentto-7758546-175-1050077755-rquinn=web.de@...
                  >
                  [mailto:sentto-7758546-175-1050077755-rquinn=web.de@...
                  > .com] Im Auftrag von Tim Andersen
                  > Gesendet: Freitag, 11. April 2003 18:16
                  > An: agileDatabases@yahoogroups.com
                  > Betreff: RE: [agileDatabases] DB Design - Web vs
                  > client server
                  > applications
                  >
                  >
                  > We are in the process of developing a web
                  > application
                  > to replace our existing client server application.
                  > These changes are forced because of restricitons of
                  > a
                  > web browser.
                  >
                  > One major database design change is the user
                  > persistance is kept in the database instead of an
                  > ini
                  > file on the client machine (this could have been
                  > done
                  > in the client server application, but with the web
                  > application we were forced to make this design
                  > change)
                  >
                  > another major change is the possibility of
                  > overwriting
                  > updates if more than one person tries to update the
                  > same record at the same time. to handle this, we
                  > are
                  > planning to add a timestamp to every table and
                  > compare
                  > it on the retrieve and update and if the timestamps
                  > are different it will not overwrite changes someone
                  > else made. if anyone else had a better way to
                  > handle
                  > this issue i would be extremely interested.
                  > thanks, tim.
                  >
                  >
                  > --- rquinn@... wrote:
                  > > Yes, I would say there is one major difference.
                  > >
                  > > Many Web-Apps maintain SessionState in the
                  > database,
                  > > often with an
                  > > entity for current sessions and another, dependent
                  > > entity for session
                  > > data. Some webapps get away with this, relying on
                  > > the Web or App Server
                  > > to manage session state data, I would suggest that
                  > > the majority of
                  > > enterprise web solutions need to manage session
                  > data persistence in a
                  > > database, not least because of the complexities
                  > > involved when dealing
                  > > with session - server mapping on web farms.
                  > >
                  > > Client Server programs, with rich clients, do not
                  > > succumb to the
                  > > limitations of HTTP and so need do nothing more
                  > than
                  > > declare program
                  > > variables to store session state data.
                  > >
                  > > Cheers,
                  > > --------------------------------
                  > > richard quinn t. 0761 20758412
                  > > virtual-identity ag f. 0761 20758401
                  > > gr�nw�lderstr. 10-14, 79098 freiburg
                  > http://www.virtual-identity.com/
                  > >
                  > >
                  > > -----Original Message-----
                  > > From:
                  > >
                  >
                  sentto-7758546-171-1050058487-rquinn=web.de@...
                  > >
                  >
                  [mailto:sentto-7758546-171-1050058487-rquinn=web.de@...
                  > > .com] On Behalf Of Jayanthi Venugopal
                  > > Sent: Freitag, 11. April 2003 12:55
                  > > To: agileDatabases@yahoogroups.com
                  > > Subject: [agileDatabases] DB Design - Web vs
                  > client
                  > > server applications
                  > >
                  > >
                  > >
                  > > Are there any distinct features that separate a
                  > > Database Design that
                  > > is used for web based applications vis-a-vis
                  > client
                  > > server
                  > > applications.
                  > >
                  > > Thanks
                  > > Jayanthi
                  > > To unsubscribe from this group, send an email to:
                  > > agileDatabases-unsubscribe@yahoogroups.com
                  > >
                  > >
                  > >
                  > > Your use of Yahoo! Groups is subject to the Yahoo!
                  > > Terms of Service
                  > > <http://docs.yahoo.com/info/terms/> .
                  > >
                  > >
                  > >
                  >
                  >
                  > __________________________________________________
                  > Do you Yahoo!?
                  > Yahoo! Tax Center - File online, calculators, forms,
                  > and more
                  > http://tax.yahoo.com
                  >
                  >
                  > To unsubscribe from this group, send an email to:
                  > agileDatabases-unsubscribe@yahoogroups.com
                  >
                  >
                  >
                  > Your use of Yahoo! Groups is subject to
                  > http://docs.yahoo.com/info/terms/
                  >
                  >
                  >
                  >
                  > ------------------------ Yahoo! Groups Sponsor
                  >
                  > To unsubscribe from this group, send an email to:
                  > agileDatabases-unsubscribe@yahoogroups.com
                  >
                  >
                  >
                  > Your use of Yahoo! Groups is subject to
                  > http://docs.yahoo.com/info/terms/
                  >
                  >


                  __________________________________________________
                  Do you Yahoo!?
                  Yahoo! Tax Center - File online, calculators, forms, and more
                  http://tax.yahoo.com
                • Max Rydahl Andersen
                  You are talking about OPTIMISTIC locking instead of pessimistic locking, right ? Pessimistic locking = The database system prevents other users from accessing
                  Message 8 of 18 , Apr 11, 2003
                  • 0 Attachment
                    You are talking about OPTIMISTIC locking instead of pessimistic locking,
                    right ?

                    Pessimistic locking = The database system prevents other users from
                    accessing the data. The data can be locked forever - Bad Thing

                    Optimistic locking = Each record has a version/timestamp that is used as
                    you describe. The data is not locked, but you can only update if you have
                    read the latest record and thus know the latest version/timestamp. Users
                    cannot "lock down" the system - Good Ting ;)

                    /max

                    ----- Original Message -----
                    From: "David Waters" <davidmwaters@...>
                    To: <agileDatabases@yahoogroups.com>
                    Sent: Saturday, April 12, 2003 12:14 AM
                    Subject: Re: AW: [agileDatabases] DB Design - Web vs client server
                    applications


                    > Hi
                    > This is known as pessimistic locking and can have some
                    > advantages over update locks, particularly for online
                    > transaction processing systems, either for thin client
                    > or client server.
                    >
                    > A record may be locked in an update lock and not be
                    > updated for a large (ish) period of time. This is bad
                    > for performance.
                    >
                    > The disadvantage with pessimistic locking is that
                    > every update must have a where clause, containing the
                    > original time stamp (and a nice catch for the
                    > resulting error).
                    >
                    > I worked on a system in Australia, spread all over
                    > Queensland, which is a state even bigger than Texas.
                    > Some of the users worked at remote locations and can
                    > close the office for an hour and have lunch and so on.
                    > Pessimistic locking was the only option for us (other
                    > than a very busy DBA).
                    >
                    > We found it worked well...
                    >
                    > David
                    > --- Rich <rquinn@...> wrote:
                    > > Hello,
                    > >
                    > > Well, this is a little off topic, but traditionally
                    > > one prevents
                    > > concurrent updates by holding an update lock on the
                    > > row / page / table
                    > > holding the record.
                    > >
                    > > Then you would use some form of server side, row
                    > > based cursor to access,
                    > > hold locks and then update the record. No one else
                    > > can update because of
                    > > the lock. It kills your concurrency but you do get
                    > > the required
                    > > isolation...
                    > >
                    > > - Richard
                    > >
                    > > -----Ursprüngliche Nachricht-----
                    > > Von:
                    > >
                    > sentto-7758546-175-1050077755-rquinn=web.de@...
                    > >
                    > [mailto:sentto-7758546-175-1050077755-rquinn=web.de@...
                    > > .com] Im Auftrag von Tim Andersen
                    > > Gesendet: Freitag, 11. April 2003 18:16
                    > > An: agileDatabases@yahoogroups.com
                    > > Betreff: RE: [agileDatabases] DB Design - Web vs
                    > > client server
                    > > applications
                    > >
                    > >
                    > > We are in the process of developing a web
                    > > application
                    > > to replace our existing client server application.
                    > > These changes are forced because of restricitons of
                    > > a
                    > > web browser.
                    > >
                    > > One major database design change is the user
                    > > persistance is kept in the database instead of an
                    > > ini
                    > > file on the client machine (this could have been
                    > > done
                    > > in the client server application, but with the web
                    > > application we were forced to make this design
                    > > change)
                    > >
                    > > another major change is the possibility of
                    > > overwriting
                    > > updates if more than one person tries to update the
                    > > same record at the same time. to handle this, we
                    > > are
                    > > planning to add a timestamp to every table and
                    > > compare
                    > > it on the retrieve and update and if the timestamps
                    > > are different it will not overwrite changes someone
                    > > else made. if anyone else had a better way to
                    > > handle
                    > > this issue i would be extremely interested.
                    > > thanks, tim.
                    > >
                    > >
                    > > --- rquinn@... wrote:
                    > > > Yes, I would say there is one major difference.
                    > > >
                    > > > Many Web-Apps maintain SessionState in the
                    > > database,
                    > > > often with an
                    > > > entity for current sessions and another, dependent
                    > > > entity for session
                    > > > data. Some webapps get away with this, relying on
                    > > > the Web or App Server
                    > > > to manage session state data, I would suggest that
                    > > > the majority of
                    > > > enterprise web solutions need to manage session
                    > > data persistence in a
                    > > > database, not least because of the complexities
                    > > > involved when dealing
                    > > > with session - server mapping on web farms.
                    > > >
                    > > > Client Server programs, with rich clients, do not
                    > > > succumb to the
                    > > > limitations of HTTP and so need do nothing more
                    > > than
                    > > > declare program
                    > > > variables to store session state data.
                    > > >
                    > > > Cheers,
                    > > > --------------------------------
                    > > > richard quinn t. 0761 20758412
                    > > > virtual-identity ag f. 0761 20758401
                    > > > grünwälderstr. 10-14, 79098 freiburg
                    > > http://www.virtual-identity.com/
                    > > >
                    > > >
                    > > > -----Original Message-----
                    > > > From:
                    > > >
                    > >
                    > sentto-7758546-171-1050058487-rquinn=web.de@...
                    > > >
                    > >
                    > [mailto:sentto-7758546-171-1050058487-rquinn=web.de@...
                    > > > .com] On Behalf Of Jayanthi Venugopal
                    > > > Sent: Freitag, 11. April 2003 12:55
                    > > > To: agileDatabases@yahoogroups.com
                    > > > Subject: [agileDatabases] DB Design - Web vs
                    > > client
                    > > > server applications
                    > > >
                    > > >
                    > > >
                    > > > Are there any distinct features that separate a
                    > > > Database Design that
                    > > > is used for web based applications vis-a-vis
                    > > client
                    > > > server
                    > > > applications.
                    > > >
                    > > > Thanks
                    > > > Jayanthi
                    > > > To unsubscribe from this group, send an email to:
                    > > > agileDatabases-unsubscribe@yahoogroups.com
                    > > >
                    > > >
                    > > >
                    > > > Your use of Yahoo! Groups is subject to the Yahoo!
                    > > > Terms of Service
                    > > > <http://docs.yahoo.com/info/terms/> .
                    > > >
                    > > >
                    > > >
                    > >
                    > >
                    > > __________________________________________________
                    > > Do you Yahoo!?
                    > > Yahoo! Tax Center - File online, calculators, forms,
                    > > and more
                    > > http://tax.yahoo.com
                    > >
                    > >
                    > > To unsubscribe from this group, send an email to:
                    > > agileDatabases-unsubscribe@yahoogroups.com
                    > >
                    > >
                    > >
                    > > Your use of Yahoo! Groups is subject to
                    > > http://docs.yahoo.com/info/terms/
                    > >
                    > >
                    > >
                    > >
                    > > ------------------------ Yahoo! Groups Sponsor
                    > >
                    > > To unsubscribe from this group, send an email to:
                    > > agileDatabases-unsubscribe@yahoogroups.com
                    > >
                    > >
                    > >
                    > > Your use of Yahoo! Groups is subject to
                    > > http://docs.yahoo.com/info/terms/
                    > >
                    > >
                    >
                    >
                    > __________________________________________________
                    > Do you Yahoo!?
                    > Yahoo! Tax Center - File online, calculators, forms, and more
                    > http://tax.yahoo.com
                    >
                    >
                    > To unsubscribe from this group, send an email to:
                    > agileDatabases-unsubscribe@yahoogroups.com
                    >
                    >
                    >
                    > Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
                    >
                    >
                    >
                  • Bryan Hughes
                    Also, it is import to be aware that a PESSIMISTIC locking approach will create key-value locks on the index pages. While this is to prevent phantom rows, it
                    Message 9 of 18 , Apr 11, 2003
                    • 0 Attachment
                      Also, it is import to be aware that a PESSIMISTIC locking approach
                      will create key-value locks on the index pages. While this is to
                      prevent phantom rows, it results in a lock cascade which will bring
                      the engine to a halt in short order. Aside from this, pessimistic
                      locking scenarios are also ripe for a multitude of dead lock
                      conditions.

                      ~bryan

                      System and Database Architect
                      SpokeSoftware

                      <max@e...> wrote:
                      > You are talking about OPTIMISTIC locking instead of pessimistic
                      locking,
                      > right ?
                      >
                      > Pessimistic locking = The database system prevents other users from
                      > accessing the data. The data can be locked forever - Bad Thing
                      >
                      > Optimistic locking = Each record has a version/timestamp that is
                      used as
                      > you describe. The data is not locked, but you can only update if
                      you have
                      > read the latest record and thus know the latest version/timestamp.
                      Users
                      > cannot "lock down" the system - Good Ting ;)
                      >
                      > /max
                      >
                      > ----- Original Message -----
                      > From: "David Waters" <davidmwaters@y...>
                      > To: <agileDatabases@yahoogroups.com>
                      > Sent: Saturday, April 12, 2003 12:14 AM
                      > Subject: Re: AW: [agileDatabases] DB Design - Web vs client server
                      > applications
                      >
                      >
                      > > Hi
                      > > This is known as pessimistic locking and can have some
                      > > advantages over update locks, particularly for online
                      > > transaction processing systems, either for thin client
                      > > or client server.
                      > >
                      > > A record may be locked in an update lock and not be
                      > > updated for a large (ish) period of time. This is bad
                      > > for performance.
                      > >
                      > > The disadvantage with pessimistic locking is that
                      > > every update must have a where clause, containing the
                      > > original time stamp (and a nice catch for the
                      > > resulting error).
                      > >
                      > > I worked on a system in Australia, spread all over
                      > > Queensland, which is a state even bigger than Texas.
                      > > Some of the users worked at remote locations and can
                      > > close the office for an hour and have lunch and so on.
                      > > Pessimistic locking was the only option for us (other
                      > > than a very busy DBA).
                      > >
                      > > We found it worked well...
                      > >
                      > > David
                      > > --- Rich <rquinn@w...> wrote:
                      > > > Hello,
                      > > >
                      > > > Well, this is a little off topic, but traditionally
                      > > > one prevents
                      > > > concurrent updates by holding an update lock on the
                      > > > row / page / table
                      > > > holding the record.
                      > > >
                      > > > Then you would use some form of server side, row
                      > > > based cursor to access,
                      > > > hold locks and then update the record. No one else
                      > > > can update because of
                      > > > the lock. It kills your concurrency but you do get
                      > > > the required
                      > > > isolation...
                      > > >
                      > > > - Richard
                      > > >
                      > > > -----Ursprüngliche Nachricht-----
                      > > > Von:
                      > > >
                      > > sentto-7758546-175-1050077755-rquinn=web.de@r...
                      > > >
                      > > [mailto:sentto-7758546-175-1050077755-rquinn=web.de@r...
                      > > > .com] Im Auftrag von Tim Andersen
                      > > > Gesendet: Freitag, 11. April 2003 18:16
                      > > > An: agileDatabases@yahoogroups.com
                      > > > Betreff: RE: [agileDatabases] DB Design - Web vs
                      > > > client server
                      > > > applications
                      > > >
                      > > >
                      > > > We are in the process of developing a web
                      > > > application
                      > > > to replace our existing client server application.
                      > > > These changes are forced because of restricitons of
                      > > > a
                      > > > web browser.
                      > > >
                      > > > One major database design change is the user
                      > > > persistance is kept in the database instead of an
                      > > > ini
                      > > > file on the client machine (this could have been
                      > > > done
                      > > > in the client server application, but with the web
                      > > > application we were forced to make this design
                      > > > change)
                      > > >
                      > > > another major change is the possibility of
                      > > > overwriting
                      > > > updates if more than one person tries to update the
                      > > > same record at the same time. to handle this, we
                      > > > are
                      > > > planning to add a timestamp to every table and
                      > > > compare
                      > > > it on the retrieve and update and if the timestamps
                      > > > are different it will not overwrite changes someone
                      > > > else made. if anyone else had a better way to
                      > > > handle
                      > > > this issue i would be extremely interested.
                      > > > thanks, tim.
                      > > >
                      > > >
                      > > > --- rquinn@w... wrote:
                      > > > > Yes, I would say there is one major difference.
                      > > > >
                      > > > > Many Web-Apps maintain SessionState in the
                      > > > database,
                      > > > > often with an
                      > > > > entity for current sessions and another, dependent
                      > > > > entity for session
                      > > > > data. Some webapps get away with this, relying on
                      > > > > the Web or App Server
                      > > > > to manage session state data, I would suggest that
                      > > > > the majority of
                      > > > > enterprise web solutions need to manage session
                      > > > data persistence in a
                      > > > > database, not least because of the complexities
                      > > > > involved when dealing
                      > > > > with session - server mapping on web farms.
                      > > > >
                      > > > > Client Server programs, with rich clients, do not
                      > > > > succumb to the
                      > > > > limitations of HTTP and so need do nothing more
                      > > > than
                      > > > > declare program
                      > > > > variables to store session state data.
                      > > > >
                      > > > > Cheers,
                      > > > > --------------------------------
                      > > > > richard quinn t. 0761 20758412
                      > > > > virtual-identity ag f. 0761 20758401
                      > > > > grünwälderstr. 10-14, 79098 freiburg
                      > > > http://www.virtual-identity.com/
                      > > > >
                      > > > >
                      > > > > -----Original Message-----
                      > > > > From:
                      > > > >
                      > > >
                      > > sentto-7758546-171-1050058487-rquinn=web.de@r...
                      > > > >
                      > > >
                      > > [mailto:sentto-7758546-171-1050058487-rquinn=web.de@r...
                      > > > > .com] On Behalf Of Jayanthi Venugopal
                      > > > > Sent: Freitag, 11. April 2003 12:55
                      > > > > To: agileDatabases@yahoogroups.com
                      > > > > Subject: [agileDatabases] DB Design - Web vs
                      > > > client
                      > > > > server applications
                      > > > >
                      > > > >
                      > > > >
                      > > > > Are there any distinct features that separate a
                      > > > > Database Design that
                      > > > > is used for web based applications vis-a-vis
                      > > > client
                      > > > > server
                      > > > > applications.
                      > > > >
                      > > > > Thanks
                      > > > > Jayanthi
                      > > > > To unsubscribe from this group, send an email to:
                      > > > > agileDatabases-unsubscribe@yahoogroups.com
                      > > > >
                      > > > >
                      > > > >
                      > > > > Your use of Yahoo! Groups is subject to the Yahoo!
                      > > > > Terms of Service
                      > > > > <http://docs.yahoo.com/info/terms/> .
                      > > > >
                      > > > >
                      > > > >
                      > > >
                      > > >
                      > > > __________________________________________________
                      > > > Do you Yahoo!?
                      > > > Yahoo! Tax Center - File online, calculators, forms,
                      > > > and more
                      > > > http://tax.yahoo.com
                      > > >
                      > > >
                      > > > To unsubscribe from this group, send an email to:
                      > > > agileDatabases-unsubscribe@yahoogroups.com
                      > > >
                      > > >
                      > > >
                      > > > Your use of Yahoo! Groups is subject to
                      > > > http://docs.yahoo.com/info/terms/
                      > > >
                      > > >
                      > > >
                      > > >
                      > > > ------------------------ Yahoo! Groups Sponsor
                      > > >
                      > > > To unsubscribe from this group, send an email to:
                      > > > agileDatabases-unsubscribe@yahoogroups.com
                      > > >
                      > > >
                      > > >
                      > > > Your use of Yahoo! Groups is subject to
                      > > > http://docs.yahoo.com/info/terms/
                      > > >
                      > > >
                      > >
                      > >
                      > > __________________________________________________
                      > > Do you Yahoo!?
                      > > Yahoo! Tax Center - File online, calculators, forms, and more
                      > > http://tax.yahoo.com
                      > >
                      > >
                      > > To unsubscribe from this group, send an email to:
                      > > agileDatabases-unsubscribe@yahoogroups.com
                      > >
                      > >
                      > >
                      > > Your use of Yahoo! Groups is subject to
                      http://docs.yahoo.com/info/terms/
                      > >
                      > >
                      > >
                    • David Waters
                      Oops, too early in the morning. I most definitely meant Optimistic locking... ... sentto-7758546-175-1050077755-rquinn=web.de@returns.groups.yahoo.com ...
                      Message 10 of 18 , Apr 11, 2003
                      • 0 Attachment
                        Oops, too early in the morning. I most definitely
                        meant Optimistic locking...
                        --- Max Rydahl Andersen <max@...> wrote:
                        > You are talking about OPTIMISTIC locking instead of
                        > pessimistic locking,
                        > right ?
                        >
                        > Pessimistic locking = The database system prevents
                        > other users from
                        > accessing the data. The data can be locked forever -
                        > Bad Thing
                        >
                        > Optimistic locking = Each record has a
                        > version/timestamp that is used as
                        > you describe. The data is not locked, but you can
                        > only update if you have
                        > read the latest record and thus know the latest
                        > version/timestamp. Users
                        > cannot "lock down" the system - Good Ting ;)
                        >
                        > /max
                        >
                        > ----- Original Message -----
                        > From: "David Waters" <davidmwaters@...>
                        > To: <agileDatabases@yahoogroups.com>
                        > Sent: Saturday, April 12, 2003 12:14 AM
                        > Subject: Re: AW: [agileDatabases] DB Design - Web vs
                        > client server
                        > applications
                        >
                        >
                        > > Hi
                        > > This is known as pessimistic locking and can have
                        > some
                        > > advantages over update locks, particularly for
                        > online
                        > > transaction processing systems, either for thin
                        > client
                        > > or client server.
                        > >
                        > > A record may be locked in an update lock and not
                        > be
                        > > updated for a large (ish) period of time. This is
                        > bad
                        > > for performance.
                        > >
                        > > The disadvantage with pessimistic locking is that
                        > > every update must have a where clause, containing
                        > the
                        > > original time stamp (and a nice catch for the
                        > > resulting error).
                        > >
                        > > I worked on a system in Australia, spread all over
                        > > Queensland, which is a state even bigger than
                        > Texas.
                        > > Some of the users worked at remote locations and
                        > can
                        > > close the office for an hour and have lunch and so
                        > on.
                        > > Pessimistic locking was the only option for us
                        > (other
                        > > than a very busy DBA).
                        > >
                        > > We found it worked well...
                        > >
                        > > David
                        > > --- Rich <rquinn@...> wrote:
                        > > > Hello,
                        > > >
                        > > > Well, this is a little off topic, but
                        > traditionally
                        > > > one prevents
                        > > > concurrent updates by holding an update lock on
                        > the
                        > > > row / page / table
                        > > > holding the record.
                        > > >
                        > > > Then you would use some form of server side, row
                        > > > based cursor to access,
                        > > > hold locks and then update the record. No one
                        > else
                        > > > can update because of
                        > > > the lock. It kills your concurrency but you do
                        > get
                        > > > the required
                        > > > isolation...
                        > > >
                        > > > - Richard
                        > > >
                        > > > -----Urspr�ngliche Nachricht-----
                        > > > Von:
                        > > >
                        > >
                        >
                        sentto-7758546-175-1050077755-rquinn=web.de@...
                        > > >
                        > >
                        >
                        [mailto:sentto-7758546-175-1050077755-rquinn=web.de@...
                        > > > .com] Im Auftrag von Tim Andersen
                        > > > Gesendet: Freitag, 11. April 2003 18:16
                        > > > An: agileDatabases@yahoogroups.com
                        > > > Betreff: RE: [agileDatabases] DB Design - Web vs
                        > > > client server
                        > > > applications
                        > > >
                        > > >
                        > > > We are in the process of developing a web
                        > > > application
                        > > > to replace our existing client server
                        > application.
                        > > > These changes are forced because of restricitons
                        > of
                        > > > a
                        > > > web browser.
                        > > >
                        > > > One major database design change is the user
                        > > > persistance is kept in the database instead of
                        > an
                        > > > ini
                        > > > file on the client machine (this could have been
                        > > > done
                        > > > in the client server application, but with the
                        > web
                        > > > application we were forced to make this design
                        > > > change)
                        > > >
                        > > > another major change is the possibility of
                        > > > overwriting
                        > > > updates if more than one person tries to update
                        > the
                        > > > same record at the same time. to handle this,
                        > we
                        > > > are
                        > > > planning to add a timestamp to every table and
                        > > > compare
                        > > > it on the retrieve and update and if the
                        > timestamps
                        > > > are different it will not overwrite changes
                        > someone
                        > > > else made. if anyone else had a better way to
                        > > > handle
                        > > > this issue i would be extremely interested.
                        > > > thanks, tim.
                        > > >
                        > > >
                        > > > --- rquinn@... wrote:
                        > > > > Yes, I would say there is one major
                        > difference.
                        > > > >
                        > > > > Many Web-Apps maintain SessionState in the
                        > > > database,
                        > > > > often with an
                        > > > > entity for current sessions and another,
                        > dependent
                        > > > > entity for session
                        > > > > data. Some webapps get away with this, relying
                        > on
                        > > > > the Web or App Server
                        > > > > to manage session state data, I would suggest
                        > that
                        > > > > the majority of
                        > > > > enterprise web solutions need to manage
                        > session
                        > > > data persistence in a
                        > > > > database, not least because of the
                        > complexities
                        > > > > involved when dealing
                        > > > > with session - server mapping on web farms.
                        > > > >
                        > > > > Client Server programs, with rich clients, do
                        > not
                        > > > > succumb to the
                        > > > > limitations of HTTP and so need do nothing
                        > more
                        > > > than
                        > > > > declare program
                        > > > > variables to store session state data.
                        > > > >
                        > > > > Cheers,
                        > > > > --------------------------------
                        > > > > richard quinn t. 0761 20758412
                        > > > > virtual-identity ag f. 0761 20758401
                        > > > > gr�nw�lderstr. 10-14, 79098 freiburg
                        > > > http://www.virtual-identity.com/
                        > > > >
                        > > > >
                        > > > > -----Original Message-----
                        > > > > From:
                        > > > >
                        > > >
                        > >
                        >
                        sentto-7758546-171-1050058487-rquinn=web.de@...
                        > > > >
                        > > >
                        > >
                        >
                        [mailto:sentto-7758546-171-1050058487-rquinn=web.de@...
                        > > > > .com] On Behalf Of Jayanthi Venugopal
                        > > > > Sent: Freitag, 11. April 2003 12:55
                        > > > > To: agileDatabases@yahoogroups.com
                        > > > > Subject: [agileDatabases] DB Design - Web vs
                        > > > client
                        > > > > server applications
                        > > > >
                        > > > >
                        > > > >
                        >
                        === message truncated ===


                        __________________________________________________
                        Do you Yahoo!?
                        Yahoo! Tax Center - File online, calculators, forms, and more
                        http://tax.yahoo.com
                      • Scott W. Ambler
                        ... From: Max Rydahl Andersen To: Sent: Friday, April 11, 2003 6:26 PM Subject: Re: AW: [agileDatabases] DB
                        Message 11 of 18 , Apr 12, 2003
                        • 0 Attachment
                          ----- Original Message -----
                          From: "Max Rydahl Andersen" <max@...>
                          To: <agileDatabases@yahoogroups.com>
                          Sent: Friday, April 11, 2003 6:26 PM
                          Subject: Re: AW: [agileDatabases] DB Design - Web vs client server
                          applications


                          > You are talking about OPTIMISTIC locking instead of pessimistic locking,
                          > right ?
                          >
                          > Pessimistic locking = The database system prevents other users from
                          > accessing the data. The data can be locked forever - Bad Thing

                          Actually, pessimistic locking, even though I avoid it, can be a good thing
                          in some situations.

                          >
                          > Optimistic locking = Each record has a version/timestamp that is used as
                          > you describe. The data is not locked, but you can only update if you have
                          > read the latest record and thus know the latest version/timestamp. Users
                          > cannot "lock down" the system - Good Ting ;)

                          There are several strategies for implementing optimistic locking.
                          Timestamps are an example of a "marking" strategy where you mark a record
                          with an identifying tag (timestamp, incremental number, user ID, GUID, ...).
                          You can also take a non-marking strategy where an original copy of the
                          record is kept in memory and then compared later.

                          Trade-offs of pessimistic, optimistic, and no locking are covered at
                          www.agiledata.org/essays/concurrencyControl.html#Collisions.

                          - Scott

                          >
                          > /max
                          >
                          > ----- Original Message -----
                          > From: "David Waters" <davidmwaters@...>
                          > To: <agileDatabases@yahoogroups.com>
                          > Sent: Saturday, April 12, 2003 12:14 AM
                          > Subject: Re: AW: [agileDatabases] DB Design - Web vs client server
                          > applications
                          >
                          >
                          > > Hi
                          > > This is known as pessimistic locking and can have some
                          > > advantages over update locks, particularly for online
                          > > transaction processing systems, either for thin client
                          > > or client server.
                          > >
                          > > A record may be locked in an update lock and not be
                          > > updated for a large (ish) period of time. This is bad
                          > > for performance.
                          > >
                          > > The disadvantage with pessimistic locking is that
                          > > every update must have a where clause, containing the
                          > > original time stamp (and a nice catch for the
                          > > resulting error).
                          > >
                          > > I worked on a system in Australia, spread all over
                          > > Queensland, which is a state even bigger than Texas.
                          > > Some of the users worked at remote locations and can
                          > > close the office for an hour and have lunch and so on.
                          > > Pessimistic locking was the only option for us (other
                          > > than a very busy DBA).
                          > >
                          > > We found it worked well...
                          > >
                          > > David
                          > > --- Rich <rquinn@...> wrote:
                          > > > Hello,
                          > > >
                          > > > Well, this is a little off topic, but traditionally
                          > > > one prevents
                          > > > concurrent updates by holding an update lock on the
                          > > > row / page / table
                          > > > holding the record.
                          > > >
                          > > > Then you would use some form of server side, row
                          > > > based cursor to access,
                          > > > hold locks and then update the record. No one else
                          > > > can update because of
                          > > > the lock. It kills your concurrency but you do get
                          > > > the required
                          > > > isolation...
                          > > >
                          > > > - Richard
                          > > >
                          > > > -----Ursprüngliche Nachricht-----
                          > > > Von:
                          > > >
                          > > sentto-7758546-175-1050077755-rquinn=web.de@...
                          > > >
                          > > [mailto:sentto-7758546-175-1050077755-rquinn=web.de@...
                          > > > .com] Im Auftrag von Tim Andersen
                          > > > Gesendet: Freitag, 11. April 2003 18:16
                          > > > An: agileDatabases@yahoogroups.com
                          > > > Betreff: RE: [agileDatabases] DB Design - Web vs
                          > > > client server
                          > > > applications
                          > > >
                          > > >
                          > > > We are in the process of developing a web
                          > > > application
                          > > > to replace our existing client server application.
                          > > > These changes are forced because of restricitons of
                          > > > a
                          > > > web browser.
                          > > >
                          > > > One major database design change is the user
                          > > > persistance is kept in the database instead of an
                          > > > ini
                          > > > file on the client machine (this could have been
                          > > > done
                          > > > in the client server application, but with the web
                          > > > application we were forced to make this design
                          > > > change)
                          > > >
                          > > > another major change is the possibility of
                          > > > overwriting
                          > > > updates if more than one person tries to update the
                          > > > same record at the same time. to handle this, we
                          > > > are
                          > > > planning to add a timestamp to every table and
                          > > > compare
                          > > > it on the retrieve and update and if the timestamps
                          > > > are different it will not overwrite changes someone
                          > > > else made. if anyone else had a better way to
                          > > > handle
                          > > > this issue i would be extremely interested.
                          > > > thanks, tim.
                          > > >
                          > > >
                          > > > --- rquinn@... wrote:
                          > > > > Yes, I would say there is one major difference.
                          > > > >
                          > > > > Many Web-Apps maintain SessionState in the
                          > > > database,
                          > > > > often with an
                          > > > > entity for current sessions and another, dependent
                          > > > > entity for session
                          > > > > data. Some webapps get away with this, relying on
                          > > > > the Web or App Server
                          > > > > to manage session state data, I would suggest that
                          > > > > the majority of
                          > > > > enterprise web solutions need to manage session
                          > > > data persistence in a
                          > > > > database, not least because of the complexities
                          > > > > involved when dealing
                          > > > > with session - server mapping on web farms.
                          > > > >
                          > > > > Client Server programs, with rich clients, do not
                          > > > > succumb to the
                          > > > > limitations of HTTP and so need do nothing more
                          > > > than
                          > > > > declare program
                          > > > > variables to store session state data.
                          > > > >
                          > > > > Cheers,
                          > > > > --------------------------------
                          > > > > richard quinn t. 0761 20758412
                          > > > > virtual-identity ag f. 0761 20758401
                          > > > > grünwälderstr. 10-14, 79098 freiburg
                          > > > http://www.virtual-identity.com/
                          > > > >
                          > > > >
                          > > > > -----Original Message-----
                          > > > > From:
                          > > > >
                          > > >
                          > > sentto-7758546-171-1050058487-rquinn=web.de@...
                          > > > >
                          > > >
                          > > [mailto:sentto-7758546-171-1050058487-rquinn=web.de@...
                          > > > > .com] On Behalf Of Jayanthi Venugopal
                          > > > > Sent: Freitag, 11. April 2003 12:55
                          > > > > To: agileDatabases@yahoogroups.com
                          > > > > Subject: [agileDatabases] DB Design - Web vs
                          > > > client
                          > > > > server applications
                          > > > >
                          > > > >
                          > > > >
                          > > > > Are there any distinct features that separate a
                          > > > > Database Design that
                          > > > > is used for web based applications vis-a-vis
                          > > > client
                          > > > > server
                          > > > > applications.
                          > > > >
                          > > > > Thanks
                          > > > > Jayanthi
                          > > > > To unsubscribe from this group, send an email to:
                          > > > > agileDatabases-unsubscribe@yahoogroups.com
                          > > > >
                          > > > >
                          > > > >
                          > > > > Your use of Yahoo! Groups is subject to the Yahoo!
                          > > > > Terms of Service
                          > > > > <http://docs.yahoo.com/info/terms/> .
                          > > > >
                          > > > >
                          > > > >
                          > > >
                          > > >
                          > > > __________________________________________________
                          > > > Do you Yahoo!?
                          > > > Yahoo! Tax Center - File online, calculators, forms,
                          > > > and more
                          > > > http://tax.yahoo.com
                          > > >
                          > > >
                          > > > To unsubscribe from this group, send an email to:
                          > > > agileDatabases-unsubscribe@yahoogroups.com
                          > > >
                          > > >
                          > > >
                          > > > Your use of Yahoo! Groups is subject to
                          > > > http://docs.yahoo.com/info/terms/
                          > > >
                          > > >
                          > > >
                          > > >
                          > > > ------------------------ Yahoo! Groups Sponsor
                          > > >
                          > > > To unsubscribe from this group, send an email to:
                          > > > agileDatabases-unsubscribe@yahoogroups.com
                          > > >
                          > > >
                          > > >
                          > > > Your use of Yahoo! Groups is subject to
                          > > > http://docs.yahoo.com/info/terms/
                          > > >
                          > > >
                          > >
                          > >
                          > > __________________________________________________
                          > > Do you Yahoo!?
                          > > Yahoo! Tax Center - File online, calculators, forms, and more
                          > > http://tax.yahoo.com
                          > >
                          > >
                          > > To unsubscribe from this group, send an email to:
                          > > agileDatabases-unsubscribe@yahoogroups.com
                          > >
                          > >
                          > >
                          > > Your use of Yahoo! Groups is subject to
                          http://docs.yahoo.com/info/terms/
                          > >
                          > >
                          > >
                          >
                          >
                          >
                          > To unsubscribe from this group, send an email to:
                          > agileDatabases-unsubscribe@yahoogroups.com
                          >
                          >
                          >
                          > Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
                          >
                          >
                          >
                        • Dean Franks
                          Another approach is to create a disconnected cursor object, populate the object from the original row (then close the cursor). The disconnected cursor is then
                          Message 12 of 18 , Apr 12, 2003
                          • 0 Attachment
                            Another approach is to create a disconnected cursor object, populate the
                            object from the original row (then close the cursor).

                            The disconnected cursor is then stored in the session state for that
                            connection. When the eventual update and post is done, the cursor builds an
                            update statement with only the modified fields in the <field>=<value>
                            portion of the statement. The where clause lists all the fields and their
                            original values; similar to:

                            update <table> set <field1>=<newvalue1>, <field2>=<newvalue2>
                            where <pkfield>=<pkvalue> and <field1>=<oldvalue1> and <field2>=<oldvalue>

                            When the updated statement is executed, the modified rows count is checked.
                            If the rowcount <> 1 then an error is returned to the user. Be aware that
                            if any of the <oldvalue> values are null, you'll have to use "is null"
                            instead of = NULL for most databases (you can get away with it on a few).

                            It has to be acceptable to allow concurrent updates to the same row that
                            modify different fields if you want to use this technique (a specific goal
                            in my app).

                            If you can't live with the concurrent updates to the same row, use the
                            timestamp mechanism others have suggested. I would create a disconnected
                            cursor class that automatically implements the entire behavior so you have
                            the opportunity to change your strategy without having to modify all your
                            edit/page pages. (A lot safer in terms of coding defects as well)

                            This technique avoids a lot of locking contention problems and tends to be
                            deadlock free (you can still get yourself in trouble if you're not careful),
                            and avoids the problems associated with "disappearing" web users (browser
                            close/nav away from site while on an edit screen), as the disconnected
                            cursor is destroyed with the rest of the client session on timeout. You
                            also don't have to time-out the edit page, it doesn't really matter if the
                            user sits on the page for an hour before clicking submit.

                            I also use this technique for both 2-tier and n-tier systems for the same
                            reasons. Much lower chance of lock contention and deadlock. If you are
                            building a banking app or something else that requires solid transactional
                            integrity you pretty much have to use real cursors with locks and design
                            your update sequences carefully to avoid deadlock, as well as make sure you
                            don't have queries anywhere in the system that will do table scans (or just
                            scan a lot of rows) as your edit locks will block these queries.

                            Dean

                            Message: 5
                            Date: Fri, 11 Apr 2003 09:15:54 -0700 (PDT)
                            From: Tim Andersen <timander37@...>
                            Subject: RE: DB Design - Web vs client server applications

                            We are in the process of developing a web application
                            to replace our existing client server application.
                            These changes are forced because of restricitons of a
                            web browser.

                            One major database design change is the user
                            persistance is kept in the database instead of an ini
                            file on the client machine (this could have been done
                            in the client server application, but with the web
                            application we were forced to make this design change)

                            another major change is the possibility of overwriting
                            updates if more than one person tries to update the
                            same record at the same time. to handle this, we are
                            planning to add a timestamp to every table and compare
                            it on the retrieve and update and if the timestamps
                            are different it will not overwrite changes someone
                            else made. if anyone else had a better way to handle
                            this issue i would be extremely interested.
                            thanks, tim.
                          • Alexander Tabakov
                            Hi, I have just one note on the theme of optimistic locking. Do not use timestamp, use record version instead. This is due to the granularity of the timestamp.
                            Message 13 of 18 , Apr 13, 2003
                            • 0 Attachment
                              Hi,

                              I have just one note on the theme of optimistic locking. Do not use
                              timestamp, use record version instead.

                              This is due to the granularity of the timestamp. Imagine what would
                              happen if you have 2 update operations that occur in less than a
                              second.

                              --
                              Best regards,
                              Alexander mailto:saho@...
                            • Scott W. Ambler
                              Depends on the database I suppose, but timestamps can be much more finely detailed than to the second. If you do use timestamps make sure the server sets them
                              Message 14 of 18 , Apr 13, 2003
                              • 0 Attachment
                                Depends on the database I suppose, but timestamps can be much more finely
                                detailed than to the second.

                                If you do use timestamps make sure the server sets them and not the client.

                                - Scott
                                ----- Original Message -----
                                From: "Alexander Tabakov" <saho@...>
                                To: <agileDatabases@yahoogroups.com>
                                Sent: Sunday, April 13, 2003 5:05 AM
                                Subject: Re: [agileDatabases] RE: DB Design - Web vs client server
                                applications


                                > Hi,
                                >
                                > I have just one note on the theme of optimistic locking. Do not use
                                > timestamp, use record version instead.
                                >
                                > This is due to the granularity of the timestamp. Imagine what would
                                > happen if you have 2 update operations that occur in less than a
                                > second.
                                >
                                > --
                                > Best regards,
                                > Alexander mailto:saho@...
                                >
                                >
                                >
                                > To unsubscribe from this group, send an email to:
                                > agileDatabases-unsubscribe@yahoogroups.com
                                >
                                >
                                >
                                > Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
                                >
                                >
                                >
                              • Bryan Hughes
                                Just some clarification... First, timestamps (aka datetime) have a millisecond, not second resolution (such as Informix). If you are stuck with SQL Server,
                                Message 15 of 18 , Apr 13, 2003
                                • 0 Attachment
                                  Just some clarification...

                                  First, timestamps (aka datetime) have a millisecond, not second resolution (such as Informix). If you are stuck with SQL Server, then it is only 3.33 milliseconds.

                                  Second, be aware that not every vendor describes timestamp as the same datatype. For example, SQL Server 2000 describes timestamp as "a database-wide unique number that gets updated every time a row gets updated" and is used typically as a mechanism for version-stamping table rows.

                                  Bryan Hughes
                                  Database Architect
                                  Spoke Software

                                  -----Original Message-----
                                  From: Alexander Tabakov [mailto:saho@...]
                                  Sent: Sun 4/13/2003 2:05 AM
                                  To: agileDatabases@yahoogroups.com
                                  Cc:
                                  Subject: Re: [agileDatabases] RE: DB Design - Web vs client server applications



                                  Hi,

                                  I have just one note on the theme of optimistic locking. Do not use
                                  timestamp, use record version instead.

                                  This is due to the granularity of the timestamp. Imagine what would
                                  happen if you have 2 update operations that occur in less than a
                                  second.

                                  --
                                  Best regards,
                                  Alexander mailto:saho@...


                                  ------------------------ Yahoo! Groups Sponsor ---------------------~-->
                                  Get 128 Bit SSL Encryption!
                                  http://us.click.yahoo.com/xaxhjB/hdqFAA/VygGAA/z3wwlB/TM
                                  ---------------------------------------------------------------------~->

                                  To unsubscribe from this group, send an email to:
                                  agileDatabases-unsubscribe@yahoogroups.com



                                  Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
                                • Rich
                                  Hi, Indeed, SQL Server Timestamps are described as containing binary data: the latest Server time followed by a HEX number describing the current version of
                                  Message 16 of 18 , Apr 13, 2003
                                  • 0 Attachment
                                    Hi,

                                    Indeed, SQL Server Timestamps are described as containing binary data:
                                    the latest Server time followed by a HEX number describing the current
                                    version of the row, which has nothing to do with how often or when the
                                    row was last modified, more like when an index referencing the heap was
                                    accessed.

                                    When dealing with SQL Server we always add a SQL Server Timestamp data
                                    type to each table. We find it helps reduce the number of locking
                                    conditions. Also it speeds up row comparsion, since the engine only
                                    needs to compare the two timestamp fields and not each and every field
                                    to see if a modification has occurred.

                                    MySQL has millisecond resolution timestamps and, interstingly, each
                                    table can have multiple timestamp fields. With MySQL we always add 2
                                    timestamp fields to each table, the first gets updated when the row is
                                    first committed and the second on every further update, quite a nice
                                    feature. It beats maintaing triggers or doing this stuff in application
                                    code.

                                    Timestamps are a bit of pain if it comes to migrating the database to a
                                    different vendor. But since that doesn't happen too often we always go
                                    with whatever functionality the current vendor offers, leaving the pain
                                    of a later migration to someone else :)

                                    The question is, then, is it an agile technique to use timestamps -
                                    given that they are implemented so differently across vendors?

                                    - Richard

                                    -----Urspr�ngliche Nachricht-----
                                    Von:
                                    sentto-7758546-188-1050255905-rquinn=web.de@...
                                    [mailto:sentto-7758546-188-1050255905-rquinn=web.de@...
                                    .com] Im Auftrag von Bryan Hughes
                                    Gesendet: Sonntag, 13. April 2003 19:42
                                    An: agileDatabases@yahoogroups.com; agileDatabases@yahoogroups.com
                                    Betreff: RE: [agileDatabases] RE: DB Design - Web vs client server
                                    applications


                                    Just some clarification...

                                    First, timestamps (aka datetime) have a millisecond, not second
                                    resolution (such as Informix). If you are stuck with SQL Server, then
                                    it is only 3.33 milliseconds.

                                    Second, be aware that not every vendor describes timestamp as the same
                                    datatype. For example, SQL Server 2000 describes timestamp as "a
                                    database-wide unique number that gets updated every time a row gets
                                    updated" and is used typically as a mechanism for version-stamping table
                                    rows.

                                    Bryan Hughes
                                    Database Architect
                                    Spoke Software

                                    -----Original Message-----
                                    From: Alexander Tabakov [mailto:saho@...]
                                    Sent: Sun 4/13/2003 2:05 AM
                                    To: agileDatabases@yahoogroups.com
                                    Cc:
                                    Subject: Re: [agileDatabases] RE: DB Design - Web vs client server
                                    applications



                                    Hi,

                                    I have just one note on the theme of optimistic locking. Do not
                                    use
                                    timestamp, use record version instead.

                                    This is due to the granularity of the timestamp. Imagine what
                                    would
                                    happen if you have 2 update operations that occur in less than a
                                    second.

                                    --
                                    Best regards,
                                    Alexander mailto:saho@...


                                    ------------------------ Yahoo! Groups Sponsor


                                    To unsubscribe from this group, send an email to:
                                    agileDatabases-unsubscribe@yahoogroups.com



                                    Your use of Yahoo! Groups is subject to
                                    http://docs.yahoo.com/info/terms/





                                    Yahoo! Groups Sponsor



                                    To unsubscribe from this group, send an email to:
                                    agileDatabases-unsubscribe@yahoogroups.com



                                    Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
                                  • Bryan Hughes
                                    We too use two datetime fields for every row, the first is updatetime and the second is inserttime. Depending upon which vendor you are using, pick the
                                    Message 17 of 18 , Apr 13, 2003
                                    • 0 Attachment
                                      We too use two datetime fields for every row, the first is updatetime and the second is inserttime. Depending upon which vendor you are using, pick the appropriate implementation. The significance is that it allows in-place data transformation and migration.

                                      There are two approaches to database migration from one version schema to another. In many instances, the schema delta is significant and requires table restructuring with some data transformation both pre and post migration. Most people tackle database migration as down-system approach where it needs to be done as quickly as possible, usually bringing the entire resources of the server to bare on the problem.

                                      An alternative approach that I have successfully used is the slow, in-place approach. This requires two tools. The first to deploy an inplace upgrade where altered colliding tables are created with a mangled name. There are some issues with this, especially if you are stuck with SQL Server which I do not believe allows you to rename tables.

                                      The idea is using the inserttime and updatetime, to iteratively loop through the massive tables in parallel while throttled down until a state is reached where you have eventually caught up to the changes. At this point, your tools need to be able to place a table lock which the old table is swapped out for the new one. Depending upon your database vendor, there might be some issues with fixing up foreign constraints. With most of them (definately Informix and I believe with SQLServer), updating the constraints in the system catalog will solve the problem.

                                      At Shutterfly, we transformed and migrated a massive 500 million row table over 3 days, in-place while the system was live with only a minor interruption. After swapping out the table and updating the system catelog (again depending upon the vendor), a SQL Exception is thrown because the catalog was modified. We had to restart the system at this point, but correctly written connection managers should simple re-aquire new connections, seamlessly moving on.

                                      Bryan

                                      -----Original Message-----
                                      From: Rich [mailto:rquinn@...]
                                      Sent: Sun 4/13/2003 1:40 PM
                                      To: agileDatabases@yahoogroups.com
                                      Cc:
                                      Subject: RE: [agileDatabases] RE: DB Design - Web vs client server applications



                                      Hi,

                                      Indeed, SQL Server Timestamps are described as containing binary data:
                                      the latest Server time followed by a HEX number describing the current
                                      version of the row, which has nothing to do with how often or when the
                                      row was last modified, more like when an index referencing the heap was
                                      accessed.

                                      When dealing with SQL Server we always add a SQL Server Timestamp data
                                      type to each table. We find it helps reduce the number of locking
                                      conditions. Also it speeds up row comparsion, since the engine only
                                      needs to compare the two timestamp fields and not each and every field
                                      to see if a modification has occurred.

                                      MySQL has millisecond resolution timestamps and, interstingly, each
                                      table can have multiple timestamp fields. With MySQL we always add 2
                                      timestamp fields to each table, the first gets updated when the row is
                                      first committed and the second on every further update, quite a nice
                                      feature. It beats maintaing triggers or doing this stuff in application
                                      code.

                                      Timestamps are a bit of pain if it comes to migrating the database to a
                                      different vendor. But since that doesn't happen too often we always go
                                      with whatever functionality the current vendor offers, leaving the pain
                                      of a later migration to someone else :)

                                      The question is, then, is it an agile technique to use timestamps -
                                      given that they are implemented so differently across vendors?

                                      - Richard

                                      -----Ursprüngliche Nachricht-----
                                      Von:
                                      sentto-7758546-188-1050255905-rquinn=web.de@...
                                      [mailto:sentto-7758546-188-1050255905-rquinn=web.de@...
                                      .com] Im Auftrag von Bryan Hughes
                                      Gesendet: Sonntag, 13. April 2003 19:42
                                      An: agileDatabases@yahoogroups.com; agileDatabases@yahoogroups.com
                                      Betreff: RE: [agileDatabases] RE: DB Design - Web vs client server
                                      applications


                                      Just some clarification...

                                      First, timestamps (aka datetime) have a millisecond, not second
                                      resolution (such as Informix). If you are stuck with SQL Server, then
                                      it is only 3.33 milliseconds.

                                      Second, be aware that not every vendor describes timestamp as the same
                                      datatype. For example, SQL Server 2000 describes timestamp as "a
                                      database-wide unique number that gets updated every time a row gets
                                      updated" and is used typically as a mechanism for version-stamping table
                                      rows.

                                      Bryan Hughes
                                      Database Architect
                                      Spoke Software

                                      -----Original Message-----
                                      From: Alexander Tabakov [mailto:saho@...]
                                      Sent: Sun 4/13/2003 2:05 AM
                                      To: agileDatabases@yahoogroups.com
                                      Cc:
                                      Subject: Re: [agileDatabases] RE: DB Design - Web vs client server
                                      applications



                                      Hi,

                                      I have just one note on the theme of optimistic locking. Do not
                                      use
                                      timestamp, use record version instead.

                                      This is due to the granularity of the timestamp. Imagine what
                                      would
                                      happen if you have 2 update operations that occur in less than a
                                      second.

                                      --
                                      Best regards,
                                      Alexander mailto:saho@...


                                      ------------------------ Yahoo! Groups Sponsor


                                      To unsubscribe from this group, send an email to:
                                      agileDatabases-unsubscribe@yahoogroups.com



                                      Your use of Yahoo! Groups is subject to
                                      http://docs.yahoo.com/info/terms/





                                      Yahoo! Groups Sponsor



                                      To unsubscribe from this group, send an email to:
                                      agileDatabases-unsubscribe@yahoogroups.com



                                      Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



                                      ------------------------ Yahoo! Groups Sponsor ---------------------~-->
                                      Get 128 Bit SSL Encryption!
                                      http://us.click.yahoo.com/W7NydA/hdqFAA/VygGAA/z3wwlB/TM
                                      ---------------------------------------------------------------------~->

                                      To unsubscribe from this group, send an email to:
                                      agileDatabases-unsubscribe@yahoogroups.com



                                      Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
                                    • Alexander Tabakov
                                      ... You are right, that s the question. That s why I advocated using explicit version number instead of timestamp field. Of course everything depends on you
                                      Message 18 of 18 , Apr 14, 2003
                                      • 0 Attachment
                                        >The question is, then, is it an agile technique to use timestamps -
                                        >given that they are implemented so differently across vendors?

                                        You are right, that's the question. That's why I advocated using
                                        explicit version number instead of timestamp field. Of course everything
                                        depends on you project, what I mean is that anybody concerned about
                                        working with with different databases must be aware of this gotcha :).

                                        --
                                        Best regards,
                                        Alexander mailto:saho@...
                                      Your message has been successfully submitted and would be delivered to recipients shortly.