Lock a table for a CTAS upsert in Azure SQL DataWarehouse
I have a type 2 dimension in Azure SQL Data Warehouse.
Essentially I am creating an interim table with
CREATE TABLE myDimension_temp
AS
SELECT
...
FROM myStagingTable;
etc
After the CTAS is complete I do a
RENAME OBJECT myDimension TO myDimension_old;
RENAME OBJECT myDimension_tmp TO myDimension;
DROP TABLE myDimension_old;
Is there a way to lock these tables so that any actions against myDimension are blocked until the renaming of both tables is complete?
If so, is this worth implementing or is the time it takes to swap out tables in this manner so negligible that the chances of this occurring are too small to worry about or am I looking at/going about this in completely the wrong way?
t-sql locking azure-sql-data-warehouse ctas
asked Oct 20 '17 at 3:27
TomTom
61
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I have a type 2 dimension in Azure SQL Data Warehouse.
Essentially I am creating an interim table with
CREATE TABLE myDimension_temp
AS
SELECT
...
FROM myStagingTable;
etc
After the CTAS is complete I do a
RENAME OBJECT myDimension TO myDimension_old;
RENAME OBJECT myDimension_tmp TO myDimension;
DROP TABLE myDimension_old;
Is there a way to lock these tables so that any actions against myDimension are blocked until the renaming of both tables is complete?
If so, is this worth implementing or is the time it takes to swap out tables in this manner so negligible that the chances of this occurring are too small to worry about or am I looking at/going about this in completely the wrong way?
t-sql locking azure-sql-data-warehouse ctas
asked Oct 20 '17 at 3:27
TomTom
61
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
The order of your operations is correct and the name swap operation typically completes in a few seconds so the odds of someone sneaking in and running a query exists but is low unless you're running a very busy DW. That said, you might encounter blocking when trying to rename the existing/old table so be prepared with the right scripts to find the offending query/user and handle it (kill query, notify user, etc...).
– SQLmojoe
Dec 20 '17 at 20:56
add a comment |
I have a type 2 dimension in Azure SQL Data Warehouse.
Essentially I am creating an interim table with
CREATE TABLE myDimension_temp
AS
SELECT
...
FROM myStagingTable;
etc
After the CTAS is complete I do a
RENAME OBJECT myDimension TO myDimension_old;
RENAME OBJECT myDimension_tmp TO myDimension;
DROP TABLE myDimension_old;
Is there a way to lock these tables so that any actions against myDimension are blocked until the renaming of both tables is complete?
If so, is this worth implementing or is the time it takes to swap out tables in this manner so negligible that the chances of this occurring are too small to worry about or am I looking at/going about this in completely the wrong way?
t-sql locking azure-sql-data-warehouse ctas
asked Oct 20 '17 at 3:27
TomTom
61
I have a type 2 dimension in Azure SQL Data Warehouse.
Essentially I am creating an interim table with
CREATE TABLE myDimension_temp
AS
SELECT
...
FROM myStagingTable;
etc
After the CTAS is complete I do a
RENAME OBJECT myDimension TO myDimension_old;
RENAME OBJECT myDimension_tmp TO myDimension;
DROP TABLE myDimension_old;
Is there a way to lock these tables so that any actions against myDimension are blocked until the renaming of both tables is complete?
If so, is this worth implementing or is the time it takes to swap out tables in this manner so negligible that the chances of this occurring are too small to worry about or am I looking at/going about this in completely the wrong way?
t-sql locking azure-sql-data-warehouse ctas
t-sql locking azure-sql-data-warehouse ctas
asked Oct 20 '17 at 3:27
TomTom
61
asked Oct 20 '17 at 3:27
TomTom
61
asked Oct 20 '17 at 3:27
TomTom
61
asked Oct 20 '17 at 3:27
TomTom
61
asked Oct 20 '17 at 3:27
TomTom
61
61
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 5 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
The order of your operations is correct and the name swap operation typically completes in a few seconds so the odds of someone sneaking in and running a query exists but is low unless you're running a very busy DW. That said, you might encounter blocking when trying to rename the existing/old table so be prepared with the right scripts to find the offending query/user and handle it (kill query, notify user, etc...).
– SQLmojoe
Dec 20 '17 at 20:56
add a comment |
The order of your operations is correct and the name swap operation typically completes in a few seconds so the odds of someone sneaking in and running a query exists but is low unless you're running a very busy DW. That said, you might encounter blocking when trying to rename the existing/old table so be prepared with the right scripts to find the offending query/user and handle it (kill query, notify user, etc...).
– SQLmojoe
Dec 20 '17 at 20:56
The order of your operations is correct and the name swap operation typically completes in a few seconds so the odds of someone sneaking in and running a query exists but is low unless you're running a very busy DW. That said, you might encounter blocking when trying to rename the existing/old table so be prepared with the right scripts to find the offending query/user and handle it (kill query, notify user, etc...).
– SQLmojoe
Dec 20 '17 at 20:56
The order of your operations is correct and the name swap operation typically completes in a few seconds so the odds of someone sneaking in and running a query exists but is low unless you're running a very busy DW. That said, you might encounter blocking when trying to rename the existing/old table so be prepared with the right scripts to find the offending query/user and handle it (kill query, notify user, etc...).
– SQLmojoe
Dec 20 '17 at 20:56
add a comment |
1 Answer
1
active
oldest
votes
There is generally no exclusive locking in Azure SQL Data Warehouse as the default isolation level is READ UNCOMMITTED as documented here.
Other approaches to this such as sp_get_applock are also unavailable. Therefore you manage this based on low concurrency and workflow, ie don't allow users to connect to your warehouse during the update, or use some kind of flag that has to be checked during operations.
answered Oct 20 '17 at 9:21
wBobwBob
9,50221738
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%2f188895%2flock-a-table-for-a-ctas-upsert-in-azure-sql-datawarehouse%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
There is generally no exclusive locking in Azure SQL Data Warehouse as the default isolation level is READ UNCOMMITTED as documented here.
Other approaches to this such as sp_get_applock are also unavailable. Therefore you manage this based on low concurrency and workflow, ie don't allow users to connect to your warehouse during the update, or use some kind of flag that has to be checked during operations.
answered Oct 20 '17 at 9:21
wBobwBob
9,50221738
add a comment |
There is generally no exclusive locking in Azure SQL Data Warehouse as the default isolation level is READ UNCOMMITTED as documented here.
Other approaches to this such as sp_get_applock are also unavailable. Therefore you manage this based on low concurrency and workflow, ie don't allow users to connect to your warehouse during the update, or use some kind of flag that has to be checked during operations.
answered Oct 20 '17 at 9:21
wBobwBob
9,50221738
add a comment |
There is generally no exclusive locking in Azure SQL Data Warehouse as the default isolation level is READ UNCOMMITTED as documented here.
Other approaches to this such as sp_get_applock are also unavailable. Therefore you manage this based on low concurrency and workflow, ie don't allow users to connect to your warehouse during the update, or use some kind of flag that has to be checked during operations.
answered Oct 20 '17 at 9:21
wBobwBob
9,50221738
There is generally no exclusive locking in Azure SQL Data Warehouse as the default isolation level is READ UNCOMMITTED as documented here.
Other approaches to this such as sp_get_applock are also unavailable. Therefore you manage this based on low concurrency and workflow, ie don't allow users to connect to your warehouse during the update, or use some kind of flag that has to be checked during operations.
answered Oct 20 '17 at 9:21
wBobwBob
9,50221738
answered Oct 20 '17 at 9:21
wBobwBob
9,50221738
answered Oct 20 '17 at 9:21
wBobwBob
9,50221738
answered Oct 20 '17 at 9:21
wBobwBob
9,50221738
9,50221738
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%2f188895%2flock-a-table-for-a-ctas-upsert-in-azure-sql-datawarehouse%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
The order of your operations is correct and the name swap operation typically completes in a few seconds so the odds of someone sneaking in and running a query exists but is low unless you're running a very busy DW. That said, you might encounter blocking when trying to rename the existing/old table so be prepared with the right scripts to find the offending query/user and handle it (kill query, notify user, etc...).
– SQLmojoe
Dec 20 '17 at 20:56