Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transactions


Transactions

Author
Message
ppcx
ppcx
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 431
As a long-time Oracle DBA and a new MSSQL DBA I find this whole concept mind-boggling. If it hadn't been for the prior isolation-level question (which I missed) and then yesterday's (Guest) Editorial I would've gotten today's question wrong too.
LGibson69
LGibson69
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 88
I expected to get no records because the transaction was still open but, when I ran it on SQL 2008 I got an error message saying "Msg 208, Level 16, State1, Line 3. Invalid object name 'test_tran'. Since this is the actual result I got I selected "None of the above" as the correct answer and was told I was wrong. I think I should get my "point" because my answer was technically correct. :-)
Bob Griffin
Bob Griffin
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1167 Visits: 702
Good question because it made us think....I missed it, but I learned something which is really the point....It's very tough to cover all your bases on these questions.... :-D
JF1081
JF1081
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 322
Mine ran for a while with no results so being the impatient person I am I decided to see what would happen if I ran the first one again. Upon doing so it returned the first 2 rows (1 and 2) in the second query.
I am using SQL Server 2005.
h84liang
h84liang
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
dear Friends,

i have testing this question and return all values (1,2,3) if i execute it :-D

thx
SanjayAttray
SanjayAttray
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3953 Visits: 1619
Good question. I didn't read it correctly and executed in same window. Which gave me 1,2,3 but after it got wrong, then tried it in another window and it was still running.

Will see if it returns something after sometime.

SQL DBA.
SanjayAttray
SanjayAttray
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3953 Visits: 1619
h84liang (3/19/2009)
dear Friends,

i have testing this question and return all values (1,2,3) if i execute it :-D

thx


Dude, read question correctly. It say's execute it in another window and not the same.
Try again.

SQL DBA.
DPhillips-731960
DPhillips-731960
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1158 Visits: 801
Sometimes missing information in a question induces thought, teaches, and also helps one to permanently retain what was learned.

I think this was a great question, and also the points brought out in discussion.
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24167 Visits: 37929
LGibson69 (3/19/2009)
I expected to get no records because the transaction was still open but, when I ran it on SQL 2008 I got an error message saying "Msg 208, Level 16, State1, Line 3. Invalid object name 'test_tran'. Since this is the actual result I got I selected "None of the above" as the correct answer and was told I was wrong. I think I should get my "point" because my answer was technically correct. :-)


I am guessing you are running in a case-sensitive environment. I am too and had the same error. I corrected the second query to use the same EXACT name as the first. I then had the proper result (based on SNAPSHOT ISOLATION being OFF).

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
kevin.l.williams
kevin.l.williams
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3379 Visits: 1323
At first I was confused about why it would not return the already committed data and I did an experiment by changing the code. Very interesting question. Thanks!

--first window
CREATE TABLE Test_Tran(id int identity, col1 int)
ALTER TABLE Test_Tran ADD CONSTRAINT [pk_Test_Tran] PRIMARY KEY CLUSTERED (id ASC)
insert into Test_Tran values(1)
insert into Test_Tran values(2)
Begin Tran
insert into Test_Tran values(3)

--second window
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
select * from a_test_tran where id < 3

--results
id col1
----------- -----------
1 1
2 2

(2 row(s) affected)



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search