| Author |
Topic |
|
venkath
Posting Yak Master
202 Posts |
 Posted - 2008-05-20 : 08:15:57
|
| Hi AllIs there a way in SQL to query the data with out locking.My intention is to read commited data with out locking Thanks |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-05-20 : 08:32:29
|
| What you are asking is in conflict with each other. If there is no locking, SQL Server can't ensure data is read committed and vice versa.What you are trying to achieve?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-20 : 08:35:28
|
Maybe he means others commited data without himself putting a lock in the data?Read in Books Online about NOLOCK query/table hint.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-05-20 : 08:38:44
|
| But with NOLOCK hint, he can't be sure he is seeing read committed data.Anyway, what's wrong with having shared lock that SELECT places? its not going to block other processes? What's your intention in avoiding locks?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
venkath
Posting Yak Master
202 Posts |
Posted - 2008-05-20 : 08:48:38
|
| share lock will not allow other transactions to modify the data. So all the other transactions are waiting until the shared lock is released by the SELECT statement.My intention is to avoid shared lock while querying the data or allow modifications while the shared lock exist. so that there will not be any delays in the transactions.. |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-05-20 : 08:56:45
|
| In that case use NOLOCK hint as suggested by Peso, but be warned that you may see phantom rows since all you will get is uncommitted data.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
venkath
Posting Yak Master
202 Posts |
Posted - 2008-05-20 : 09:08:16
|
| Yes..NOLOCK hint will not have any locks on the tables.But it will give uncommitted data . But I would like to get only committed data that exist before I execute the SELECT statement.Is it Possible ?Any isolation level that supports this kind?Thanks |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-05-20 : 09:15:46
|
| You can use new SNAPSHOT ISOLATION level to achieve this, if you are using SQL Server 2005.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-20 : 09:52:39
|
quote:
Originally posted by harsh_athalye
You can use new SNAPSHOT ISOLATION level to achieve this, if you are using SQL Server 2005.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED"
This is good option.It avoids reader/writer blocking.But remember it does row versioning in TempDB. |
|
|
|
|
Read commited data with out lock