Is it possible for SQL statements to execute concurrently within a single session in SQL Server? Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 23, 2019 at 00:00UTC (8:00pm US/Eastern)how to make a table unlocked within transaction blockAssociate a piece of data with a sessionSPIDs stuck indefinitely in suspended stateUnable to start a nested transaction for OLE DB provider “SQLNCLI11” for linked serverHow to avoid a “duplicate key” error?Executing SSIS Package from a stored procedure with different user privilegesRead/Write Deadlock in SQL ServerWhen exactly are multiple users unable to simultaneously run a stored procedure with a temp table?Is it possible to limit the scope of a SYNONYM in SQL Server?How to Set the Transaction Isolation level in a Table Value Multi Function
Time to Settle Down!
How to write the following sign?
How do I use the new nonlinear finite element in Mathematica 12 for this equation?
SF book about people trapped in a series of worlds they imagine
Is CEO the "profession" with the most psychopaths?
Hangman Game with C++
Most bit efficient text communication method?
Amount of permutations on an NxNxN Rubik's Cube
Take 2! Is this homebrew Lady of Pain warlock patron balanced?
Do wooden building fires get hotter than 600°C?
How to react to hostile behavior from a senior developer?
Is it fair for a professor to grade us on the possession of past papers?
Does the Weapon Master feat grant you a fighting style?
Generate an RGB colour grid
Why wasn't DOSKEY integrated with COMMAND.COM?
Is there any word for a place full of confusion?
If windows 7 doesn't support WSL, then what does Linux subsystem option mean?
What is a fractional matching?
How come Sam didn't become Lord of Horn Hill?
Effects on objects due to a brief relocation of massive amounts of mass
How do I find out the mythology and history of my Fortress?
How to write this math term? with cases it isn't working
What is the font for "b" letter?
Did Krishna say in Bhagavad Gita "I am in every living being"
Is it possible for SQL statements to execute concurrently within a single session in SQL Server?
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 23, 2019 at 00:00UTC (8:00pm US/Eastern)how to make a table unlocked within transaction blockAssociate a piece of data with a sessionSPIDs stuck indefinitely in suspended stateUnable to start a nested transaction for OLE DB provider “SQLNCLI11” for linked serverHow to avoid a “duplicate key” error?Executing SSIS Package from a stored procedure with different user privilegesRead/Write Deadlock in SQL ServerWhen exactly are multiple users unable to simultaneously run a stored procedure with a temp table?Is it possible to limit the scope of a SYNONYM in SQL Server?How to Set the Transaction Isolation level in a Table Value Multi Function
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I have written a stored procedure which makes use of a temporary table. I know that in SQL Server, temporary tables are session-scoped. However, I have not been able to find definitive information on exactly what a session is capable of. In particular, if it is possible for this stored procedure to execute twice concurrently in a single session, a significantly higher isolation level is required for a transaction within that procedure due to the two executions now sharing a temporary table.
sql-server sql-server-2012
add a comment |
I have written a stored procedure which makes use of a temporary table. I know that in SQL Server, temporary tables are session-scoped. However, I have not been able to find definitive information on exactly what a session is capable of. In particular, if it is possible for this stored procedure to execute twice concurrently in a single session, a significantly higher isolation level is required for a transaction within that procedure due to the two executions now sharing a temporary table.
sql-server sql-server-2012
add a comment |
I have written a stored procedure which makes use of a temporary table. I know that in SQL Server, temporary tables are session-scoped. However, I have not been able to find definitive information on exactly what a session is capable of. In particular, if it is possible for this stored procedure to execute twice concurrently in a single session, a significantly higher isolation level is required for a transaction within that procedure due to the two executions now sharing a temporary table.
sql-server sql-server-2012
I have written a stored procedure which makes use of a temporary table. I know that in SQL Server, temporary tables are session-scoped. However, I have not been able to find definitive information on exactly what a session is capable of. In particular, if it is possible for this stored procedure to execute twice concurrently in a single session, a significantly higher isolation level is required for a transaction within that procedure due to the two executions now sharing a temporary table.
sql-server sql-server-2012
sql-server sql-server-2012
asked 4 hours ago
Trevor GiddingsTrevor Giddings
254
254
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
Not concurrently. Your options include:
- Run the queries one after another in the same session
- Switch from a temp table to a global temp table (use ##TableName instead of #TableName), but be aware that the global temp table is automatically dropped when the session that created the temp table closes, and there are no other active sessions with a reference to it
- Switch to a real user table in TempDB - you can create tables there, but be aware that they'll disappear on server restart
- Switch to a real user table in a user database
add a comment |
While Brent's answer is correct for for all practical purposes, and this is not something I've ever seen someone worry about, it is possible for multiple invocations of a stored procedure in a session to affect each other through a session-scoped #temp table.
The good news is it's extremely unlikely to happen in the wild because
1) A temp tables declared inside a stored procedures or nested batches don't actually have session visibility (or lifetime). And these are by far the most common case.
2) It requires MultipleActiveResultsets and either some very strange async client programming, or for the stored procedure to return a resultset in the middle, and the client to call another instance of the stored procedure while processing the results from the first.
Here's a cooked-up example:
using System;
using System.Data.SqlClient;
namespace ado.nettest
class Program
static void Main(string[] args)
using (var con = new SqlConnection("Server=localhost;database=tempdb;integrated security=true;MultipleActiveResultSets = True"))
con.Open();
var procDdl = @"
create table #t(id int)
exec ('
create procedure #foo
as
begin
insert into #t(id) values (1);
select top 10000 * from sys.messages m, sys.messages m2;
select count(*) rc from #t;
delete from #t;
end
');
";
var cmdDDL = con.CreateCommand();
cmdDDL.CommandText = procDdl;
cmdDDL.ExecuteNonQuery();
var cmd = con.CreateCommand();
cmd.CommandText = "exec #foo";
using (var rdr = cmd.ExecuteReader())
rdr.Read();
var cmd2 = con.CreateCommand();
cmd2.CommandText = "exec #foo";
using (var rdr2 = cmd2.ExecuteReader())
while (rdr.Read())
rdr.NextResult();
rdr.Read();
var rc = rdr.GetInt32(0);
Console.WriteLine($"Numer of rows in temp table rc");
Console.WriteLine("Hit any key to exit");
Console.ReadKey();
which outputs
Numer of rows in temp table 0
Hit any key to exit
If you move the
create table #t(id int)
into the stored procedure it outputs:
Numer of rows in temp table 1
Hit any key to exit
And with the temp table declared inside the procedure, if you change the second query to
cmd2.CommandText = "select * from #t";
It fails with:
'Invalid object name '#t'.'
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f235197%2fis-it-possible-for-sql-statements-to-execute-concurrently-within-a-single-sessio%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Not concurrently. Your options include:
- Run the queries one after another in the same session
- Switch from a temp table to a global temp table (use ##TableName instead of #TableName), but be aware that the global temp table is automatically dropped when the session that created the temp table closes, and there are no other active sessions with a reference to it
- Switch to a real user table in TempDB - you can create tables there, but be aware that they'll disappear on server restart
- Switch to a real user table in a user database
add a comment |
Not concurrently. Your options include:
- Run the queries one after another in the same session
- Switch from a temp table to a global temp table (use ##TableName instead of #TableName), but be aware that the global temp table is automatically dropped when the session that created the temp table closes, and there are no other active sessions with a reference to it
- Switch to a real user table in TempDB - you can create tables there, but be aware that they'll disappear on server restart
- Switch to a real user table in a user database
add a comment |
Not concurrently. Your options include:
- Run the queries one after another in the same session
- Switch from a temp table to a global temp table (use ##TableName instead of #TableName), but be aware that the global temp table is automatically dropped when the session that created the temp table closes, and there are no other active sessions with a reference to it
- Switch to a real user table in TempDB - you can create tables there, but be aware that they'll disappear on server restart
- Switch to a real user table in a user database
Not concurrently. Your options include:
- Run the queries one after another in the same session
- Switch from a temp table to a global temp table (use ##TableName instead of #TableName), but be aware that the global temp table is automatically dropped when the session that created the temp table closes, and there are no other active sessions with a reference to it
- Switch to a real user table in TempDB - you can create tables there, but be aware that they'll disappear on server restart
- Switch to a real user table in a user database
answered 4 hours ago
Brent OzarBrent Ozar
35.9k19112246
35.9k19112246
add a comment |
add a comment |
While Brent's answer is correct for for all practical purposes, and this is not something I've ever seen someone worry about, it is possible for multiple invocations of a stored procedure in a session to affect each other through a session-scoped #temp table.
The good news is it's extremely unlikely to happen in the wild because
1) A temp tables declared inside a stored procedures or nested batches don't actually have session visibility (or lifetime). And these are by far the most common case.
2) It requires MultipleActiveResultsets and either some very strange async client programming, or for the stored procedure to return a resultset in the middle, and the client to call another instance of the stored procedure while processing the results from the first.
Here's a cooked-up example:
using System;
using System.Data.SqlClient;
namespace ado.nettest
class Program
static void Main(string[] args)
using (var con = new SqlConnection("Server=localhost;database=tempdb;integrated security=true;MultipleActiveResultSets = True"))
con.Open();
var procDdl = @"
create table #t(id int)
exec ('
create procedure #foo
as
begin
insert into #t(id) values (1);
select top 10000 * from sys.messages m, sys.messages m2;
select count(*) rc from #t;
delete from #t;
end
');
";
var cmdDDL = con.CreateCommand();
cmdDDL.CommandText = procDdl;
cmdDDL.ExecuteNonQuery();
var cmd = con.CreateCommand();
cmd.CommandText = "exec #foo";
using (var rdr = cmd.ExecuteReader())
rdr.Read();
var cmd2 = con.CreateCommand();
cmd2.CommandText = "exec #foo";
using (var rdr2 = cmd2.ExecuteReader())
while (rdr.Read())
rdr.NextResult();
rdr.Read();
var rc = rdr.GetInt32(0);
Console.WriteLine($"Numer of rows in temp table rc");
Console.WriteLine("Hit any key to exit");
Console.ReadKey();
which outputs
Numer of rows in temp table 0
Hit any key to exit
If you move the
create table #t(id int)
into the stored procedure it outputs:
Numer of rows in temp table 1
Hit any key to exit
And with the temp table declared inside the procedure, if you change the second query to
cmd2.CommandText = "select * from #t";
It fails with:
'Invalid object name '#t'.'
add a comment |
While Brent's answer is correct for for all practical purposes, and this is not something I've ever seen someone worry about, it is possible for multiple invocations of a stored procedure in a session to affect each other through a session-scoped #temp table.
The good news is it's extremely unlikely to happen in the wild because
1) A temp tables declared inside a stored procedures or nested batches don't actually have session visibility (or lifetime). And these are by far the most common case.
2) It requires MultipleActiveResultsets and either some very strange async client programming, or for the stored procedure to return a resultset in the middle, and the client to call another instance of the stored procedure while processing the results from the first.
Here's a cooked-up example:
using System;
using System.Data.SqlClient;
namespace ado.nettest
class Program
static void Main(string[] args)
using (var con = new SqlConnection("Server=localhost;database=tempdb;integrated security=true;MultipleActiveResultSets = True"))
con.Open();
var procDdl = @"
create table #t(id int)
exec ('
create procedure #foo
as
begin
insert into #t(id) values (1);
select top 10000 * from sys.messages m, sys.messages m2;
select count(*) rc from #t;
delete from #t;
end
');
";
var cmdDDL = con.CreateCommand();
cmdDDL.CommandText = procDdl;
cmdDDL.ExecuteNonQuery();
var cmd = con.CreateCommand();
cmd.CommandText = "exec #foo";
using (var rdr = cmd.ExecuteReader())
rdr.Read();
var cmd2 = con.CreateCommand();
cmd2.CommandText = "exec #foo";
using (var rdr2 = cmd2.ExecuteReader())
while (rdr.Read())
rdr.NextResult();
rdr.Read();
var rc = rdr.GetInt32(0);
Console.WriteLine($"Numer of rows in temp table rc");
Console.WriteLine("Hit any key to exit");
Console.ReadKey();
which outputs
Numer of rows in temp table 0
Hit any key to exit
If you move the
create table #t(id int)
into the stored procedure it outputs:
Numer of rows in temp table 1
Hit any key to exit
And with the temp table declared inside the procedure, if you change the second query to
cmd2.CommandText = "select * from #t";
It fails with:
'Invalid object name '#t'.'
add a comment |
While Brent's answer is correct for for all practical purposes, and this is not something I've ever seen someone worry about, it is possible for multiple invocations of a stored procedure in a session to affect each other through a session-scoped #temp table.
The good news is it's extremely unlikely to happen in the wild because
1) A temp tables declared inside a stored procedures or nested batches don't actually have session visibility (or lifetime). And these are by far the most common case.
2) It requires MultipleActiveResultsets and either some very strange async client programming, or for the stored procedure to return a resultset in the middle, and the client to call another instance of the stored procedure while processing the results from the first.
Here's a cooked-up example:
using System;
using System.Data.SqlClient;
namespace ado.nettest
class Program
static void Main(string[] args)
using (var con = new SqlConnection("Server=localhost;database=tempdb;integrated security=true;MultipleActiveResultSets = True"))
con.Open();
var procDdl = @"
create table #t(id int)
exec ('
create procedure #foo
as
begin
insert into #t(id) values (1);
select top 10000 * from sys.messages m, sys.messages m2;
select count(*) rc from #t;
delete from #t;
end
');
";
var cmdDDL = con.CreateCommand();
cmdDDL.CommandText = procDdl;
cmdDDL.ExecuteNonQuery();
var cmd = con.CreateCommand();
cmd.CommandText = "exec #foo";
using (var rdr = cmd.ExecuteReader())
rdr.Read();
var cmd2 = con.CreateCommand();
cmd2.CommandText = "exec #foo";
using (var rdr2 = cmd2.ExecuteReader())
while (rdr.Read())
rdr.NextResult();
rdr.Read();
var rc = rdr.GetInt32(0);
Console.WriteLine($"Numer of rows in temp table rc");
Console.WriteLine("Hit any key to exit");
Console.ReadKey();
which outputs
Numer of rows in temp table 0
Hit any key to exit
If you move the
create table #t(id int)
into the stored procedure it outputs:
Numer of rows in temp table 1
Hit any key to exit
And with the temp table declared inside the procedure, if you change the second query to
cmd2.CommandText = "select * from #t";
It fails with:
'Invalid object name '#t'.'
While Brent's answer is correct for for all practical purposes, and this is not something I've ever seen someone worry about, it is possible for multiple invocations of a stored procedure in a session to affect each other through a session-scoped #temp table.
The good news is it's extremely unlikely to happen in the wild because
1) A temp tables declared inside a stored procedures or nested batches don't actually have session visibility (or lifetime). And these are by far the most common case.
2) It requires MultipleActiveResultsets and either some very strange async client programming, or for the stored procedure to return a resultset in the middle, and the client to call another instance of the stored procedure while processing the results from the first.
Here's a cooked-up example:
using System;
using System.Data.SqlClient;
namespace ado.nettest
class Program
static void Main(string[] args)
using (var con = new SqlConnection("Server=localhost;database=tempdb;integrated security=true;MultipleActiveResultSets = True"))
con.Open();
var procDdl = @"
create table #t(id int)
exec ('
create procedure #foo
as
begin
insert into #t(id) values (1);
select top 10000 * from sys.messages m, sys.messages m2;
select count(*) rc from #t;
delete from #t;
end
');
";
var cmdDDL = con.CreateCommand();
cmdDDL.CommandText = procDdl;
cmdDDL.ExecuteNonQuery();
var cmd = con.CreateCommand();
cmd.CommandText = "exec #foo";
using (var rdr = cmd.ExecuteReader())
rdr.Read();
var cmd2 = con.CreateCommand();
cmd2.CommandText = "exec #foo";
using (var rdr2 = cmd2.ExecuteReader())
while (rdr.Read())
rdr.NextResult();
rdr.Read();
var rc = rdr.GetInt32(0);
Console.WriteLine($"Numer of rows in temp table rc");
Console.WriteLine("Hit any key to exit");
Console.ReadKey();
which outputs
Numer of rows in temp table 0
Hit any key to exit
If you move the
create table #t(id int)
into the stored procedure it outputs:
Numer of rows in temp table 1
Hit any key to exit
And with the temp table declared inside the procedure, if you change the second query to
cmd2.CommandText = "select * from #t";
It fails with:
'Invalid object name '#t'.'
edited 3 mins ago
answered 18 mins ago
David Browne - MicrosoftDavid Browne - Microsoft
12.5k729
12.5k729
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f235197%2fis-it-possible-for-sql-statements-to-execute-concurrently-within-a-single-sessio%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown