Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

DBAs on ACID

By Tony Davis,

I am still reasonably surprised at how few developers seem to grasp fully the difficulties of ensuring the consistency and transactional integrity of the data stored in SQL Server, when that data is subject to concurrent queries and modifications, and the implications this has on the level of required database testing.

Most developers will be able to tell you about the ACID properties of transactions, but have a touching faith that SQL Server will just automatically take care of their enforcement. In most cases, it will, but in his excellent book, Defensive Database Programming, Alex Kuznetsov proves that it's far from guaranteed. When running under the READ COMMITTED transaction isolation level, the default for normal SQL Server operation, not only is it possible for a reporting query to return inconsistent results, if another transaction is modifying the same data, but that the situation is quite easy to reproduce. Likewise he demonstrates the 'lost updates' and constraint violations that can and do occur when data is subject to concurrent modifications, and how certain T-SQL patterns, such as IF EXISTS(…) THEN are unreliable under such conditions.

Any DBA who has been bitten by these subtle data integrity issues, or reports that erratically produce the wrong results, will know how much careful testing is involved in mitigating them. The database needs to be carefully, painstakingly designed, with appropriate levels of normalization. It needs to be thoroughly stress tested under concurrent access, with queries representative of a typical workload, against volumes and distributions of data that closely match reality. Query performance, and the stability of execution plans, needs to be closely monitored under various load and conditions. Extra measures to preserve data integrity such as use of higher isolations levels, additional constraints or hints, must be applied as necessary. Any subsequent change to the database, however seemingly-subtle, necessitates careful regression testing.

This is the reason why databases are so resistant to many of the Agile development practices that allow and encourage the changing of code freely and often.

Cheers,

Tony.

Total article views: 242 | Views in the last 30 days: 1
 
Related Articles
ARTICLE

Transaction Isolation Levels

An examination into how the various transaction isolation levels affect locking (and blocking)

ARTICLE

Transaction Isolation Level

A short description of the transaction isolation levels in SQL Server

FORUM

Transaction Isolation Level SQL Server 2005

Transaction Isolation Level SQL Server 2005

FORUM

The transaction log for database 'tempdb' is full. Msg 9002, Level 17, State 4, Line 2

The transaction log for database 'tempdb' is full. Msg 9002, Level 17, State 4, Line 2

BLOG

DMV-11 : T-Log space used by transaction……..sys.dm_tran_database_transactions

sys.dm_tran_database_transactions DMV (Dynamic Management View), described by BOL as follows: http:/...

Tags
database weekly    
editorial    
transactions    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones