concurrency question - 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 2000 Forums

SQL Server Development (2000)

concurrency question

Author

Topic

TomC
Starting Member

1 Post

Posted - 2006-10-06 : 00:41:09

Suppose process A is updating record #1 in table T.

By default, can other processes read record #1 while the updating is in progress ??

If the answer is Yes, then which value can they see - the old one or the new one ?

Thank you in advance.

Tommy

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-06 : 00:57:01

The default is Read Committed, meaning only read committed data.

You can read about Transaction Isolation Levels in SQL Server Books Online.

SQL Server 2000 Books Online
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp
SQL Server 2005 Books Online
http://msdn2.microsoft.com/en-us/library/ms130214.aspx

CODO ERGO SUM

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-10-06 : 04:06:11

By default process B must wait for process A to finish unless you specify that you want to read "dirty" data, ~~in that case you would get the old data~~ EDIT: there is no way to tell which data you will get...it depends on how far the transaction has come

SELECT Column1 FROM table WITH (NOLOCK)

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-10-06 : 04:15:06

A simple way to test it...open two windows in QA and in the first you type

CREATE TABLE tmp (ID int)
INSERT INTO tmp SELECT 1 UNION ALL SELECT 2

BEGIN TRAN

UPDATE tmp SET ID = ID + 1
WAITFOR DELAY '00:00:30'

ROLLBACK TRAN 

DROP TABLE tmp

and in the second you type

SELECT * FROM tmp WITH (NOLOCK)

Run the batch in the first window and then switch to the second and experiment by adding/removing the WITH (NOLOCK) part of the query...very simple test but it's a good illustration on how it works.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources