|
|
You're in the
sql server programming
group:MS SQL Server 2000 very serious design flaws with transactions and locking ? (Unusable for ERP systems)
sql server programming:
On Mon, 21 Jul 2003 23:46:31 +0200, Guido Stepken
[quoted text, click to view] <g.stepken@t-online.de> wrote: >After having read several documents 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. 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! [quoted text, click to view] >While reading this document: >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). 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! [quoted text, click to view] >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. Nobody runs that way. Nobody should, anyway. [quoted text, click to view] >3. Doing transactions COMMITTED or SERIALIZABLE or REPETABLE READ, >selects hang, regardless those select options WITH (NOLOCK). The default use of SERIALIZABLE in COM applications is a real bottleneck, but it is fixable. [quoted text, click to view] >4. DEADLOCKS occur in complex ERP systems, which do many optimisations >and analyses, statistics in background. 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. [quoted text, click to view] >MVCC does things different. Whenever a transaction begins, a virtual >copy of the database/tables/... is made instantly (that goes in nearly >zero time), Nothing is free, either the overhead occurs when the copy is made, or when a concurrency issue arises. [quoted text, click to view] > 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. Of course deadlocks can still occur. [quoted text, click to view] >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 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. [quoted text, click to view] >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. 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. [quoted text, click to view] >Even Oracle wrote a nice PDF about: >http://otn.oracle.com/products/manageability/database/pdf/SSOracletechcomparison1.pdf Well of course! [quoted text, click to view] >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... Clues are good. Joshua Stern [quoted text, click to view] >
>regards, Guido Stepken
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) [quoted text, click to view] "Guido Stepken" <g.stepken@t-online.de> wrote in message http://otn.oracle.com/products/manageability/database/pdf/SSOracletechcomparnews:bfhn0o$vav$00$1@news.t-online.com... > Hello, out there ! > > After having read several documents 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 document: > 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 analyses, 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: > ison1.pdf [quoted text, click to view] > > 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 >
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 [quoted text, click to view] "Guido Stepken" <g.stepken@t-online.de> wrote in message
news:bfhteo$9n9$07$1@news.t-online.com... > 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: > > 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" <g.stepken@t-online.de> wrote in message > > news:bfhn0o$vav$00$1@news.t-online.com... > > > >>Hello, out there ! > >> > >>After having read several documents 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 document: > >>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 analyses, 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/SSOracletechcompar > > ison1.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 ?
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 [quoted text, click to view] "Guido Stepken" <g.stepken@t-online.de> wrote in message news:bfhteo$9n9$07$1@news.t-online.com... > 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: > > 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" <g.stepken@t-online.de> wrote in message > > news:bfhn0o$vav$00$1@news.t-online.com... > > > >>Hello, out there ! > >> > >>After having read several documents 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 document: > >>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 analyses, 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/SSOracletechcompar > > ison1.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 > >> > > > > > > >
Hello, out there !
After having read several documents 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 document: 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 analyses, 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
Did you try creating a view which contains this?
Like: create view vw_sales as select * from sales (READPAST) GertD@SQLDev.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. [quoted text, click to view] "Guido Stepken" <g.stepken@t-online.de> wrote in message
news:bfin1p$69s$04$1@news.t-online.com... > 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: > > 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" <g.stepken@t-online.de> wrote in message > > news:bfhteo$9n9$07$1@news.t-online.com... > > > >>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: > >> > >>>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" <g.stepken@t-online.de> wrote in message > >>>news:bfhn0o$vav$00$1@news.t-online.com... > >>> > >>> > >>>>Hello, out there ! > >>>> > >>>>After having read several documents 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 document: > >>>>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 analyses, 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. > >>>>
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 [quoted text, click to view] > MVCC, the current "state of the art" in SQL programming. MVCC is definitly not state of the art, it was Oracle solution to lockingbefore 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. [quoted text, click to view] "Guido Stepken" <g.stepken@t-online.de> wrote in message news:bfhn0o$vav$00$1@news.t-online.com... > Hello, out there ! > > After having read several documents 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 document: > 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 analyses, 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 >
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 [quoted text, click to view] Martin Nicholson wrote:
> 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" <g.stepken@t-online.de> wrote in message > news:bfhn0o$vav$00$1@news.t-online.com... > >>Hello, out there ! >> >>After having read several documents 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 document: >>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 analyses, 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/SSOracletechcompar > ison1.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,
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 [quoted text, click to view] "Guido Stepken" <g.stepken@t-online.de> wrote in message news:bfhvr5$105$05$1@news.t-online.com... > 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: > > 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, the current "state of the art" in SQL programming. > > > > 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" <g.stepken@t-online.de> wrote in message > > news:bfhn0o$vav$00$1@news.t-online.com... > > > >>Hello, out there ! > >> > >>After having read several documents 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 document: > >>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 analyses, 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 > >> > > > > > > >
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 [quoted text, click to view] > 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. > > >>MVCC does things different. Whenever a transaction begins, a virtual >>copy of the database/tables/... is made instantly (that goes in nearly >>zero time), > > > Nothing is free, either the overhead occurs when the copy is made, or > when a concurrency issue arises. > Of course deadlocks can still occur. > > Clues are good. > > Joshua Stern
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 [quoted text, click to view] Jacco Schalkwijk wrote:
> 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, the current "state of the art" in SQL programming. > > 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" <g.stepken@t-online.de> wrote in message > news:bfhn0o$vav$00$1@news.t-online.com... > >>Hello, out there ! >> >>After having read several documents 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 document: >>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 analyses, 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 >> > > >
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 [quoted text, click to view] Martin Nicholson wrote:
> 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" <g.stepken@t-online.de> wrote in message > news:bfhteo$9n9$07$1@news.t-online.com... > >>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: >> >>>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" <g.stepken@t-online.de> wrote in message >>>news:bfhn0o$vav$00$1@news.t-online.com... >>> >>> >>>>Hello, out there ! >>>> >>>>After having read several documents 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 document: >>>>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 analyses, 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/SSOracletechcompar > >>>ison1.pdf >>> >>>
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 [quoted text, click to view] Jacco Schalkwijk wrote:
> 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" <g.stepken@t-online.de> wrote in message > news:bfhvr5$105$05$1@news.t-online.com... > >>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: >> >>>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, the current "state of the art" in SQL programming. >>> >>>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" <g.stepken@t-online.de> wrote in message >>>news:bfhn0o$vav$00$1@news.t-online.com... >>> >>> >>>>Hello, out there ! >>>> >>>>After having read several documents 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 document: >>>>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 analyses, 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: >>>> >>> >>>
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 Documentation Builder Document 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 [quoted text, click to view] "Guido Stepken" <g.stepken@t-online.de> wrote in message
news:bfhvr5$105$05$1@news.t-online.com... > 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: > > 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, the current "state of the art" in SQL programming. > > > > 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" <g.stepken@t-online.de> wrote in message > > news:bfhn0o$vav$00$1@news.t-online.com... > > > >>Hello, out there ! > >> > >>After having read several documents 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 document: > >>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 analyses, 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 > >> > > > >
[quoted text, click to view] "Jacco Schalkwijk" <NOSPAMjaccos@eurostop.co.uk> wrote in message news:OeuIkD#TDHA.1576@TK2MSFTNGP12.phx.gbl... >The next version of SQL Server will have MVCC btw, but it is one of the >features I can be bothered about the least. But will be brother by the most (see below). [quoted text, click to view] >>"Guido Stepken" <g.stepken@t-online.de> wrote in message arison1.pdf>>news:bfhn0o$vav$00$1@news.t-online.com... >> >> Even Oracle wrote a nice PDF about: >> >>http://otn.oracle.com/products/manageability/database/pdf/SSOracletechcomp Wonder: Did they really mean to say "highly intuitive unlike Oracle". Wonder: Did they really mean for the recovery from human errors part to do so realistic? The recovery itself is riddled with human error and then goes onward to brag about what a good job was done. Wonder At: How lucky you are the times the "recovery" fails and is rolled back vs how unluck you are the times the "recovery" is committed. Bye, Delbert Glass
[quoted text, click to view]
Greg Obleshchuk wrote: > 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. > > Fine, what is with a transaction, going over 3 days, e.g. And you do your daily backup. Your transaction log ist marked for these 5 hours ob backup, e.g. Then you have a server crash. You have to restore the whole database. According to your information, you have a database, and a part of a transaction inserted into your database. happy debugging of your data sets. regards, Guido Stepken
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |