Isolation Level: Read Uncommitted

Dear experts:


I know that read committed allows reading of dirty read.
If I place read committed in a table, does this mean that I may not read uncommitted transaction by others or that others may not read my uncommitted transactions?

When you enter a data fora newly hired employee,  which isolation may be the best choice for you?    

Who is Participating?
k_murli_krishnaConnect With a Mentor Commented:
Please refer:

One cannot place isolation level in/on a table. They are assigned to database, connection/session, transaction and SQL statement. How table is involved is it is the main object and also throws up the charcteristics of isolation levels when one operates against their structure/data via locks, operation as a transaction and commit/rollback to end the transaction.

if you place read committed for database/transaction/SQL statement, if INSERT/UPDATE/DELETE in other transaction have placed a lock on the table/block/row that you want to read then it wont come through. But if it is within your own transaction it will since you acquire the lock and hence it does not become a dirty read.

Because I am entering data, not reading, I may not place any isolation level.  Am I right?
Yes, if you are always doing INSERT/UPDATE/DELETE and no SELECT, then for your statements you need not specify an isolation level but database & transaction isolation level are fixed at a more global level till they are changed again. So, the other person reading will not be able to do so unless database and if not then transaaction and if not then query isolation level is set to uncommitted read.

When you enter a data fora newly hired employee,  which isolation may be the best choice for you?
Go with the default i.e. Read Committed. If you want more strict isolation, go for Repeatable Read/Serializable. 2 new isolation levels in SQL Server 2005. Go through the link that I gave on top. It describes in more detail with examples.
dutaAuthor Commented:
Because I am entering data, not reading, I may not place any isolation level.  Am I right?

The most important question is:

While I am querying with uncommitted read isolation level in my table, are others not allowed to read uncommitted data in my table OR  am I not allowed to read uncommitted data in others?

dutaAuthor Commented:
Thank you so much for your kind, clear explaanation.

By the way, when should use ""repeatable Read" and  when "Serializable"?

I think there is a very fine distinction.

If you provide me with specific real-world situations, it may be very helpful.

Thanks again!
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

dutaAuthor Commented:
If I use "Read Uncommitted,"   I may read others' uncommitted transaction, and others may read my uncommitted transaction. In others, all users in a database may read others' uncommitted transaction actions. Am I right?
And  an isolation level is placed in the DB level, and it affects the entire database. Am I also right?


About READ UNCOMMITTED your are absolutely correct. And  an isolation level is placed in the DB level, and it affects the entire database. Am I also right? Not entirely. Isolation level is places at DB/session or connection(only some RDBMS support this in different ways)/transaction/statement(only some RDBMS support this) level. Against database there are sessions/connections and in them there are transactions and in them in turn statements.

So DB isolation level applies for everything/all but the rest for the particular session or connection/transaction/statement. You must have an isolation level for database but for the remaining 3 it is optional to override that of database for the specific ones for which a different isolation level is set. Setting same isolation level for these 3 as to that of DB has no meaning. Better to stick to default isolation level for DB unless situation demands another one. Isolation level specifies or makes implement the degree to which access of tables/views are isolated from access within session/transaction/statement by DDL(only is autocommit can be set to false)/DML statements by making use of different kinds of locks (main classification - write/exclusive & read/shared), levels of locks (table/block/row) and commit/rollback both of which release locks allowing other sessions/transactions/statements to acquire what they can and should through basically operation/statement(CRUD - CREATE(INSERT)/READ(SELECT)/UPDATE/DELETE).

Transactions i.e. from BEGIN TRANSACTION to COMMIT/ROLLBACK TRANSACTION in SQL Server and Sybase OR after acquiring connection first statement till COMMIT/ROLLBACK. This is provided autocommit is set to false even in case of Java - JDBC or any other programming language. In procedure, transaction is ended when COMMIT occurs on end/exit of procedure or when COMMIT/ROLLBACK is encountered outside/within exception => error/warning (represented differenly in different RDBMS (SQLCODE/SQLSTATE/@@ERROR etc.) handling.

Transaction has ACID property i.e. atomicity, consistency, integrity, durability. Please go through these. Integrity i.e. its level is maintained by isolation level via locks placed/acquired and released by different operations on different objects data) and hence for Read Uncommitted we have less integrity and dirty reads. But this isolation level which is least stringent leads to maximum performance since read is neither acruired nor released and performance keeps on decreasing with stringency similar to its decrease on increasing normalization of database design.

Off course, isolation level(s) of only DB or different ones for DB and one or more of the other three and whether transaction is maintained or not with autommit false/true, CRUD operations on data and concurrency level which is a subset of scalability decide lock waits, deadlocks and lock contention. There is no concept of one database being isolated from the other due to isolation level of both the databases. This is since locks acquired against objects of one database can be released/contend with locks sought by CRUD operations from other database which can occur only on objects of this other database. This is true even if one has distributed/federated computing across databases of same/disparate type using db links in Oracle, nicknames in DB2 and four part name in SQL Server/Sybase i.e. server.database.owner.objname since it is for objects within a single database against which operations are isolated from each other using different kinds of locks for different operations at different levels that are acquired/released as required from/by operations based on which rows of tables/columns are affected by which kind of operations.

For example if one connects to only DB1 and fires a SELECT from object in schema of DB1 and from another object in schema of linked DB2 then this select acquires read/shared locks on either of these objects only when write/exclusive locks are not already present/acquired i.e. there is not lock contention at row/block/table level. Operations can be from anywhere but it is the type and level of lock against most importantly the specific object which matters.
Here is gist of main four isolation levels of SQL Server 2005 (total 6 - 2 new added) from above link.
Read Uncommitted Isolation Level
This is the lowest level and can be set, so that it provides higher concurrency but introduces all concurrency problems; dirty-reads, Lost updates, Nonrepeatable reads (Inconsistent analysis) and phantom reads.
Read Committed Isolation Level
This is the default Isolation Level of SQL Server. This eliminates dirty-reads but all other concurrency related problems. Lost updates example is given. Understand the remaining 2 concurrency problems and code examples of your own.

Remeber that database design i.e. level of normalization is not in anyway related to isolation levels, locks, deadlocks, lock waits, connection/session, transaction, statements but to database, its objects and their data if any. While here we talk about concurrency issues, there we talk about consistency issues. While here we get concurrency problems; dirty-reads, Lost updates, Nonrepeatable reads (Inconsistent analysis) and phantom reads, there, we get redundancy => inconsistency and insert/update/delete anamolies both of which are indirectly/reverse proportional to degree/level of normalization.
Repeatable Read Isolation Level
This Isolation Level addresses all concurrency related problems except Phantom reads. Unlike Read Committed, it does not release the shared lock once the record is read. It obtains the shared lock for reading and keeps till the transaction is over. This stops other transactions accessing the resource, avoiding Lost Updates and Nonrepeatable reads.
Serializable Isolation Level
This is the highest Isolation Level and it avoids all the concurrency related problems. The behavior of this level is just like the Repeatable Read with one additional feature. It obtains key range locks based on the filters that have been used. It locks not only current records that stratify the filter but new records fall into same filter.

Yes, you are correct, there is little difference between the last 2 isolation levels. But sometimes the one little difference can make all the big difference. Fantastic link though and thanks for getting it digged out.

dutaAuthor Commented:
Krishna,  I thank you very much for your very knowledgeable, kind, clear, patient  explanation.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.