Can my UPDATE statements will work if run concurrently in MySQL?
I saw many similar questions but still I am not fully sure if I am correct.
We have an application which launches a job to mass send many messages. The messages delivery status are received later in batches and in no particular order.
The table structure is like:-
CREATE TABLE `message` (
`pk` char(32) NOT NULL DEFAULT '',
`job_id` varchar(40) DEFAULT NULL,
`status` varchar(40) DEFAULT NULL,
`update_date` datetime DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `job_id` (`job_id`),
KEY `status` (`status`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The records are initially created with status set to null. Its value is expected to change from null to sent and then to delivered. Below statements are used to update the records.
When status to be set is - delivered
Update message SET status = 'delivered', update_date = Now()
WHERE job_id = :someId
When status to be set is - sent
Update message SET status = 'sent', update_date = Now()
WHERE job_id = :someId AND status IS NULL
Problem is, that it is possible that there could be two threads concurrently trying to set the status of same record to 'sent' and 'delivered'. In this case 'delivered' is the final status so we would want that to win eventually.
Will the above statements ensure this in MySql or MariaDB?
mysql mariadb concurrentmodification
edited Nov 20 at 18:08
Rick James
65.7k55797
asked Nov 20 at 17:49
AppleGrew
4,0751657102
add a comment |
I saw many similar questions but still I am not fully sure if I am correct.
We have an application which launches a job to mass send many messages. The messages delivery status are received later in batches and in no particular order.
The table structure is like:-
CREATE TABLE `message` (
`pk` char(32) NOT NULL DEFAULT '',
`job_id` varchar(40) DEFAULT NULL,
`status` varchar(40) DEFAULT NULL,
`update_date` datetime DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `job_id` (`job_id`),
KEY `status` (`status`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The records are initially created with status set to null. Its value is expected to change from null to sent and then to delivered. Below statements are used to update the records.
When status to be set is - delivered
Update message SET status = 'delivered', update_date = Now()
WHERE job_id = :someId
When status to be set is - sent
Update message SET status = 'sent', update_date = Now()
WHERE job_id = :someId AND status IS NULL
Problem is, that it is possible that there could be two threads concurrently trying to set the status of same record to 'sent' and 'delivered'. In this case 'delivered' is the final status so we would want that to win eventually.
Will the above statements ensure this in MySql or MariaDB?
mysql mariadb concurrentmodification
edited Nov 20 at 18:08
Rick James
65.7k55797
asked Nov 20 at 17:49
AppleGrew
4,0751657102
add a comment |
I saw many similar questions but still I am not fully sure if I am correct.
We have an application which launches a job to mass send many messages. The messages delivery status are received later in batches and in no particular order.
The table structure is like:-
CREATE TABLE `message` (
`pk` char(32) NOT NULL DEFAULT '',
`job_id` varchar(40) DEFAULT NULL,
`status` varchar(40) DEFAULT NULL,
`update_date` datetime DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `job_id` (`job_id`),
KEY `status` (`status`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The records are initially created with status set to null. Its value is expected to change from null to sent and then to delivered. Below statements are used to update the records.
When status to be set is - delivered
Update message SET status = 'delivered', update_date = Now()
WHERE job_id = :someId
When status to be set is - sent
Update message SET status = 'sent', update_date = Now()
WHERE job_id = :someId AND status IS NULL
Problem is, that it is possible that there could be two threads concurrently trying to set the status of same record to 'sent' and 'delivered'. In this case 'delivered' is the final status so we would want that to win eventually.
Will the above statements ensure this in MySql or MariaDB?
mysql mariadb concurrentmodification
edited Nov 20 at 18:08
Rick James
65.7k55797
asked Nov 20 at 17:49
AppleGrew
4,0751657102
I saw many similar questions but still I am not fully sure if I am correct.
We have an application which launches a job to mass send many messages. The messages delivery status are received later in batches and in no particular order.
The table structure is like:-
CREATE TABLE `message` (
`pk` char(32) NOT NULL DEFAULT '',
`job_id` varchar(40) DEFAULT NULL,
`status` varchar(40) DEFAULT NULL,
`update_date` datetime DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `job_id` (`job_id`),
KEY `status` (`status`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The records are initially created with status set to null. Its value is expected to change from null to sent and then to delivered. Below statements are used to update the records.
When status to be set is - delivered
Update message SET status = 'delivered', update_date = Now()
WHERE job_id = :someId
When status to be set is - sent
Update message SET status = 'sent', update_date = Now()
WHERE job_id = :someId AND status IS NULL
Problem is, that it is possible that there could be two threads concurrently trying to set the status of same record to 'sent' and 'delivered'. In this case 'delivered' is the final status so we would want that to win eventually.
Will the above statements ensure this in MySql or MariaDB?
mysql mariadb concurrentmodification
mysql mariadb concurrentmodification
edited Nov 20 at 18:08
Rick James
65.7k55797
asked Nov 20 at 17:49
AppleGrew
4,0751657102
edited Nov 20 at 18:08
Rick James
65.7k55797
asked Nov 20 at 17:49
AppleGrew
4,0751657102
edited Nov 20 at 18:08
Rick James
65.7k55797
edited Nov 20 at 18:08
Rick James
65.7k55797
edited Nov 20 at 18:08
Rick James
65.7k55797
65.7k55797
asked Nov 20 at 17:49
AppleGrew
4,0751657102
asked Nov 20 at 17:49
AppleGrew
4,0751657102
asked Nov 20 at 17:49
AppleGrew
4,0751657102
4,0751657102
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
Yes, in both MySQL and MariaDB (and probably any SQL database).
updates to the same row are atomic.
- 'sent' will overwrite NULL, but not 'delivered'
- 'delivered' will overwrite NULL and 'sent'
This is what you want. Just make sure message with given job_id exists before updating it :)
answered Nov 21 at 16:24
Vladislav Vaintroub
4,0491927
add a comment |
Update message SET status = 'delivered', update_date = Now()
WHERE job_id = :someId
AND status = 'sent' -- add this??
(There should be no difference between MySQL and MariaDB in this area.)
answered Nov 20 at 18:12
Rick James
65.7k55797
It is possible that the 'delivered' status was delivered before the 'sent' status. In that case we would want to record the 'delivered' status and ignore the 'sent' one. Anyway what I was trying to arrive at is there a chance that the 'delivered' update is about to update the record at that very instance if 'sent's update too runs and 'sees' that thestatusis still null. In the meantime thestatusgets updated to 'delivered' but then the 'sent' update 'saw' null so it eventually sets it to 'sent'. But I guess relational database being guaranteeing atomicity, this won't happen.
– AppleGrew
Nov 20 at 20:48
1
@AppleGrew - What is the value ofautocommit? Is theUPDATEin aBEGIN...COMMITtransactions? If eachUPDATEis a transaction unto itself, then your original pair ofUPDATEswas perfect.
– Rick James
Nov 20 at 20:53
add a comment |
Update message SET status = 'sent', update_date = Now()
WHERE job_id = :someId
AND status != 'delivered';
answered Nov 20 at 18:50
Croco
157
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%2f53398715%2fcan-my-update-statements-will-work-if-run-concurrently-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Yes, in both MySQL and MariaDB (and probably any SQL database).
updates to the same row are atomic.
- 'sent' will overwrite NULL, but not 'delivered'
- 'delivered' will overwrite NULL and 'sent'
This is what you want. Just make sure message with given job_id exists before updating it :)
answered Nov 21 at 16:24
Vladislav Vaintroub
4,0491927
add a comment |
Yes, in both MySQL and MariaDB (and probably any SQL database).
updates to the same row are atomic.
- 'sent' will overwrite NULL, but not 'delivered'
- 'delivered' will overwrite NULL and 'sent'
This is what you want. Just make sure message with given job_id exists before updating it :)
answered Nov 21 at 16:24
Vladislav Vaintroub
4,0491927
add a comment |
Yes, in both MySQL and MariaDB (and probably any SQL database).
updates to the same row are atomic.
- 'sent' will overwrite NULL, but not 'delivered'
- 'delivered' will overwrite NULL and 'sent'
This is what you want. Just make sure message with given job_id exists before updating it :)
answered Nov 21 at 16:24
Vladislav Vaintroub
4,0491927
Yes, in both MySQL and MariaDB (and probably any SQL database).
updates to the same row are atomic.
- 'sent' will overwrite NULL, but not 'delivered'
- 'delivered' will overwrite NULL and 'sent'
This is what you want. Just make sure message with given job_id exists before updating it :)
answered Nov 21 at 16:24
Vladislav Vaintroub
4,0491927
answered Nov 21 at 16:24
Vladislav Vaintroub
4,0491927
answered Nov 21 at 16:24
Vladislav Vaintroub
4,0491927
answered Nov 21 at 16:24
Vladislav Vaintroub
4,0491927
4,0491927
add a comment |
add a comment |
Update message SET status = 'delivered', update_date = Now()
WHERE job_id = :someId
AND status = 'sent' -- add this??
(There should be no difference between MySQL and MariaDB in this area.)
answered Nov 20 at 18:12
Rick James
65.7k55797
It is possible that the 'delivered' status was delivered before the 'sent' status. In that case we would want to record the 'delivered' status and ignore the 'sent' one. Anyway what I was trying to arrive at is there a chance that the 'delivered' update is about to update the record at that very instance if 'sent's update too runs and 'sees' that thestatusis still null. In the meantime thestatusgets updated to 'delivered' but then the 'sent' update 'saw' null so it eventually sets it to 'sent'. But I guess relational database being guaranteeing atomicity, this won't happen.
– AppleGrew
Nov 20 at 20:48
1
@AppleGrew - What is the value ofautocommit? Is theUPDATEin aBEGIN...COMMITtransactions? If eachUPDATEis a transaction unto itself, then your original pair ofUPDATEswas perfect.
– Rick James
Nov 20 at 20:53
add a comment |
Update message SET status = 'delivered', update_date = Now()
WHERE job_id = :someId
AND status = 'sent' -- add this??
(There should be no difference between MySQL and MariaDB in this area.)
answered Nov 20 at 18:12
Rick James
65.7k55797
It is possible that the 'delivered' status was delivered before the 'sent' status. In that case we would want to record the 'delivered' status and ignore the 'sent' one. Anyway what I was trying to arrive at is there a chance that the 'delivered' update is about to update the record at that very instance if 'sent's update too runs and 'sees' that thestatusis still null. In the meantime thestatusgets updated to 'delivered' but then the 'sent' update 'saw' null so it eventually sets it to 'sent'. But I guess relational database being guaranteeing atomicity, this won't happen.
– AppleGrew
Nov 20 at 20:48
1
@AppleGrew - What is the value ofautocommit? Is theUPDATEin aBEGIN...COMMITtransactions? If eachUPDATEis a transaction unto itself, then your original pair ofUPDATEswas perfect.
– Rick James
Nov 20 at 20:53
add a comment |
Update message SET status = 'delivered', update_date = Now()
WHERE job_id = :someId
AND status = 'sent' -- add this??
(There should be no difference between MySQL and MariaDB in this area.)
answered Nov 20 at 18:12
Rick James
65.7k55797
Update message SET status = 'delivered', update_date = Now()
WHERE job_id = :someId
AND status = 'sent' -- add this??
(There should be no difference between MySQL and MariaDB in this area.)
answered Nov 20 at 18:12
Rick James
65.7k55797
answered Nov 20 at 18:12
Rick James
65.7k55797
answered Nov 20 at 18:12
Rick James
65.7k55797
answered Nov 20 at 18:12
Rick James
65.7k55797
65.7k55797
It is possible that the 'delivered' status was delivered before the 'sent' status. In that case we would want to record the 'delivered' status and ignore the 'sent' one. Anyway what I was trying to arrive at is there a chance that the 'delivered' update is about to update the record at that very instance if 'sent's update too runs and 'sees' that thestatusis still null. In the meantime thestatusgets updated to 'delivered' but then the 'sent' update 'saw' null so it eventually sets it to 'sent'. But I guess relational database being guaranteeing atomicity, this won't happen.
– AppleGrew
Nov 20 at 20:48
1
@AppleGrew - What is the value ofautocommit? Is theUPDATEin aBEGIN...COMMITtransactions? If eachUPDATEis a transaction unto itself, then your original pair ofUPDATEswas perfect.
– Rick James
Nov 20 at 20:53
add a comment |
It is possible that the 'delivered' status was delivered before the 'sent' status. In that case we would want to record the 'delivered' status and ignore the 'sent' one. Anyway what I was trying to arrive at is there a chance that the 'delivered' update is about to update the record at that very instance if 'sent's update too runs and 'sees' that thestatusis still null. In the meantime thestatusgets updated to 'delivered' but then the 'sent' update 'saw' null so it eventually sets it to 'sent'. But I guess relational database being guaranteeing atomicity, this won't happen.
– AppleGrew
Nov 20 at 20:48
1
@AppleGrew - What is the value ofautocommit? Is theUPDATEin aBEGIN...COMMITtransactions? If eachUPDATEis a transaction unto itself, then your original pair ofUPDATEswas perfect.
– Rick James
Nov 20 at 20:53
It is possible that the 'delivered' status was delivered before the 'sent' status. In that case we would want to record the 'delivered' status and ignore the 'sent' one. Anyway what I was trying to arrive at is there a chance that the 'delivered' update is about to update the record at that very instance if 'sent's update too runs and 'sees' that the
status is still null. In the meantime the status gets updated to 'delivered' but then the 'sent' update 'saw' null so it eventually sets it to 'sent'. But I guess relational database being guaranteeing atomicity, this won't happen.– AppleGrew
Nov 20 at 20:48
It is possible that the 'delivered' status was delivered before the 'sent' status. In that case we would want to record the 'delivered' status and ignore the 'sent' one. Anyway what I was trying to arrive at is there a chance that the 'delivered' update is about to update the record at that very instance if 'sent's update too runs and 'sees' that the
status is still null. In the meantime the status gets updated to 'delivered' but then the 'sent' update 'saw' null so it eventually sets it to 'sent'. But I guess relational database being guaranteeing atomicity, this won't happen.– AppleGrew
Nov 20 at 20:48
1
1
@AppleGrew - What is the value of
autocommit? Is the UPDATE in a BEGIN...COMMIT transactions? If each UPDATE is a transaction unto itself, then your original pair of UPDATEs was perfect.– Rick James
Nov 20 at 20:53
@AppleGrew - What is the value of
autocommit? Is the UPDATE in a BEGIN...COMMIT transactions? If each UPDATE is a transaction unto itself, then your original pair of UPDATEs was perfect.– Rick James
Nov 20 at 20:53
add a comment |
Update message SET status = 'sent', update_date = Now()
WHERE job_id = :someId
AND status != 'delivered';
answered Nov 20 at 18:50
Croco
157
add a comment |
Update message SET status = 'sent', update_date = Now()
WHERE job_id = :someId
AND status != 'delivered';
answered Nov 20 at 18:50
Croco
157
add a comment |
Update message SET status = 'sent', update_date = Now()
WHERE job_id = :someId
AND status != 'delivered';
answered Nov 20 at 18:50
Croco
157
Update message SET status = 'sent', update_date = Now()
WHERE job_id = :someId
AND status != 'delivered';
answered Nov 20 at 18:50
Croco
157
answered Nov 20 at 18:50
Croco
157
answered Nov 20 at 18:50
Croco
157
answered Nov 20 at 18:50
Croco
157
157
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53398715%2fcan-my-update-statements-will-work-if-run-concurrently-in-mysql%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
