#0184-SQL Server-Quiz-Which database does not follow one of the ACID properties? Prize: Two (2) Pluralsight training codes!


Any RDBMS system follows a certain set of general principles that are geared towards ensuring the integrity of the data residing within the database. These fundamental principles are collectively known as ACID properties, which every database within an RDBMS system should confirm to.



  • Atomicity:

    • At a transaction level, this is the all-or-nothing approach, i.e. either all changes are committed or none – one cannot have a partially committed transaction
    • Example: A parallel can be established with the banking industry – when transferring funds from one bank account to another, it is imperative that the amount of money deducted from the source account are credited to the destination account. It is not acceptable to have a deduction from one account, but no credit to another account (that would be a scam!). If something goes wrong during the transaction, the rollback needs to be triggered in both systems – source and destination
    • Similarly, all RDBMS systems need to ensure that if something goes wrong within any transaction, an “undo” or “rollback” happens for the non-committed transaction

  • Consistency:

    • Whenever any transaction is “committed”, the effect of the transaction should leave the database in a logically consistent state
    • Example: When adding sales data into a database, it would be logically inconsistent to enter a sales order without having a corresponding customer record. Through the use of foreign keys, the RDBMS ensures that one cannot leave the database in a logically inconsistent state when committing a transaction

  • Isolation

    • Each transaction should be isolated (or protected) from the effects of other concurrently running transactions
    • This means that although multiple transactions are running in parallel, the net effect of the execution of the execution should be identical to that of executing all transactions in series (i.e. one after another in some serial order)
    • Example: Taking our banking example further, let’s assume that John wants to transfer $100 to Jack’s account. Jack wants to transfer $75 to Steve’s account. Both are separate transactions, but the net effect of these transactions is that Jack will end up with $25 more than his base balance ($x). Here’s how: [($x + $100) – ($75)] = $x + $25. If this is not the end result, the system is no good

  • Durability

    • Durability simply means that once a transaction has been completed, it’s effect must be persisted within the database irrespective of the state of the system (i.e. even in case of a system crash, the data must be permanently committed to disk upon recovery)
    • Example: Continuing our example of the banking system, if the system goes down in the middle of the banking transaction, Atomicity would ensure that uncommitted transactions are rolled back. However, if the system goes down after completion of the transaction, then changes made to all accounts involved must remain as-is when the system comes back online

The Question – Which SQL Server database does not follow one of the ACID properties?


Every user database within Microsoft SQL Server follows these ACID properties. However, there is one special case – one database which does not support one of the ACID properties. The question today is:



Which SQL Server database does not follow one of the ACID properties?


Bonus Questions!!!


Here are two (2) bonus questions which you can answer – the bonus questions also contribute to the prize-winning questions!



  1. Based on the answer to the basic question, name the ACID property which the database does not follow
  2. Explain, in your own words, the reason why you believe the database does not follow the said ACID property

Rules


The rules are very simple:



  1. You can answer these questions till Wednesday 22:00hrs (IST)/Wednesday 12:30hrs (US-EDT) – August 08, 2012
  2. Answers will be shared and winners declared in my post on Thursday, August 09, 2012
  3. You can answer multiple times – however, at the end of the day, only one entry would be considered
  4. Follow the following on Twitter!

  5. Winners will be chosen randomly

Remember: Participation is always more important! Make sure you reply to the quiz even if it’s just a guess! 


Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

9 thoughts on “#0184-SQL Server-Quiz-Which database does not follow one of the ACID properties? Prize: Two (2) Pluralsight training codes!

  1. Gaurang Patel

    1.Tempdb does not Follow all ACID rules.

    Answer of Bonus Question

    1.Tempdb Does not follow property Durability.

    2.Tempdb is created whenever sql server is restarted so all changes in tempdb are gone.what if system is crash and starts again we will have news tempdb.

    Like

    Reply
  2. Olga Medvedeva

    It’s **tempdb** database and it doesn’t follow **Durability** property of ACID. The reason is this database is cleared on every SQL server start, so there is no need to recover data in it

    Like

    Reply
  3. Hardik Doshi

    **Nice question Nakul.**

    My answer is: **TempDB** (Does not follow: **Durability**)

    **Reason:** SQL Server build/create ‘TempDB’ whenever we restart the server/service. If you are doing some calculation using TempDB (you are in middle of something) and suddenly SQL Server crashes and you have to restart the server/service then all your temporary data stored in TempDB is no longer available. You have to start your process once again.

    It is correct behavior (logicall as well) as TempDB is created for temporary purpose only. So, whenever connection has been closed, local temp tables are also dropped automatically.

    Like

    Reply
  4. ErikEckhardt

    All user databases in SQL Server must and do support ACID. All that’s left are the system databases, which are:

    – **master** – the repository for system stored procedures, schema views, functions, DM views, and so on.
    – **model** – an empty, basic database that new databases are copied from.
    – **msdb** – the location where backup data, jobs, SSIS packages, and other server-level data is stored.
    – **tempdb** – a frequently-used data storage location that holds metadata about temporary object such as temp tables and table variables, as well as their data if not kept in memory. Also, when the server needs work tables, it stores them in tempdb. For example, if a query needs a hash plan and exceeds its memory grant, it will “spill” to tempdb. Tempdb is also used to maintain cursors, and it holds versions of table rows generated by snapshot isolation & row versioning isolation levels, online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

    Only one of these databases has any reason for or allowance of not supporting all the properties of ACID: *tempdb*.

    1. The ACID property it does not support is *durability*.
    2. The reason it does not maintain durability is that in *tempdb*, data is temporary by nature, and upon certain conditions this data will be abandoned and not persisted/permanently committed to disk:

    – When the session that created a temp table disconnects, the temp table is destroyed. Note: this includes temporary stored procedures, as well as all normally-named objects that are created while using the tempdb database, e.g. `USE tempdb; CREATE TABLE a (b int);` actually creates a temporary table which is destroyed upon disconnect.
    – When the session that created a global temp table disconnects, and all the sessions referring to it disconnect, the global temp table is destroyed.
    – When all the other uses for tempdb mentioned above are no longer needed, the associated data is abandoned:
    – a work table is no longer needed
    – row versions no longer need to be maintained
    – a cursor is closed
    – MARS are closed
    – an AFTER trigger completes, and so on.

    Note that when data in tempdb is “destroyed” this does not mean that all the data is immediately overwritten, but that the metadata about the data is deleted. This is true in any database, not just tempdb. When a table is dropped, only the minimum I/O is done necessary to “deallocate.”

    Like

    Reply
  5. Nirav

    Answer is :TempDB Database.

    because when the SQl Server restarted We will get new TempDb ,all changes are gone.

    and tempDb does not follow “Durability” property of ACID.

    Like

    Reply
  6. jlnoll

    The answer is TempDB. TempDB does not meet the Durability property. TempDB is created at SQL Server startup from a copy of Model database.

    Like

    Reply
  7. Olga Medvedeva

    **sk2000**, first of all the question was:

    > Which SQL Server database does not
    > follow one of the ACID properties?

    SQL Server is RDBMS, not NoSQL DBMS. And one more thing: NoSQL is a movement and the BASE (Basically Available, Soft state, Eventual consistency) claimed to be the opposite of ACID. But there are some NoSQL databases that have ACID transactions (for example, [Berkeley DB][1]; [GT.M.][2] and others).

    [1]: http://en.wikipedia.org/wiki/Berkeley_DB
    [2]: http://www.fisglobal.com/products-technologyplatforms-gtm

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s