Consistency Problems

A transaction must exhibit ACID properties but apart from ACID properties they can exhibit other properties.These properties are called consistency problems.They are DIRTY READS, NONREPETABLE READS,PHANTOMS and LAST UPDATES.
An application should be avoid LAST UPDATES the other problems can ignore depending on the application.
You can determine these behaviours to allow or disallow by using Isolation levels.

LAST UPDATES :

Last Updates occur when two process reads the same data and both manipulate and update the data at the same time.The second process might overwrite the first update completely.

DIRTY READS :

It occurs when one process reads the uncommitted data . If one process has changed the data but not yet committed the second process reads the data in an inconsistent state.
By default DIRTY READS are not allowed.
By using the Isolation level READ UNCOMMITTED you can see DIRTY READS.
NON REPEATABLE READS :
It occurs when a process might get different values reading the same data in two seperate reads with in the same transaction.These are also called Inconsistent analysis.

Phantoms :

 It can happen only when a query with a action such as Where <> 10 is involved.
If two select operation using the same action in the same transaction return different number of rows.

The behaviour of  your transaction depends on which Isolation level.
You can set the isolation level by using Set Transaction Isolation Level (isolation level name)
Your concurrency models determines how the isolation level is implemented.