November 28, 2008 at 8:26 am
Hi,
I've been doing a bit lately with passing table parameters into stored procedures, and I was wondering how the transaction isolation level is managed during this process.
As I understand it, the actual data is not passed into the procedure, rather a pointer to the data is, and so is it possible to set the isolation level to be, for example, read uncommitted?
Thanks,
Jackal
November 28, 2008 at 8:51 am
Hmm, I thought that table variables and parameters were not transactional?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 28, 2008 at 8:55 am
Hi,
They may well be, but surely if the pointer goes to the data, if a change is being made to the data then will the pointer have to wait for any locks to be released, or will it operate in read uncommitted manner, otherwise I would not be able to ensure the data I am getting is correct.
Does that make sense. I've confused myself! :hehe:
November 28, 2008 at 9:01 am
They're session-local aren't they? Who else could be accessing them?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 28, 2008 at 9:04 am
Yep, your right.
I suppose when I populate the table variable, it's at that point any isolation level who be applied, then it becomes a parameter, and as you point out, is not subject to isolation levels anymore.
Thanks for you help, guess I just needed to talk it through to understand it better.
Much appreciated.
Jackal
November 28, 2008 at 9:27 am
Well if you mean "populate the table variable" from T-SQL, then the table variable is already non-transactional.
However, if you mean "populate the table variable" from client code, then it becomes non-transactional as soon as you transmit it, AFAIK.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 28, 2008 at 9:56 am
Thanks for you help,
Jackal.
November 28, 2008 at 10:07 am
Glad I could help...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply