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

RE: [agileDatabases] RE: DB Design - Web vs client server applications

Expand Messages
  • 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 1 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 2 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 3 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.