You need to read up on transactions. Don't think in terms of locking, think in terms of what operations are permitted to occur concurrently. The easiest, but least performant, method is to use the Serializable transaction isolation level. This prevents all updates to data you've read or updated since the beginning of a transaction until you commit the transaction. SQL Server simply blocks any client trying to make a modification, or read one of your modifications, until your transaction is committed (if using locking, rather than row versioning). There are lower isolation levels that you should read up on carefully before using to ensure that the effect is the desired one. The default level is ReadCommitted, which means that you cannot read uncommitted updates which another transaction has made (and if SQL Server is using locking rather than row versioning, blocks your connection until the changes are either committed or rolled back). You should explicitly wrap your operations that need to have a consistent state in a BEGIN TRANSACTION/COMMIT TRANSACTION pair of statements, or use a SqlTransaction object. If an error is returned from a data-modification operation, you should roll back the transaction and abandon any further statements. At any point if you decide you need to discard your operations, you can roll back (note that everything you did in the transaction is rolled back). Be aware that a second connection opened by your application is treated as a separate client and you can end up blocking yourself. This does not apply to SQL Server 2005 with the Multiple Active Result Sets (MARS) feature enabled. Also be aware that if you perform operations in a different order in one transaction compared to another, you can end up with a deadlock. In this case, SQL Server will decide to kill off one of the transactions so that another complete. The transaction that is killed will be rolled back. Sometimes you need to lock a record on a logical level for updates and still permit other users or clients to read it. Here you will have to roll your own - SQL Server does not provide support for this. It's important for all database developers to understand transactions and isolation if the database is going to be used concurrently, as the vast majority of server-type databases will be.
Stability. What an interesting concept. -- Chris Maunder
