Creating and retrieving index at the same time?
I am working on a C# program which runs with MariaDB behind it.
As it could be run by multiple users at the same time, when inserting (creating) a new row with 'INSERT' statement, I leave the index (unique index field) blank and let the MariaDB assign them.
The problem is, since I have left the index field blank in my program, I don't know what index number is assigned in DB. If I try to create the index on the client side, then I cannot guarantee that this is unique, on the DB as well.
Thus, is there a way to retrieve index number assigned by DB, at the time creating the row?
PS: I'm using sqlcommandbuilder and update function of sqladapter.
c# sql indexing mariadb
edited Nov 14 '18 at 23:31
Mick
4,18013148
asked Nov 14 '18 at 23:22
Jimin ChoiJimin Choi
34
|
show 5 more comments
I am working on a C# program which runs with MariaDB behind it.
As it could be run by multiple users at the same time, when inserting (creating) a new row with 'INSERT' statement, I leave the index (unique index field) blank and let the MariaDB assign them.
The problem is, since I have left the index field blank in my program, I don't know what index number is assigned in DB. If I try to create the index on the client side, then I cannot guarantee that this is unique, on the DB as well.
Thus, is there a way to retrieve index number assigned by DB, at the time creating the row?
PS: I'm using sqlcommandbuilder and update function of sqladapter.
c# sql indexing mariadb
edited Nov 14 '18 at 23:31
Mick
4,18013148
asked Nov 14 '18 at 23:22
Jimin ChoiJimin Choi
34
So it isAUTO_INCREMENT?
– mjwills
Nov 14 '18 at 23:24
1
It would be awesome if you could provide a Minimal, Complete, and Verifiable example.
– mjwills
Nov 14 '18 at 23:25
What you want is something like teh SQL Output clause. docs.microsoft.com/en-us/sql/t-sql/queries/… Unfortunately I have no idea if Maria DB supports something like it.
– Christopher
Nov 14 '18 at 23:28
MariaDB is a variant of MySQL the SQL Server providers ("sqlcommandbuilder " and "sqladapter") ought not work with it. The official MySQL provider is on NuGet
– None of the Above
Nov 14 '18 at 23:37
yes, the field is AUTO_INCREMENT set.
– Jimin Choi
Nov 14 '18 at 23:48
|
show 5 more comments
I am working on a C# program which runs with MariaDB behind it.
As it could be run by multiple users at the same time, when inserting (creating) a new row with 'INSERT' statement, I leave the index (unique index field) blank and let the MariaDB assign them.
The problem is, since I have left the index field blank in my program, I don't know what index number is assigned in DB. If I try to create the index on the client side, then I cannot guarantee that this is unique, on the DB as well.
Thus, is there a way to retrieve index number assigned by DB, at the time creating the row?
PS: I'm using sqlcommandbuilder and update function of sqladapter.
c# sql indexing mariadb
edited Nov 14 '18 at 23:31
Mick
4,18013148
asked Nov 14 '18 at 23:22
Jimin ChoiJimin Choi
34
I am working on a C# program which runs with MariaDB behind it.
As it could be run by multiple users at the same time, when inserting (creating) a new row with 'INSERT' statement, I leave the index (unique index field) blank and let the MariaDB assign them.
The problem is, since I have left the index field blank in my program, I don't know what index number is assigned in DB. If I try to create the index on the client side, then I cannot guarantee that this is unique, on the DB as well.
Thus, is there a way to retrieve index number assigned by DB, at the time creating the row?
PS: I'm using sqlcommandbuilder and update function of sqladapter.
c# sql indexing mariadb
c# sql indexing mariadb
edited Nov 14 '18 at 23:31
Mick
4,18013148
asked Nov 14 '18 at 23:22
Jimin ChoiJimin Choi
34
edited Nov 14 '18 at 23:31
Mick
4,18013148
asked Nov 14 '18 at 23:22
Jimin ChoiJimin Choi
34
edited Nov 14 '18 at 23:31
Mick
4,18013148
edited Nov 14 '18 at 23:31
Mick
4,18013148
edited Nov 14 '18 at 23:31
Mick
4,18013148
4,18013148
asked Nov 14 '18 at 23:22
Jimin ChoiJimin Choi
34
asked Nov 14 '18 at 23:22
Jimin ChoiJimin Choi
34
asked Nov 14 '18 at 23:22
Jimin ChoiJimin Choi
34
34
So it isAUTO_INCREMENT?
– mjwills
Nov 14 '18 at 23:24
1
It would be awesome if you could provide a Minimal, Complete, and Verifiable example.
– mjwills
Nov 14 '18 at 23:25
What you want is something like teh SQL Output clause. docs.microsoft.com/en-us/sql/t-sql/queries/… Unfortunately I have no idea if Maria DB supports something like it.
– Christopher
Nov 14 '18 at 23:28
MariaDB is a variant of MySQL the SQL Server providers ("sqlcommandbuilder " and "sqladapter") ought not work with it. The official MySQL provider is on NuGet
– None of the Above
Nov 14 '18 at 23:37
yes, the field is AUTO_INCREMENT set.
– Jimin Choi
Nov 14 '18 at 23:48
|
show 5 more comments
So it isAUTO_INCREMENT?
– mjwills
Nov 14 '18 at 23:24
1
It would be awesome if you could provide a Minimal, Complete, and Verifiable example.
– mjwills
Nov 14 '18 at 23:25
What you want is something like teh SQL Output clause. docs.microsoft.com/en-us/sql/t-sql/queries/… Unfortunately I have no idea if Maria DB supports something like it.
– Christopher
Nov 14 '18 at 23:28
MariaDB is a variant of MySQL the SQL Server providers ("sqlcommandbuilder " and "sqladapter") ought not work with it. The official MySQL provider is on NuGet
– None of the Above
Nov 14 '18 at 23:37
yes, the field is AUTO_INCREMENT set.
– Jimin Choi
Nov 14 '18 at 23:48
So it is
AUTO_INCREMENT?– mjwills
Nov 14 '18 at 23:24
So it is
AUTO_INCREMENT?– mjwills
Nov 14 '18 at 23:24
1
1
It would be awesome if you could provide a Minimal, Complete, and Verifiable example.
– mjwills
Nov 14 '18 at 23:25
It would be awesome if you could provide a Minimal, Complete, and Verifiable example.
– mjwills
Nov 14 '18 at 23:25
What you want is something like teh SQL Output clause. docs.microsoft.com/en-us/sql/t-sql/queries/… Unfortunately I have no idea if Maria DB supports something like it.
– Christopher
Nov 14 '18 at 23:28
What you want is something like teh SQL Output clause. docs.microsoft.com/en-us/sql/t-sql/queries/… Unfortunately I have no idea if Maria DB supports something like it.
– Christopher
Nov 14 '18 at 23:28
MariaDB is a variant of MySQL the SQL Server providers ("sqlcommandbuilder " and "sqladapter") ought not work with it. The official MySQL provider is on NuGet
– None of the Above
Nov 14 '18 at 23:37
MariaDB is a variant of MySQL the SQL Server providers ("sqlcommandbuilder " and "sqladapter") ought not work with it. The official MySQL provider is on NuGet
– None of the Above
Nov 14 '18 at 23:37
yes, the field is AUTO_INCREMENT set.
– Jimin Choi
Nov 14 '18 at 23:48
yes, the field is AUTO_INCREMENT set.
– Jimin Choi
Nov 14 '18 at 23:48
|
show 5 more comments
2 Answers
2
active
oldest
votes
You have it perfectly right: You can not get define the primary key in the GUI. Doing so would only get you into race conditions. You need to let the DB generate it (and any other autovalues), then return it.
I have no knowledge of Maria DB, so I can only give you the SQL related answers: Both SELECT TOP and SCOPE_IDENTITY have been used. However those are not race condition save at all. Something that however is, is the SQL OUTPUT clause. Retreiving automatically created data like PK's is what it is there for. And it is even part of the original Transaction surrounding the DML statement.
I could not find a match googling for "MariaDB OUTPUT clause". However I did find this:
Other options for the statement include “INSERT...SET” statements,
“INSERT...SELECT” statements, and several other options.
A "insert select" syntax could be the closest equivalent to SQL's INSERT ... OUTPUT combination. Could.
Edit: Well, INSERT SELECT in MariaDB does not seem the droid you are looking for after all: https://mariadb.com/kb/en/library/insert-select/ It is more like a bulk insert/insert from other table/import
So I guess the fallback is to make your own insert/select combination, that is part of a manually created Transaction and lock on the table.
answered Nov 14 '18 at 23:35
ChristopherChristopher
3,0062823
Many thanks Christopher. I have read all your links and it enlightened me. Yes, I've gotta use my own insert .. select and run it through ExecuteScalar() command.
– Jimin Choi
Nov 15 '18 at 0:04
add a comment |
What about, using 2 sql below?
SELECT MAX(id) FROM mytable.
or
SELECT LAST_INSERT_ID()
Will be making another race condition?
answered Nov 15 '18 at 0:07
Jimin ChoiJimin Choi
34
If anything else was inserted between the INSERT and the select going off: Sure it will be a race condition. You get the wrong ID. It would be save if you could wrap it into the same lock as the original Insert. Then no other Insert could interfere.
– Christopher
Nov 15 '18 at 0:11
Yes lock could work, but tablelock seems to prevent reading privileges as well. I don't want to block users from reading, but writing...
– Jimin Choi
Nov 15 '18 at 0:14
Usually queries have a timeout on aquiring a lock/detection of deadlocks. Something around 30 seconds. The DML State Insert itself already requires a full read/write lock on the table. That is by nature part of doing a insert. So it would not be a big change to manually create the lock and add a select into it.
– Christopher
Nov 15 '18 at 0:20
Any DQL statement will have a implicit read lock as part of the operation. Any DML statement will have a implicit read/write lock and a implicit transaction as part of the operation. That is so fundamental to Databases, NTFS itself has both these featurs as well. I know that you can turn off the implicit transactions (you should never do however). I doubt you even can do that with locks. However both can be overruled/overshadowed if you have a comparable lock/transaction around the whole thing. You manually create them, to get "more opeartions into the fold".
– Christopher
Nov 15 '18 at 0:31
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53310251%2fcreating-and-retrieving-index-at-the-same-time%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
You have it perfectly right: You can not get define the primary key in the GUI. Doing so would only get you into race conditions. You need to let the DB generate it (and any other autovalues), then return it.
I have no knowledge of Maria DB, so I can only give you the SQL related answers: Both SELECT TOP and SCOPE_IDENTITY have been used. However those are not race condition save at all. Something that however is, is the SQL OUTPUT clause. Retreiving automatically created data like PK's is what it is there for. And it is even part of the original Transaction surrounding the DML statement.
I could not find a match googling for "MariaDB OUTPUT clause". However I did find this:
Other options for the statement include “INSERT...SET” statements,
“INSERT...SELECT” statements, and several other options.
A "insert select" syntax could be the closest equivalent to SQL's INSERT ... OUTPUT combination. Could.
Edit: Well, INSERT SELECT in MariaDB does not seem the droid you are looking for after all: https://mariadb.com/kb/en/library/insert-select/ It is more like a bulk insert/insert from other table/import
So I guess the fallback is to make your own insert/select combination, that is part of a manually created Transaction and lock on the table.
answered Nov 14 '18 at 23:35
ChristopherChristopher
3,0062823
Many thanks Christopher. I have read all your links and it enlightened me. Yes, I've gotta use my own insert .. select and run it through ExecuteScalar() command.
– Jimin Choi
Nov 15 '18 at 0:04
add a comment |
You have it perfectly right: You can not get define the primary key in the GUI. Doing so would only get you into race conditions. You need to let the DB generate it (and any other autovalues), then return it.
I have no knowledge of Maria DB, so I can only give you the SQL related answers: Both SELECT TOP and SCOPE_IDENTITY have been used. However those are not race condition save at all. Something that however is, is the SQL OUTPUT clause. Retreiving automatically created data like PK's is what it is there for. And it is even part of the original Transaction surrounding the DML statement.
I could not find a match googling for "MariaDB OUTPUT clause". However I did find this:
Other options for the statement include “INSERT...SET” statements,
“INSERT...SELECT” statements, and several other options.
A "insert select" syntax could be the closest equivalent to SQL's INSERT ... OUTPUT combination. Could.
Edit: Well, INSERT SELECT in MariaDB does not seem the droid you are looking for after all: https://mariadb.com/kb/en/library/insert-select/ It is more like a bulk insert/insert from other table/import
So I guess the fallback is to make your own insert/select combination, that is part of a manually created Transaction and lock on the table.
answered Nov 14 '18 at 23:35
ChristopherChristopher
3,0062823
Many thanks Christopher. I have read all your links and it enlightened me. Yes, I've gotta use my own insert .. select and run it through ExecuteScalar() command.
– Jimin Choi
Nov 15 '18 at 0:04
add a comment |
You have it perfectly right: You can not get define the primary key in the GUI. Doing so would only get you into race conditions. You need to let the DB generate it (and any other autovalues), then return it.
I have no knowledge of Maria DB, so I can only give you the SQL related answers: Both SELECT TOP and SCOPE_IDENTITY have been used. However those are not race condition save at all. Something that however is, is the SQL OUTPUT clause. Retreiving automatically created data like PK's is what it is there for. And it is even part of the original Transaction surrounding the DML statement.
I could not find a match googling for "MariaDB OUTPUT clause". However I did find this:
Other options for the statement include “INSERT...SET” statements,
“INSERT...SELECT” statements, and several other options.
A "insert select" syntax could be the closest equivalent to SQL's INSERT ... OUTPUT combination. Could.
Edit: Well, INSERT SELECT in MariaDB does not seem the droid you are looking for after all: https://mariadb.com/kb/en/library/insert-select/ It is more like a bulk insert/insert from other table/import
So I guess the fallback is to make your own insert/select combination, that is part of a manually created Transaction and lock on the table.
answered Nov 14 '18 at 23:35
ChristopherChristopher
3,0062823
You have it perfectly right: You can not get define the primary key in the GUI. Doing so would only get you into race conditions. You need to let the DB generate it (and any other autovalues), then return it.
I have no knowledge of Maria DB, so I can only give you the SQL related answers: Both SELECT TOP and SCOPE_IDENTITY have been used. However those are not race condition save at all. Something that however is, is the SQL OUTPUT clause. Retreiving automatically created data like PK's is what it is there for. And it is even part of the original Transaction surrounding the DML statement.
I could not find a match googling for "MariaDB OUTPUT clause". However I did find this:
Other options for the statement include “INSERT...SET” statements,
“INSERT...SELECT” statements, and several other options.
A "insert select" syntax could be the closest equivalent to SQL's INSERT ... OUTPUT combination. Could.
Edit: Well, INSERT SELECT in MariaDB does not seem the droid you are looking for after all: https://mariadb.com/kb/en/library/insert-select/ It is more like a bulk insert/insert from other table/import
So I guess the fallback is to make your own insert/select combination, that is part of a manually created Transaction and lock on the table.
answered Nov 14 '18 at 23:35
ChristopherChristopher
3,0062823
edited Nov 14 '18 at 23:43
answered Nov 14 '18 at 23:35
ChristopherChristopher
3,0062823
answered Nov 14 '18 at 23:35
ChristopherChristopher
3,0062823
answered Nov 14 '18 at 23:35
ChristopherChristopher
3,0062823
3,0062823
Many thanks Christopher. I have read all your links and it enlightened me. Yes, I've gotta use my own insert .. select and run it through ExecuteScalar() command.
– Jimin Choi
Nov 15 '18 at 0:04
add a comment |
Many thanks Christopher. I have read all your links and it enlightened me. Yes, I've gotta use my own insert .. select and run it through ExecuteScalar() command.
– Jimin Choi
Nov 15 '18 at 0:04
Many thanks Christopher. I have read all your links and it enlightened me. Yes, I've gotta use my own insert .. select and run it through ExecuteScalar() command.
– Jimin Choi
Nov 15 '18 at 0:04
Many thanks Christopher. I have read all your links and it enlightened me. Yes, I've gotta use my own insert .. select and run it through ExecuteScalar() command.
– Jimin Choi
Nov 15 '18 at 0:04
add a comment |
What about, using 2 sql below?
SELECT MAX(id) FROM mytable.
or
SELECT LAST_INSERT_ID()
Will be making another race condition?
answered Nov 15 '18 at 0:07
Jimin ChoiJimin Choi
34
If anything else was inserted between the INSERT and the select going off: Sure it will be a race condition. You get the wrong ID. It would be save if you could wrap it into the same lock as the original Insert. Then no other Insert could interfere.
– Christopher
Nov 15 '18 at 0:11
Yes lock could work, but tablelock seems to prevent reading privileges as well. I don't want to block users from reading, but writing...
– Jimin Choi
Nov 15 '18 at 0:14
Usually queries have a timeout on aquiring a lock/detection of deadlocks. Something around 30 seconds. The DML State Insert itself already requires a full read/write lock on the table. That is by nature part of doing a insert. So it would not be a big change to manually create the lock and add a select into it.
– Christopher
Nov 15 '18 at 0:20
Any DQL statement will have a implicit read lock as part of the operation. Any DML statement will have a implicit read/write lock and a implicit transaction as part of the operation. That is so fundamental to Databases, NTFS itself has both these featurs as well. I know that you can turn off the implicit transactions (you should never do however). I doubt you even can do that with locks. However both can be overruled/overshadowed if you have a comparable lock/transaction around the whole thing. You manually create them, to get "more opeartions into the fold".
– Christopher
Nov 15 '18 at 0:31
add a comment |
What about, using 2 sql below?
SELECT MAX(id) FROM mytable.
or
SELECT LAST_INSERT_ID()
Will be making another race condition?
answered Nov 15 '18 at 0:07
Jimin ChoiJimin Choi
34
If anything else was inserted between the INSERT and the select going off: Sure it will be a race condition. You get the wrong ID. It would be save if you could wrap it into the same lock as the original Insert. Then no other Insert could interfere.
– Christopher
Nov 15 '18 at 0:11
Yes lock could work, but tablelock seems to prevent reading privileges as well. I don't want to block users from reading, but writing...
– Jimin Choi
Nov 15 '18 at 0:14
Usually queries have a timeout on aquiring a lock/detection of deadlocks. Something around 30 seconds. The DML State Insert itself already requires a full read/write lock on the table. That is by nature part of doing a insert. So it would not be a big change to manually create the lock and add a select into it.
– Christopher
Nov 15 '18 at 0:20
Any DQL statement will have a implicit read lock as part of the operation. Any DML statement will have a implicit read/write lock and a implicit transaction as part of the operation. That is so fundamental to Databases, NTFS itself has both these featurs as well. I know that you can turn off the implicit transactions (you should never do however). I doubt you even can do that with locks. However both can be overruled/overshadowed if you have a comparable lock/transaction around the whole thing. You manually create them, to get "more opeartions into the fold".
– Christopher
Nov 15 '18 at 0:31
add a comment |
What about, using 2 sql below?
SELECT MAX(id) FROM mytable.
or
SELECT LAST_INSERT_ID()
Will be making another race condition?
answered Nov 15 '18 at 0:07
Jimin ChoiJimin Choi
34
What about, using 2 sql below?
SELECT MAX(id) FROM mytable.
or
SELECT LAST_INSERT_ID()
Will be making another race condition?
answered Nov 15 '18 at 0:07
Jimin ChoiJimin Choi
34
edited Nov 15 '18 at 0:12
answered Nov 15 '18 at 0:07
Jimin ChoiJimin Choi
34
answered Nov 15 '18 at 0:07
Jimin ChoiJimin Choi
34
answered Nov 15 '18 at 0:07
Jimin ChoiJimin Choi
34
34
If anything else was inserted between the INSERT and the select going off: Sure it will be a race condition. You get the wrong ID. It would be save if you could wrap it into the same lock as the original Insert. Then no other Insert could interfere.
– Christopher
Nov 15 '18 at 0:11
Yes lock could work, but tablelock seems to prevent reading privileges as well. I don't want to block users from reading, but writing...
– Jimin Choi
Nov 15 '18 at 0:14
Usually queries have a timeout on aquiring a lock/detection of deadlocks. Something around 30 seconds. The DML State Insert itself already requires a full read/write lock on the table. That is by nature part of doing a insert. So it would not be a big change to manually create the lock and add a select into it.
– Christopher
Nov 15 '18 at 0:20
Any DQL statement will have a implicit read lock as part of the operation. Any DML statement will have a implicit read/write lock and a implicit transaction as part of the operation. That is so fundamental to Databases, NTFS itself has both these featurs as well. I know that you can turn off the implicit transactions (you should never do however). I doubt you even can do that with locks. However both can be overruled/overshadowed if you have a comparable lock/transaction around the whole thing. You manually create them, to get "more opeartions into the fold".
– Christopher
Nov 15 '18 at 0:31
add a comment |
If anything else was inserted between the INSERT and the select going off: Sure it will be a race condition. You get the wrong ID. It would be save if you could wrap it into the same lock as the original Insert. Then no other Insert could interfere.
– Christopher
Nov 15 '18 at 0:11
Yes lock could work, but tablelock seems to prevent reading privileges as well. I don't want to block users from reading, but writing...
– Jimin Choi
Nov 15 '18 at 0:14
Usually queries have a timeout on aquiring a lock/detection of deadlocks. Something around 30 seconds. The DML State Insert itself already requires a full read/write lock on the table. That is by nature part of doing a insert. So it would not be a big change to manually create the lock and add a select into it.
– Christopher
Nov 15 '18 at 0:20
Any DQL statement will have a implicit read lock as part of the operation. Any DML statement will have a implicit read/write lock and a implicit transaction as part of the operation. That is so fundamental to Databases, NTFS itself has both these featurs as well. I know that you can turn off the implicit transactions (you should never do however). I doubt you even can do that with locks. However both can be overruled/overshadowed if you have a comparable lock/transaction around the whole thing. You manually create them, to get "more opeartions into the fold".
– Christopher
Nov 15 '18 at 0:31
If anything else was inserted between the INSERT and the select going off: Sure it will be a race condition. You get the wrong ID. It would be save if you could wrap it into the same lock as the original Insert. Then no other Insert could interfere.
– Christopher
Nov 15 '18 at 0:11
If anything else was inserted between the INSERT and the select going off: Sure it will be a race condition. You get the wrong ID. It would be save if you could wrap it into the same lock as the original Insert. Then no other Insert could interfere.
– Christopher
Nov 15 '18 at 0:11
Yes lock could work, but tablelock seems to prevent reading privileges as well. I don't want to block users from reading, but writing...
– Jimin Choi
Nov 15 '18 at 0:14
Yes lock could work, but tablelock seems to prevent reading privileges as well. I don't want to block users from reading, but writing...
– Jimin Choi
Nov 15 '18 at 0:14
Usually queries have a timeout on aquiring a lock/detection of deadlocks. Something around 30 seconds. The DML State Insert itself already requires a full read/write lock on the table. That is by nature part of doing a insert. So it would not be a big change to manually create the lock and add a select into it.
– Christopher
Nov 15 '18 at 0:20
Usually queries have a timeout on aquiring a lock/detection of deadlocks. Something around 30 seconds. The DML State Insert itself already requires a full read/write lock on the table. That is by nature part of doing a insert. So it would not be a big change to manually create the lock and add a select into it.
– Christopher
Nov 15 '18 at 0:20
Any DQL statement will have a implicit read lock as part of the operation. Any DML statement will have a implicit read/write lock and a implicit transaction as part of the operation. That is so fundamental to Databases, NTFS itself has both these featurs as well. I know that you can turn off the implicit transactions (you should never do however). I doubt you even can do that with locks. However both can be overruled/overshadowed if you have a comparable lock/transaction around the whole thing. You manually create them, to get "more opeartions into the fold".
– Christopher
Nov 15 '18 at 0:31
Any DQL statement will have a implicit read lock as part of the operation. Any DML statement will have a implicit read/write lock and a implicit transaction as part of the operation. That is so fundamental to Databases, NTFS itself has both these featurs as well. I know that you can turn off the implicit transactions (you should never do however). I doubt you even can do that with locks. However both can be overruled/overshadowed if you have a comparable lock/transaction around the whole thing. You manually create them, to get "more opeartions into the fold".
– Christopher
Nov 15 '18 at 0:31
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53310251%2fcreating-and-retrieving-index-at-the-same-time%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
So it is
AUTO_INCREMENT?– mjwills
Nov 14 '18 at 23:24
1
It would be awesome if you could provide a Minimal, Complete, and Verifiable example.
– mjwills
Nov 14 '18 at 23:25
What you want is something like teh SQL Output clause. docs.microsoft.com/en-us/sql/t-sql/queries/… Unfortunately I have no idea if Maria DB supports something like it.
– Christopher
Nov 14 '18 at 23:28
MariaDB is a variant of MySQL the SQL Server providers ("sqlcommandbuilder " and "sqladapter") ought not work with it. The official MySQL provider is on NuGet
– None of the Above
Nov 14 '18 at 23:37
yes, the field is AUTO_INCREMENT set.
– Jimin Choi
Nov 14 '18 at 23:48