locks in SQL SERVER - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

locks in SQL SERVER

Author

Topic

AdiI
Starting Member

31 Posts

Posted - 2009-08-10 : 02:22:14

hello to all
my question is regarding SQL SERVER transaction locks. i'm an oracle/SQL DBA. i'm wonder why did not SQL server show the committed data to other session whereas in oracle it show committed values to all other sessions. i have created a table insert a value. i have insert another value but did not commit. now i have open another session and select table T but query did not return why? it must show one record as in oracle. when i select T with nolock command it show uncommitted in session 2. for better understadning i have posted my example plz help why SQL Server did not show committed values to other session

session 1
---------------
create table t (id int)
insert into t values (1)
select * from t
output
1

begin tran
insert into t values (2)
output
1
2
---------------
session 2
select * from T
not responding
if i issue select * from t with (nolock) it show uncommitted values too
output
1
2

adeel

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-10 : 02:48:04

you need to commit the transaction.

session 1
---------------
create table t (id int)
insert into t values (1)
select * from t
output
1

begin tran
insert into t values (2)
Commit

of course in your example, you shouldn't be using a transaction at all

AdiI
Starting Member

31 Posts

Posted - 2009-08-10 : 03:08:28

you did not get my point. i know i did not commit my question is there any way to view the table committed data whereas other session hold share lock on the table plz tell me if yes how can i do this. in oracle if u did not commit transaction you cant see uncommitted values it other session but can see committed values of that table

adeel

AdiI
Starting Member

31 Posts

Posted - 2009-08-10 : 05:32:12

no one explain it plz do explain this concept i' will be thanks for u

adeel

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-08-10 : 05:58:59

Hi

This used to monitor the locking activity in SQL SERVER
SELECT * FROM SYS.DM_TRAN_LOCKS

-------------------------
R..
http://code.msdn.microsoft.com/SQLExamples/
http://msdn.microsoft.com/hi-in/library/bb500155(en-us).aspx

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-08-10 : 06:57:53

In session 2 using:

select * from T WITH (READPAST)

will probably produce the results you are expecting.
Your original query is waiting for the XLOCK on Id = 2 to be released with
either a COMMIT or ROLLBACK.

If you use a SNAPSHOT isolation level your queries should behave in a similar way to Oracle.
(SQL Books online (BOL) explains the differences in the isolation levels.)

AdiI
Starting Member

31 Posts

Posted - 2009-08-10 : 07:09:01

thanks for reply but plz tell me which isolation level give me this privilege to see committed data in t without using any extra command. i have tried all isolation level but result is same provide useful link

adeel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources