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
- 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
- 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
- Whenever any transaction is “committed”, the effect of the transaction should leave the database in a logically consistent state
- 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
- Each transaction should be isolated (or protected) from the effects of other concurrently running transactions
- 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
- 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)
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!
- Based on the answer to the basic question, name the ACID property which the database does not follow
- 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:
- You can answer these questions till Wednesday 22:00hrs (IST)/Wednesday 12:30hrs (US-EDT) – August 08, 2012
- Answers will be shared and winners declared in my post on Thursday, August 09, 2012
- You can answer multiple times – however, at the end of the day, only one entry would be considered
- Follow the following on Twitter!
- Pluralsight (Handle is: @pluralsight)
- BeyondRelational.com (Handle: @beyondrel)
- Me! (Handle is: @nakulv_sql )
- Pluralsight (Handle is: @pluralsight)
- 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.
SQLTwins by Nakul Vachhrajani 
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.
LikeLike
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
LikeLike
**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.
LikeLike
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.”
LikeLike
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.
LikeLike
The answer is TempDB. TempDB does not meet the Durability property. TempDB is created at SQL Server startup from a copy of Model database.
LikeLike
Answer is TempDB Database.
LikeLike
I agree with comments. Because answer is same in all the comments.
LikeLike
**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
LikeLike