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
What does it mean that physics no longer uses mechanical models to describe phenomena?
How fail-safe is nr as stop bytes?
Why doesn't SQL Optimizer use my constraint?
Why does the remaining Rebel fleet at the end of Rogue One seem dramatically larger than the one in A New Hope?
What is the difference between globalisation and imperialism?
Is it fair for a professor to grade us on the possession of past papers?
How does the math work when buying airline miles?
Amount of permutations on an NxNxN Rubik's Cube
Has negative voting ever been officially implemented in elections, or seriously proposed, or even studied?
Disembodied hand growing fangs
Sum letters are not two different
AppleTVs create a chatty alternate WiFi network
NumericArray versus PackedArray in MMA12
Dating a Former Employee
Should I follow up with an employee I believe overracted to a mistake I made?
Does the Weapon Master feat grant you a fighting style?
What is the appropriate index architecture when forced to implement IsDeleted (soft deletes)?
Illegal assignment from sObject to Id
Putting class ranking in CV, but against dept guidelines
How could we fake a moon landing now?
How to compare two different files line by line in unix?
Why is my ESD wriststrap failing with nitrile gloves on?
Do any jurisdictions seriously consider reclassifying social media websites as publishers?
Can an alien society believe that their star system is the universe?
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 5 hours ago
Trevor GiddingsTrevor Giddings
354
354
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) #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
because the second invocation of the stored procedure inserted a row, and then deleted all the rows from #t while the first invocation was waiting for the client to fetch the rows from its first resultset. Note that if the first resultset was small, the rows might get buffered and could continue without sending anything to the client.
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'.'
1
As soon as I saw the question title I knew the answer was MARS.
– Joshua
1 hour ago
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 5 hours ago
Brent OzarBrent Ozar
35.9k19112247
35.9k19112247
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) #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
because the second invocation of the stored procedure inserted a row, and then deleted all the rows from #t while the first invocation was waiting for the client to fetch the rows from its first resultset. Note that if the first resultset was small, the rows might get buffered and could continue without sending anything to the client.
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'.'
1
As soon as I saw the question title I knew the answer was MARS.
– Joshua
1 hour ago
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) #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
because the second invocation of the stored procedure inserted a row, and then deleted all the rows from #t while the first invocation was waiting for the client to fetch the rows from its first resultset. Note that if the first resultset was small, the rows might get buffered and could continue without sending anything to the client.
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'.'
1
As soon as I saw the question title I knew the answer was MARS.
– Joshua
1 hour ago
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) #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
because the second invocation of the stored procedure inserted a row, and then deleted all the rows from #t while the first invocation was waiting for the client to fetch the rows from its first resultset. Note that if the first resultset was small, the rows might get buffered and could continue without sending anything to the client.
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) #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
because the second invocation of the stored procedure inserted a row, and then deleted all the rows from #t while the first invocation was waiting for the client to fetch the rows from its first resultset. Note that if the first resultset was small, the rows might get buffered and could continue without sending anything to the client.
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 1 hour ago
answered 1 hour ago
David Browne - MicrosoftDavid Browne - Microsoft
12.5k729
12.5k729
1
As soon as I saw the question title I knew the answer was MARS.
– Joshua
1 hour ago
add a comment |
1
As soon as I saw the question title I knew the answer was MARS.
– Joshua
1 hour ago
1
1
As soon as I saw the question title I knew the answer was MARS.
– Joshua
1 hour ago
As soon as I saw the question title I knew the answer was MARS.
– Joshua
1 hour ago
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