Quote:
quote:
... i am not clear when to use which isolation level and locking hints.
|
The four different transaction isolation levels offer increasing concurrency in multiuser environments at the cost of potential integrity issues primarily centering around the repeatability of data access within a transaction. More restrictive levels hold more restrictive locks for longer periods, so while data integrity is better maintained, it is at the cost of decreased concurrency.
For example, with the default level, READ COMMITTED, you can read a set of rows (as defined by some WHERE clause) then read the same set again within your transaction and find that rows have been added or deleted or updated in the interim by other processes. You won't see rows that are in the process of being changed by some other process's transactions (you
would if you set your level to READ UNCOMMITTED), but you will see their results when their transaction is committed.
The next level "up", REPEATABLE READ, prevents you from seeing any other updates, but still allows the possibility of seeing rows which have been inserted or deleted by other processes. The repeatability of reads is at a cost, though, because the way this is done is that exclusive locks are placed on the data, preventing other users (who also attempt REPEATABLE READS) from even reading the data.
The highest level, SERIALIZEABLE, means that even inserts and deletes are prevented from occurring. While this offers the highest level of data integrity, it does so at the cost of the lowest level of concurrency. This is because the exclusive locks are set on a
range of rows (possibly the entire table), so concurrency is reduced basically to the point of single threading access to the table(s) in question.
The question of which level to use when is not a simple one to answer, since it all depends on the requirements of your application. For browsing-type functions you may be quite happy with the lowest level, unless, of course, major business decisions are made based on the data presented. If you are offering credit terms based on a current balance, you might not want to have your credit officer base that decision on data which doesn't necessarily reflect the most recent purchase by the customer. Marketing analysis, on the other hand, may be quite reasonable working with data which is not quite the most up-to-date. Making sure each operation is at the appropriate level insures that they don't interfere with one another inappropriately. This can require some thought.
IMO, you should be careful with locking hints. I tend to use these only in response to specific concurrency/integrity issues which arise. It's too easy to actually make things worse by the inappropriate use of hints in the wrong place.
Quote:
quote:
1) Automatic generation of Custom ID (for various documents) under
multiuser enviroment.
|
Here, you
certainly don't want two documents to be assigned the same ID (I'm assuming you are not talking about
identity columns as the ID), so SERIALIZEABLE is probably the appropriate level to use. Because this is the most restrictive level, you'll want to be certain the the duration of this transaction is as short as possible. Set the transaction level, begin the transaction, assign the ID and commit as fast as you can. Other users will simply wait on the lock, so to insure reasonable concurrency you want to hold the lock for as short a period as possible.
Quote:
quote:
2) what if a single record is accessed by two user at the same time.
|
Depends on what you want; see above.
Quote:
quote:
can u suggest me some good book by Wrox Press or any other press
which deals with such issues...
|
Best book I've found on this is
Inside SQL Server 2000 by Kalen Delaney, published by Microsoft Press.
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Multiuser Considerations In SQL
