Professional Web Applications Themes

MS SQL Server 2000 very serious design flaws with transactions andlocking ? (Unusable for ERP systems) - Microsoft SQL / MS SQL Server

Hello, out there ! After having read several doents about SQL Server 7.0 / 2000 and locking, transactions i am convinced, that SQL Server is very old fashioned and unuseable for OLTP, data warehouse, ERP Software, like SAP/3, peoplesoft, oracle of JD Edwards and even ADS based on SQL (to come when ?). Here the problem, perhaps there is a solution, any hints welcome: WINDOW 1: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRANSACTION INSERT INTO COLUMN VALUES ('a','a',NULL,1); WINDOWS 2: SELECT * FROM COLUMN WITH (NOLOCK); Fact: SQL Server blocks selects and other statements while a transaction is running. ...

  1. #1

    Default MS SQL Server 2000 very serious design flaws with transactions andlocking ? (Unusable for ERP systems)

    Hello, out there !

    After having read several doents about SQL Server 7.0 / 2000 and
    locking, transactions i am convinced, that SQL Server is very old
    fashioned and unuseable for OLTP, data warehouse, ERP Software, like
    SAP/3, peoplesoft, oracle of JD Edwards and even ADS based on SQL (to
    come when ?).

    Here the problem, perhaps there is a solution, any hints welcome:

    WINDOW 1:
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    BEGIN TRANSACTION
    INSERT INTO COLUMN VALUES ('a','a',NULL,1);

    WINDOWS 2:
    SELECT * FROM COLUMN WITH (NOLOCK);

    Fact: SQL Server blocks selects and other statements while a transaction
    is running.

    While reading this doent:
    sql-server-performance.com/Other/England06.pdf

    i perhaps have understood, why. Whereas Oracle, Informix 9.xx, Firebird,
    and even PostgreSQL have MVCC (Multi Versioning Concurrency Control), MS
    SQL Server 2000 has several problems.

    1. There are transactions, yes, but one transaction blocks the other.
    This seems to be the case, for all transaction types, except READ
    UNCOMMITTED (see example above).

    2. Doing transactions uncommitted, which seems to equal (select ...with
    NOLOCK), all selects show up dirty entries from transactions, which are
    still running. Imagine, you do several inserts into tables, a dump, a
    restore of a dump, you move large tables, you do data mining. All those
    jobs have to be bundled in transactions. selects can be executed, but
    several tables show inconinstencies in data. They are called dirty reads
    or phantom entries.

    3. Doing transactions COMMITTED or SERIALIZABLE or REPETABLE READ,
    selects hang, regardless those select options WITH (NOLOCK).

    4. DEADLOCKS occur in complex ERP systems, which do many optimisations
    and yses, statistics in background.

    MVCC does things different. Whenever a transaction begins, a virtual
    copy of the database/tables/... is made instantly (that goes in nearly
    zero time), the transaction is executed only on that virtual copy and
    all other transactions, selects, inserts and updates do not see any
    locks. DEADLOCKS can never occurr, programmers never do have to care
    about locks (they can, if needed), data incoinsistencies, and so on.

    IMHO SQL Server 7.0/2000 seems to be a complete rewrite of Sysbase SQL
    anywhere, because the query optimizer has very much improved (heard,
    that 10 former oracle programmers are involved), but they didn't succeed
    in implementing MVCC, the current "state of the art" in SQL programming.

    I cannot imagine, that MS SQL Server is that much old fashioned and very
    much behind even PostgreSQL. USENET is full of such problems, all
    unsolved, so many OLTP users waiting for sql server answering their data
    masks, so much time waisted. Microsofts solution is to keep transactions
    as short as possible. This is impossible very often.

    Even Oracle wrote a nice PDF about:
    http://otn.oracle.com/products/manageability/database/pdf/SSOracletechcomparison1.pdf

    I really wonder, how much MS has payed for getting sql server certified
    for ERP systems, like SAP R/3. Microsoft even has bought for about 1000
    mio. /$ shares from german telecom and all programmers suddenly are
    advised only to use Microsoft products (and SQL Server 2000, too), why ?
    How come this ?

    apart from this, any technical clues always welcome...

    regards, Guido Stepken

    Guido Guest

  2. #2

    Default Re: MS SQL Server 2000 very serious design flaws with transactions and locking ? (Unusable for ERP systems)

    Well, jeepers, it works for me. If I do your test I get the result set I
    expect with no blocking:
    (PS - SQL Server does not require the semicolon on the end of a statement)

    regards, -marty nicholson

    DDL

    CREATE TABLE [theTable] (
    [col1] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [col2] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [col3] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [col4] [int] NOT NULL ,
    CONSTRAINT [PK_theTable] PRIMARY KEY CLUSTERED
    (
    [col1]
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    DML

    insert into theTable values('b', 'b', NULL, 1)
    insert into theTable values('c', 'b', NULL, 1)
    insert into theTable values('d', 'b', NULL, 1)
    insert into theTable values('e', 'b', NULL, 1)
    insert into theTable values('f', 'b', NULL, 1)

    WINDOW1:

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    BEGIN TRANSACTION
    INSERT INTO theTable VALUES ('a','a',NULL,1)
    WAITFOR delay '00:00:15'
    ROLLBACK TRAN

    WINDOW2:

    select * from theTable (NOLOCK)

    RESULT SET (BEFORE ROLLBACK) FROM WINDOW 2:

    col1 col2 col3 col4
    -------- -------- -------- -----------
    a a NULL 1
    b b NULL 1
    c b NULL 1
    d b NULL 1
    e b NULL 1
    f b NULL 1

    (6 row(s) affected)

    RESULT SET (AFTER ROLLBACK) FROM WINDOW 2:

    col1 col2 col3 col4
    -------- -------- -------- -----------
    b b NULL 1
    c b NULL 1
    d b NULL 1
    e b NULL 1
    f b NULL 1

    (5 row(s) affected)



    "Guido Stepken" <de> wrote in message
    news:bfhn0o$vav$00$t-online.com... 
    http://otn.oracle.com/products/manageability/database/pdf/SSOracletechcompar
    ison1.pdf 


    Martin Guest

  3. #3

    Default Re: MS SQL Server 2000 very serious design flaws with transactions and locking ? (Unusable for ERP systems)

    On Mon, 21 Jul 2003 23:46:31 +0200, Guido Stepken
    <de> wrote: 

    Only if there is a potential conflict.

    Admitedly this is a complex topic, and SQLServer's logic is not always
    as tight as it might be, and Oracle's write-aside architecture is more
    powerful in the abstract -- but much more expensive to run, too!
     

    Yes but people seem quite able to live with it. If the blocks are
    short enough, they may just run faster than "concurrent" on a slower
    system!
     

    Nobody runs that way. Nobody should, anyway.
     

    The default use of SERIALIZABLE in COM applications is a real
    bottleneck, but it is fixable.
     

    You seem to be confusing deadlocks with concurrency. A lock that
    causes a pending transaction is not the same thing as a deadlock.
    Perhaps it increases the opportunities for deadlock, but in a clean
    design, it is still not a problem.
     

    Nothing is free, either the overhead occurs when the copy is made, or
    when a concurrency issue arises.
     

    Of course deadlocks can still occur.
     

    I too am surprised that SQL7/SQL2K do not have MVCC, but in my view
    it's not nearly as big a deal as you make out.
     

    Only in very high write-transaction rate systems does it matter much.
    And even then, the cost of MVCC can be so high, that you're still
    better off optimizing the architecture instead of relying on MVCC.
     

    Well of course!
     

    Clues are good.

    Joshua Stern
     

    JXStern Guest

  4. #4

    Default Re: MS SQL Server 2000 very serious design flaws with transactionsand locking ? (Unusable for ERP systems)

    Yes, but you have dirty entries. The example with "READ UNCOMMITTED" is
    the only case, where selects are not blocked. Alle other transaction
    will block select, bad.
    I have used this example to show, that it is possible to have select
    without blocking in SQL Server, while a transaction is running.
    What i also wanted to make clear, that there are incoinsistencies
    occuring in ERP systems, which may cause several problems, which have to
    be handled by software logic, because of lack of mvcc. This makes
    software development unnecessarily expensive with MS SQL Server, IMHO.

    tnx for your nice example, it will help to make things clearer.

    regards, Guido Stepken

    Martin Nicholson wrote: 
    >
    > http://otn.oracle.com/products/manageability/database/pdf/SSOracletechcompar
    > ison1.pdf

    >
    >
    >[/ref]

    Guido Guest

  5. #5

    Default Re: MS SQL Server 2000 very serious design flaws with transactions and locking ? (Unusable for ERP systems)

    SQL Server unable to run SAP? Maybe you should have a look at these
    benchmarks, where SQL Server shares most of the top 10 with DB2,
    accidentally another DBMS that doesn't have MVCC. Oracle does have some
    entries, but only with machines that have double the number of processors.
    http://www.ideasinternational.com/benchmark/sap/sap3sdR4.html

    MVCC does have it's own problems, see this (not totally unbiased, but quite
    good) report:
    http://www-3.ibm.com/software/data/pubs/papers/readconsistency/readconsistency.pdf
     
    MVCC is definitly not state of the art, it was Oracle solution to locking
    before ANSI defined isolation levels somewhere in the late 80's. They are
    now trying to sell it of as state-of-the-art, maybe because they have run
    out of other things they can use to justify the fact that their pricetag is
    about twice as high as SQL Server?

    The next version of SQL Server will have MVCC btw, but it is one of the
    features I can be bothered about the least.



    "Guido Stepken" <de> wrote in message
    news:bfhn0o$vav$00$t-online.com... 
    http://otn.oracle.com/products/manageability/database/pdf/SSOracletechcomparison1.pdf 


    Jacco Guest

  6. #6

    Default Re: MS SQL Server 2000 very serious design flaws with transactionsand locking ? (Unusable for ERP systems)

    Hi Jushua !

    Sure, deadlocks occurr, when one lock waits for the other to be
    released. In practice SQL Server is reported to show inpredictable
    runtimes on transactions. The may vary in depencency of amount of data,
    number of concurrent transactions (select is handeled internally as such
    one), transactions execution times vary from 10 seconds to 4 hours (same
    transaction with same results).

    With MVCC i never ever had any deadlocks at highest loads. The isolation
    level is handled at database level. Transactions on a database is
    handeled like as transaction on several more identical databases. How
    should a lock occurr ? ACID is the basic feature to implement MVCC.

    Nothing is free, yes. I couldn't measure any execution time for MVCC,
    neither with informix nor postgreSQL.

    regards, Guido Stepken
     
    >
    >
    > Nothing is free, either the overhead occurs when the copy is made, or
    > when a concurrency issue arises.[/ref]
     
     

    Guido Guest

  7. #7

    Default Re: MS SQL Server 2000 very serious design flaws with transactionsand locking ? (Unusable for ERP systems)

    i fear, that your benchmark of SAP R/3 is quite unrealistic. SAP R/3
    does many, many things in background, statistics, process optimisation,
    depending on the modules activated. Do a backup of all data while
    running this benchmark with many inserts .... Restore his backup then.
    You will see, that your benchmarks will really differ much and that the
    backup will have endless incoinsitencies (you have backed up phantom
    entries) or you will see endless locks or deadlocks.
    With MVCC i can backup whatever i want an whenever i want, without any
    incoinsitencies, locks, deadlocks.
    This paper about IBM's readconsistency is worth reading. I still wonder,
    if DB2 has MVCC or MVRC. How do they differ ?

    I would rather say, that MVCC is state-of -the -art and that oracle was
    years ahead of other database programmers. I wasn't just a alternative
    solution, MVCC was a really good feature for programmers and made online
    backups without any incoinsistent data occuring in the backups possible.
    I cannot predict, how many backups of other databases (those without
    MVCC) are unusable because there will be incoinsistencies after a
    restore. Runnning 24/7 databases without MVCC is impossible.

    regards, Guido Stepken



    Jacco Schalkwijk wrote: 
    >
    > MVCC is definitly not state of the art, it was Oracle solution to locking
    > before ANSI defined isolation levels somewhere in the late 80's. They are
    > now trying to sell it of as state-of-the-art, maybe because they have run
    > out of other things they can use to justify the fact that their pricetag is
    > about twice as high as SQL Server?
    >
    > The next version of SQL Server will have MVCC btw, but it is one of the
    > features I can be bothered about the least.
    >
    >
    >
    > "Guido Stepken" <de> wrote in message
    > news:bfhn0o$vav$00$t-online.com...

    >
    > http://otn.oracle.com/products/manageability/database/pdf/SSOracletechcomparison1.pdf

    >
    >
    >[/ref]

    Guido Guest

  8. #8

    Default Re: MS SQL Server 2000 very serious design flaws with transactions and locking ? (Unusable for ERP systems)

    Guido,

    I suggest you read up a bit on the features SQL Server has to offer, because
    it doesn't seem to me that you know a lot about it except that it doesn't
    have MVCC. SQL Server does online backups without any problems and there are
    large numbers of sites running 24/7 on SQL Server.

    And you can have a look at comapnies that are running SAP on SQL Server on
    this promo site: http://www.microsoft-sap.com/default.aspx


    "Guido Stepken" <de> wrote in message
    news:bfhvr5$105$05$t-online.com... [/ref]
    processors. [/ref]
    quite [/ref]
    http://www-3.ibm.com/software/data/pubs/papers/readconsistency/readconsistency.pdf 
    > >
    > > MVCC is definitly not state of the art, it was Oracle solution to[/ref][/ref]
    locking [/ref]
    are [/ref]
    run [/ref]
    is 
    > >
    > >[/ref][/ref]
    http://otn.oracle.com/products/manageability/database/pdf/SSOracletechcomparison1.pdf 
    > >
    > >
    > >[/ref]
    >[/ref]


    Jacco Guest

  9. #9

    Default Re: MS SQL Server 2000 very serious design flaws with transactions and locking ? (Unusable for ERP systems)

    The example works for me if I use SET TRANSACTION ISOLATION LEVEL READ
    UNCOMMITTED or not in Window 1.

    BOL states that SET TRANSACTION ISOLATION LEVEL is only applicable to
    SELECT's.

    So, if

    WINDOW1:

    BEGIN TRANSACTION
    INSERT INTO theTable VALUES ('a','a',NULL,1)
    WAITFOR delay '00:00:15'
    ROLLBACK TRAN

    WINDOW2:

    select * from theTable (NOLOCK)

    THEN the result set is the same as below.

    I also tested READ COMMITTED and READ UNCOMMITTED and they worked as
    expected (no blocking).

    regards, -marty nicholson

    "Guido Stepken" <de> wrote in message
    news:bfhteo$9n9$07$t-online.com... [/ref]
    I [/ref]
    statement) 
    > >
    > >[/ref][/ref]
    http://otn.oracle.com/products/manageability/database/pdf/SSOracletechcompar 
    > >
    > >
    > >[/ref]
    >[/ref]


    Martin Guest

  10. #10

    Default Re: MS SQL Server 2000 very serious design flaws with transactions and locking ? (Unusable for ERP systems)

    Ok, looks like I might have misunderstood your question.

    If you want to get the WINDOW 2 'SELECT' to complete without (NOLOCK) and
    without blocking try:

    select * from theTable (READPAST)

    regards, -marty nicholson

    "Guido Stepken" <de> wrote in message
    news:bfhteo$9n9$07$t-online.com... [/ref]
    I [/ref]
    statement) 
    > >
    > >[/ref][/ref]
    http://otn.oracle.com/products/manageability/database/pdf/SSOracletechcompar 
    > >
    > >
    > >[/ref]
    >[/ref]


    Martin Guest

  11. #11

    Default Re: MS SQL Server 2000 very serious design flaws with transactions and locking ? (Unusable for ERP systems)

    Hi,
    The SQL backup feature are extremely robust. There is no likelihood of any
    data being missed or left out just because of running transactions. A
    standard backup dumps pages of data from the data files. The transaction log
    is marked before commencement and marked after competition, then this part
    of the transaction log is dumped as well.


    --
    I hope this helps
    regards
    Greg O MCSD
    SQL Scribe Doentation Builder
    Doent any SQL server database in minutes
    Programmers love it, DBA dream of it
    AGS SQL Scribe download a 30 day trial today
    http://www.ag-software.com/ags_scribe_index.asp

    "Guido Stepken" <de> wrote in message
    news:bfhvr5$105$05$t-online.com... [/ref]
    processors. [/ref]
    quite [/ref]
    http://www-3.ibm.com/software/data/pubs/papers/readconsistency/readconsistency.pdf 
    > >
    > > MVCC is definitly not state of the art, it was Oracle solution to[/ref][/ref]
    locking [/ref]
    are [/ref]
    run [/ref]
    is 
    > >
    > >[/ref][/ref]
    http://otn.oracle.com/products/manageability/database/pdf/SSOracletechcomparison1.pdf 
    > >
    > >
    > >[/ref]
    >[/ref]


    Greg Guest

  12. #12

    Default Re: MS SQL Server 2000 very serious design flaws with transactionsand locking ? (Unusable for ERP systems)

    Hi Martin !

    select * from theTable (READPAST) is quite ok. Quite strange is, that
    the logic of propper locking and transactions as well as the logic to
    avoid dirty reads (or phantom entries) has to be put into the client.
    fine. My ACCESS Client has one problem. It cannot be rewritten. Is there
    a solution server side existing with same effect ? Many OLTP client
    software, which works over ODBC has to be rewritten then, e.g. when
    migrating from Informix, PostgreSQL, Oracle with MVCC to MS SQL Server
    without MVCC.
    tnx for your hints.

    regards, Guido Stepken

    Martin Nicholson wrote: [/ref]
    >
    > I
    > [/ref]
    >
    > statement)
    > [/ref]
    > http://otn.oracle.com/products/manageability/database/pdf/SSOracletechcompar
    > [/ref]
    >
    >[/ref]

    Guido Guest

  13. #13

    Default Re: MS SQL Server 2000 very serious design flaws with transactionsand locking ? (Unusable for ERP systems)

    MS SQL Mag:

    "You'll notice differences in table backups between SQL Server 6.5 and
    7.0. With SQL Server 7.0, you back up only files and filegroups; to back
    up one table, you need to save that table in a file or filegroup and
    then back it up. This difference might frustrate systems administrators,
    but Microsoft's reasons for implementing these changes include system
    and database stability and consistency."

    Please tell me, how one can backup SQL Server without locking tables or
    database file for some time ?

    regards, Guido Stepken

    Jacco Schalkwijk wrote: [/ref]
    >
    > processors.
    > [/ref]
    >
    > quite
    > [/ref]
    >
    > http://www-3.ibm.com/software/data/pubs/papers/readconsistency/readconsistency.pdf
    > [/ref]
    >
    > locking
    > [/ref]
    >
    > are
    > [/ref]
    >
    > run
    > [/ref]
    >
    > is
    > [/ref]
    > http://otn.oracle.com/products/manageability/database/pdf/SSOracletechcomparison1.pdf
    > [/ref]
    >
    >[/ref]

    Guido Guest

  14. #14

    Default Re: MS SQL Server 2000 very serious design flaws with transactions and locking ? (Unusable for ERP systems)

    Did you try creating a view which contains this?

    Like:
    create view vw_sales as
    select * from sales (READPAST)

    Net

    Please reply only to the newsgroups.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    You assume all risk for your use.
    Copyright SQLDev.Net 1991-2003 All rights reserved.

    "Guido Stepken" <de> wrote in message
    news:bfin1p$69s$04$t-online.com... [/ref]
    and 
    > >
    > > I
    > > 
    > >
    > > statement)
    > > [/ref][/ref]
    transaction [/ref][/ref]
    Firebird, [/ref][/ref]
    MS [/ref][/ref]
    ....with [/ref][/ref]
    are [/ref][/ref]
    those [/ref][/ref]
    reads [/ref][/ref]
    succeed [/ref][/ref]
    programming. [/ref][/ref]
    very [/ref][/ref]
    data [/ref][/ref]
    transactions 
    > >[/ref][/ref]
    http://otn.oracle.com/products/manageability/database/pdf/SSOracletechcompar [/ref][/ref]
    certified [/ref][/ref]
    1000 [/ref][/ref]

    > >
    > >[/ref]
    >[/ref]


    Gert Guest

  15. #15

    Default Re: MS SQL Server 2000 very serious design flaws with transactions and locking ? (Unusable for ERP systems)

    Time consuming?
    SELECT 'CREATE VIEW vw' + t.table_name + ' AS' + CHAR(13) +
    'SELECT * FROM ' + t.table_name + ' (READPAST)' + CHAR(13) + 'GO' + CHAR(13)
    FROM information_schema.tables t WHERE table_type = 'base table'

    will give you a script to create views for all your tables in the database
    and that took me about 2 minutes to write.
    Having ANSI standard information_schema views in SQL Server makes this kind
    of script generation a breeze. Privileges are also part of the information
    schema and a script to generate views on all the tables and copy all the
    permission from the tables to the views would probably take no more than an
    hour or 2 to write. (If someone hasn't written it already)


    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Guido Stepken" <de> wrote in message
    news:bfivq1$pkv$05$t-online.com... [/ref]
    rights. 
    > >
    > > and
    > > [/ref][/ref]
    is [/ref][/ref]
    to [/ref][/ref]
    set [/ref][/ref]
    (to 
    > >
    > > transaction
    > > 
    > >
    > > Firebird,
    > > [/ref][/ref]
    Control), [/ref][/ref]
    other. 
    > >
    > > ...with
    > > 
    > >
    > > are
    > > [/ref][/ref]

    > >
    > > those
    > > [/ref][/ref]
    but 
    > >
    > > reads
    > > [/ref][/ref]
    optimisations [/ref][/ref]
    nearly [/ref][/ref]
    and [/ref][/ref]
    SQL 
    > >
    > > succeed
    > > 
    > >
    > > programming.
    > > 
    > >
    > > very
    > > 
    > >
    > > data
    > > 
    > >
    > > transactions
    > > 
    > >[/ref][/ref]
    http://otn.oracle.com/products/manageability/database/pdf/SSOracletechcompar 
    > >
    > > certified
    > > 
    > >
    > > 1000
    > > [/ref][/ref]
    why 
    > >
    > >[/ref]
    >[/ref]


    Jacco Guest

  16. #16

    Default Re: MS SQL Server 2000 very serious design flaws with transactionsand locking ? (Unusable for ERP systems)

    Hmmm, imagine, a long time running transaction starts. Then you do a
    online backup. During this backup another long time running transaction
    starts, which will be finished after your backup.
    You have 3 timelines of incoinsistent data in your database, in your
    backup, and after backup.
    Ok, i roll forward one transaction and backward the other to get into a
    coinsistent state.
    rolling forward and backward is also a transaction, i assume. When then
    will you reach a coinsistend state in a very busy database with long
    transactions running (data mining, ...) In the meantime the SQL Server
    is always in a coinsistent state, which can result in wrong statistics,
    queries .....

    Your description assumes, that transactions are kept short. So its very
    likely, that sql server reaches a coinsistent state short time after
    backup is completed. This is impossible, if you have long time running
    transactions.

    regards, Guido Stepken


    Jacco Schalkwijk wrote: [/ref]
    >
    > backup
    > [/ref]
    >
    > rights.
    > [/ref]
    >
    > there
    > [/ref]
    >
    > optimisation,
    > [/ref]
    >
    > the
    > [/ref]
    >
    > wonder,
    > [/ref]
    >
    > was
    > [/ref]
    >
    > online
    > [/ref]
    >
    > possible.
    > [/ref]
    >
    > some
    > [/ref]
    > http://www-3.ibm.com/software/data/pubs/papers/readconsistency/readconsisten
    > cy.pdf
    > [/ref]
    >
    > the
    > [/ref]
    >
    > (to
    > [/ref]
    >
    > Control),
    > [/ref]
    >
    > other.
    > [/ref]
    >
    > a
    > [/ref]
    >
    > but
    > [/ref]
    >
    > optimisations
    > [/ref]
    >
    > nearly
    > [/ref]
    >
    > and
    > [/ref]
    >
    > SQL
    > [/ref]
    > http://otn.oracle.com/products/manageability/database/pdf/SSOracletechcompar
    > ison1.pdf
    > [/ref]
    >
    > why
    > [/ref]
    >
    >[/ref]

    Guido Guest

  17. #17

    Default Re: MS SQL Server 2000 very serious design flaws with transactions and locking ? (Unusable for ERP systems)


    "Guido Stepken" <de> wrote in message
    news:bfj1u2$a5f$02$t-online.com... 

    Nope the data is always consistent. It is the one of the essential
    properties of transactions that they always leave the database in a
    consistent state. (Well, it is that way on SQL Server, maybe in Oracle it is
    different, I don't know). Some resources might be blocked by the
    transaction, but we discussed that point earlier. When you restore a backup
    all the transactions will be rolled forward or back _before_ the database is
    available again. The time it will take to do this is user definable, but is
    by default less than one minute.
     

    Running data mining and OLTP on the same database is not a good idea from a
    performance point of view with any DBMS. The performance requirements for
    the two are so different that you can't tune a database for both of them at
    the same time. 

    Again, SQL Server is _always_ in a consistent state when it is available to
    users. You are confusing inconsistency with locking. Locking exists beacuse
    it prevents inconsistencies.
     [/ref]
    transaction [/ref]
    one [/ref]
    the 
    > >
    > > backup
    > > [/ref][/ref]
    database 
    > >
    > > rights.
    > > [/ref][/ref]
    back [/ref][/ref]
    administrators, [/ref][/ref]
    or 
    > >
    > > there
    > > [/ref][/ref]
    Server 
    > >
    > > optimisation,
    > > [/ref][/ref]
    then. 
    > >
    > > the
    > > [/ref][/ref]
    any 
    > >
    > > wonder,
    > > 
    > >
    > > was
    > > [/ref][/ref]
    alternative 
    > >
    > > online
    > > 
    > >
    > > possible.
    > > 
    > >
    > > some
    > > [/ref][/ref]
    but 
    > >[/ref][/ref]
    http://www-3.ibm.com/software/data/pubs/papers/readconsistency/readconsisten [/ref][/ref]
    They [/ref][/ref]
    have 
    > >
    > > the
    > > [/ref][/ref]
    and [/ref][/ref]
    like 
    > >
    > > (to
    > > 
    > >
    > > Control),
    > > 
    > >
    > > other.
    > > [/ref][/ref]
    which [/ref][/ref]
    dump, 
    > >
    > > but
    > > 
    > >
    > > optimisations
    > > [/ref][/ref]
    virtual 
    > >
    > > nearly
    > > 
    > >
    > > and
    > > [/ref][/ref]
    any [/ref][/ref]
    care [/ref][/ref]
    on. 
    > >
    > > SQL
    > > [/ref][/ref]
    (heard, [/ref][/ref]
    and [/ref][/ref]
    their 
    > >[/ref][/ref]
    http://otn.oracle.com/products/manageability/database/pdf/SSOracletechcompar [/ref][/ref]
    are 
    > >
    > > why
    > > 
    > >
    > >[/ref]
    >[/ref]


    Jacco Guest

  18. Moderated Post

    Default Re: MS SQL Server 2000 very serious design flaws with transactionsand locking ? (Unusable for ERP systems)

    Removed by Administrator
    Guido Guest
    Moderated Post

  19. #19

    Default Re: MS SQL Server 2000 very serious design flaws with transactionsand locking ? (Unusable for ERP systems)

    I think, that your solution causes very much overhead. Have you
    considered, that creating a view is internally a transaction ?
    Transactions, which run long time block selects.
    All together, this solution is far more cpu intensive, than MVCC on
    PostgreSQL, which is the much cleaner and simpler solution.

    regards, Guido Stepken

    Jacco Schalkwijk wrote: [/ref]
    >
    > rights.
    > [/ref]
    >
    > is
    > [/ref]
    >
    > to
    > [/ref]
    >
    > set
    > [/ref]
    >
    > (to
    > [/ref]
    >
    > Control),
    > [/ref]
    >
    > other.
    > [/ref]
    >
    > a
    > [/ref]
    >
    > but
    > [/ref]
    >
    > optimisations
    > [/ref]
    >
    > nearly
    > [/ref]
    >
    > and
    > [/ref]
    >
    > SQL
    > [/ref]
    > http://otn.oracle.com/products/manageability/database/pdf/SSOracletechcompar
    > [/ref]
    >
    > why
    > [/ref]
    >
    >[/ref]

    Guido Guest

  20. Moderated Post

    Default Re: MS SQL Server 2000 very serious design flaws with transactions and locking ? (Unusable for ERP systems)

    Removed by Administrator
    Jacco Guest
    Moderated Post

Page 1 of 2 12 LastLast

Similar Threads

  1. Replies: 0
    Last Post: December 21st, 05:01 PM
  2. Replies: 1
    Last Post: September 5th, 08:55 PM
  3. Replies: 0
    Last Post: September 5th, 01:50 PM
  4. Replies: 0
    Last Post: August 1st, 04:16 PM
  5. Problem with VB6 App & SQL 2000 (Locking & lost transactions)
    By Javier Villegas in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: March 29th, 03:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139