Solved

Isolation Level:  Read Uncommitted

Posted on 2009-04-02
7
1,299 Views
Last Modified: 2012-05-06
Dear experts:

Hi!

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?    

Thanks!
0
Comment
Question by:duta
  • 4
  • 3
7 Comments
 

Author Comment

by:duta
ID: 24056359
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?

Thanks!
0
 
LVL 17

Accepted Solution

by:
k_murli_krishna earned 500 total points
ID: 24056549
Please refer:
http://www.sql-server-performance.com/articles/dba/isolation_levels_2005_p1.aspx

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.
 
0
 

Author Comment

by:duta
ID: 24056714
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!
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:duta
ID: 24056725
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?

Thanks!
0
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 24062701
Thanks.

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.
0
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 24062851
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.

0
 

Author Comment

by:duta
ID: 24063929
Krishna,  I thank you very much for your very knowledgeable, kind, clear, patient  explanation.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now