January 25, 2014 at 2:50 pm
L' Eomot Inversé (1/25/2014)
Hugo Kornelis (1/24/2014)
Tempdb is specifically designed to ensure that information will NOT survive a system crash.Yes, it is - and that means that the ability of a temporary table to survive commitment of the transaction in which it was created (and be used in a new transaction) is a violation of the D, as is the ability to place user tables other than temporary tables in tempdb. If tempdb couldn't hold non-temporary tables, and temporary tables were defined to have the scope of the transaction in which they were created, the fact that stuff in tempdb doesn't survive system start would not have any relevance to the D property of transactions. Defining temp tables in tempdb as having the scope of a client session, and allowing non-temp tables in tempdb, are probably bad things (and globally visible temp tables probably makes it even worse).
I'm not sure if I would call it bad things. Neither would I call the option of non-durable tables in Hekaton a bad thing. As long as a feature clearly documents that using it means sacrificing durability, I am okay with it. In the case of temporary tables, or putting any objects in tempdb, the letters "temp" are a dead giveaway. And non-durable tables have the lack of durability documented in their name and in the syntax ("durability = schema_only", if I remember correctly).
The whole popular hype of NoSQL databases is built around the idea of sacrificing one or more of the ACID properties for better performance. I don't see why the same would not be allowed for specific features in SQL Server - as long as ACID remains the default for standard tables.
January 26, 2014 at 11:23 am
Hugo Kornelis (1/25/2014)
I'm not sure if I would call it bad things. Neither would I call the option of non-durable tables in Hekaton a bad thing. As long as a feature clearly documents that using it means sacrificing durability, I am okay with it. In the case of temporary tables, or putting any objects in tempdb, the letters "temp" are a dead giveaway. And non-durable tables have the lack of durability documented in their name and in the syntax ("durability = schema_only", if I remember correctly).
I don't think having non-durable objects is a bad thing, but the way that SQL expresses them is a bad thing since it seems to me to leave them more open to abuse (or in other words to provide more scope for idiots to write stupidly bad programmes) than might other approaches to providing them.
The whole popular hype of NoSQL databases is built around the idea of sacrificing one or more of the ACID properties for better performance. I don't see why the same would not be allowed for specific features in SQL Server - as long as ACID remains the default for standard tables.
Of course SQL has a lot of history of improving performance by sacrificing some parts of some ACID properties. In fact every single one of the ACID properties is violated by standard features. This isn't a a bad thing where the features are well designed, but inevitable it provides scope for people to do some extremely stupid things.
Isolation is perhaps the main sacrificial goat here, and it's done quite tidily (although it's far from idiot proof). In the old days such slack isolation as READ COMMITTED was regarded as a very dirty trick to cheat on TPCA performance figures. READ UNCOMMITTED and WITH (NOLOCK) are perfectly clear and straightforward and no intelligent person will misuse them, but there is a lot of abuse of those two features. I can't remember what happened to the locking hint that said "ignore exclusively locked objects when reading" as opposed to "ignore exclusive locks when reading" - does SQL Server or indeed any surviving RDBMS support it? Almost all database applications run almost all the time with incomplete isolation (even SNAPSHOT isolation is not full isolation, only SERIALIZABLE is) and it hasn't been the end of the world.
As well as ISOLATION and DURABILITY, some features impair ATOMICITY: in SQL Server, the main thing that does this is variables (not just table variables) that can pass infomation from a transaction that rolled back to a subsequent transaction that may change its action depending on the variable. Of course this feature is absolutely essential for the logging needed to provide decent error management.
CONSISTENCY of course is thoroughly violated by the ability to disable constraints, to re-enable them without check (I think it's unfortunate that NOCHECK is the default), and to omit checking them when they are first created (fortunately not the default).
So DURABILITY isn't in any sense singled out by its violations in SQL Server.
Tom
January 26, 2014 at 12:55 pm
L' Eomot Inversé (1/26/2014)
I can't remember what happened to the locking hint that said "ignore exclusively locked objects when reading" as opposed to "ignore exclusive locks when reading" - does SQL Server or indeed any surviving RDBMS support it?
You mean the READPAST locking hint? Suported by SQL Server for as long as I remember. And actually quite useful for tables that implement, for instance, a list of work items.
January 27, 2014 at 2:07 pm
Hugo Kornelis (1/26/2014)
L' Eomot Inversé (1/26/2014)
I can't remember what happened to the locking hint that said "ignore exclusively locked objects when reading" as opposed to "ignore exclusive locks when reading" - does SQL Server or indeed any surviving RDBMS support it?You mean the READPAST locking hint? Suported by SQL Server for as long as I remember. And actually quite useful for tables that implement, for instance, a list of work items.
Yes, that's what I meant. I feel that it is useful in far more circumstances than the NOLOCK hint and it seems to attract less misuse. But for a long time now I've been avoiding locking hints so I'd forgotten what it was called (I only remember what NOLOCK is called because so often I've discovered it as the source of a bug and had to tell people to stop abusing it).
Tom
January 27, 2014 at 2:46 pm
L' Eomot Inversé (1/27/2014)
(I only remember what NOLOCK is called because so often I've discovered it as the source of a bug and had to tell people to stop abusing it).
I still believe that NOLOCK should have been called NORELIABILITY.
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply