This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
Raj, Basically, you use transactions to make sure the database stays consistent. This is a very general question, so I'm not sure how to give you a more specific answer. Can you expand on what you are looking for?
Data concurrency issues are handled by setting the Isolation Level of the transaction, basically there are following four types of Isolation level we can set for any transaction.
Read Uncommitted Read Uncommitted is No-Lock situation where one transaction can disturb other or it can create deadlock situation, it is generally not recommended, it is used only when you need some speed in your transaction.
Read Committed this is the default isolation level for most of the database servers and highly recommended. For read committed every statement in a transaction is locked until they are executed, when their execution is completed the lock is released, for insert, update or delete the transaction is locked until committed or rolled back. Read Committed prevents Dirty Reads situations when one transaction changes the data and the other transaction read the modified data and the first transaction the rollbacks so the second transaction only gets the wrong data.
Repeatable Read this isolation level is set for the situations where there is possibility of unrepeatable reads . When a transaction is set to Repeatable read then a transaction is locked until a transaction is committed or rolled back
Serializable This is the highest level of isolation generally not recommended it takes care of all the consistency problems (dirty reads, unrepeatable reads, phantoms) except lost updates. The Serializable isolation level allows a transaction to acquire read locks (if it only reads rows of data) or write locks (if it inserts, updates, or deletes rows of data) for the entire range of data that it affects. For example, if a transaction using the Serializable isolation level executes the SQL statement "SELECT * FROM ORDERS", the range is the entire ORDERS table. Therefore, the transaction acquires a read lock for the entire table and no new rows can be inserted into it until the transaction releases the lock. [ March 07, 2006: Message edited by: Sameer Jamal ]