SELECT … FOR UPDATE SKIP LOCKED in REPETABLE READ transactions
I have the following statement in my PostgreSQL 10.5 database, which I execute in a repeatable read transaction:
delete from task
where task.task_id = (
select task.task_id
from task
order by task.created_at asc
limit 1
for update skip locked
)
returning
task.task_id,
task.created_at
Unfortunately, when I run it, I sometimes get:
[67] ERROR: could not serialize access due to concurrent update
[67] STATEMENT: delete from task
where task.task_id = (
select task.task_id
from task
order by task.created_at asc
limit $1
for update skip locked
)
returning
task.task_id,
task.created_at
which means the transaction rolled back because some other transaction modified the record in the meantime. (I think?)
I don't quite understand this. How could a different transaction modify a record that was selected with for update skip locked, and deleted?
sql postgresql transactions isolation-level locks
edited Nov 13 '18 at 21:07
Erwin Brandstetter
344k67629805
asked Nov 13 '18 at 19:58
YnvYnv
5821817
add a comment |
I have the following statement in my PostgreSQL 10.5 database, which I execute in a repeatable read transaction:
delete from task
where task.task_id = (
select task.task_id
from task
order by task.created_at asc
limit 1
for update skip locked
)
returning
task.task_id,
task.created_at
Unfortunately, when I run it, I sometimes get:
[67] ERROR: could not serialize access due to concurrent update
[67] STATEMENT: delete from task
where task.task_id = (
select task.task_id
from task
order by task.created_at asc
limit $1
for update skip locked
)
returning
task.task_id,
task.created_at
which means the transaction rolled back because some other transaction modified the record in the meantime. (I think?)
I don't quite understand this. How could a different transaction modify a record that was selected with for update skip locked, and deleted?
sql postgresql transactions isolation-level locks
edited Nov 13 '18 at 21:07
Erwin Brandstetter
344k67629805
asked Nov 13 '18 at 19:58
YnvYnv
5821817
add a comment |
I have the following statement in my PostgreSQL 10.5 database, which I execute in a repeatable read transaction:
delete from task
where task.task_id = (
select task.task_id
from task
order by task.created_at asc
limit 1
for update skip locked
)
returning
task.task_id,
task.created_at
Unfortunately, when I run it, I sometimes get:
[67] ERROR: could not serialize access due to concurrent update
[67] STATEMENT: delete from task
where task.task_id = (
select task.task_id
from task
order by task.created_at asc
limit $1
for update skip locked
)
returning
task.task_id,
task.created_at
which means the transaction rolled back because some other transaction modified the record in the meantime. (I think?)
I don't quite understand this. How could a different transaction modify a record that was selected with for update skip locked, and deleted?
sql postgresql transactions isolation-level locks
edited Nov 13 '18 at 21:07
Erwin Brandstetter
344k67629805
asked Nov 13 '18 at 19:58
YnvYnv
5821817
I have the following statement in my PostgreSQL 10.5 database, which I execute in a repeatable read transaction:
delete from task
where task.task_id = (
select task.task_id
from task
order by task.created_at asc
limit 1
for update skip locked
)
returning
task.task_id,
task.created_at
Unfortunately, when I run it, I sometimes get:
[67] ERROR: could not serialize access due to concurrent update
[67] STATEMENT: delete from task
where task.task_id = (
select task.task_id
from task
order by task.created_at asc
limit $1
for update skip locked
)
returning
task.task_id,
task.created_at
which means the transaction rolled back because some other transaction modified the record in the meantime. (I think?)
I don't quite understand this. How could a different transaction modify a record that was selected with for update skip locked, and deleted?
sql postgresql transactions isolation-level locks
sql postgresql transactions isolation-level locks
edited Nov 13 '18 at 21:07
Erwin Brandstetter
344k67629805
asked Nov 13 '18 at 19:58
YnvYnv
5821817
edited Nov 13 '18 at 21:07
Erwin Brandstetter
344k67629805
asked Nov 13 '18 at 19:58
YnvYnv
5821817
edited Nov 13 '18 at 21:07
Erwin Brandstetter
344k67629805
edited Nov 13 '18 at 21:07
Erwin Brandstetter
344k67629805
edited Nov 13 '18 at 21:07
Erwin Brandstetter
344k67629805
344k67629805
asked Nov 13 '18 at 19:58
YnvYnv
5821817
asked Nov 13 '18 at 19:58
YnvYnv
5821817
asked Nov 13 '18 at 19:58
YnvYnv
5821817
5821817
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
This quote from the manual discusses your case exactly:
UPDATE,DELETE,SELECT FOR UPDATE, andSELECT FOR SHAREcommands
behave the same asSELECTin terms of searching for target rows: they
will only find target rows that were committed as of the transaction
start time. However, such a target row might have already been updated
(or deleted or locked) by another concurrent transaction by the time
it is found. In this case, the repeatable read transaction will wait
for the first updating transaction to commit or roll back (if it is
still in progress). If the first updater rolls back, then its effects
are negated and the repeatable read transaction can proceed with
updating the originally found row. But if the first updater commits
(and actually updated or deleted the row, not just locked it) then the
repeatable read transaction will be rolled back with the message
ERROR: could not serialize access due to concurrent update
Meaning, your transaction was unable to lock the row to begin with - due to concurrent write access that got there first. SKIP LOCKED cannot save you from this completely as there may not be a lock to skip any more and we still run into a serialization failure if the row has already been changed (and the change committed - hence the lock released) since transaction start.
The same statement should work just fine with default READ COMMITTED transaction isolation. Related:
- Postgres UPDATE … LIMIT 1
answered Nov 13 '18 at 20:47
Erwin BrandstetterErwin Brandstetter
344k67629805
Thanks for you reply! I see, so before the lock can be taken, but after the transaction starts, a different client manged to delete the row. The reason why I wanted theREPEATABLE READtransaction isolation was because in the same transaction I insert a row into a second table. Would it be still safe to do so withREAD COMMITED?
– Ynv
Nov 14 '18 at 18:39
@Ynv: Yes, a different transaction that deleted or updated and already committed. Still safe withREAD COMMITTED? Probably yes, but that all depends on requirements. Safe against what? I suggest you start a new question with defining details. (If this question is answered properly, consider accepting it.)
– Erwin Brandstetter
Nov 14 '18 at 18:51
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%2f53288584%2fselect-for-update-skip-locked-in-repetable-read-transactions%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
This quote from the manual discusses your case exactly:
UPDATE,DELETE,SELECT FOR UPDATE, andSELECT FOR SHAREcommands
behave the same asSELECTin terms of searching for target rows: they
will only find target rows that were committed as of the transaction
start time. However, such a target row might have already been updated
(or deleted or locked) by another concurrent transaction by the time
it is found. In this case, the repeatable read transaction will wait
for the first updating transaction to commit or roll back (if it is
still in progress). If the first updater rolls back, then its effects
are negated and the repeatable read transaction can proceed with
updating the originally found row. But if the first updater commits
(and actually updated or deleted the row, not just locked it) then the
repeatable read transaction will be rolled back with the message
ERROR: could not serialize access due to concurrent update
Meaning, your transaction was unable to lock the row to begin with - due to concurrent write access that got there first. SKIP LOCKED cannot save you from this completely as there may not be a lock to skip any more and we still run into a serialization failure if the row has already been changed (and the change committed - hence the lock released) since transaction start.
The same statement should work just fine with default READ COMMITTED transaction isolation. Related:
- Postgres UPDATE … LIMIT 1
answered Nov 13 '18 at 20:47
Erwin BrandstetterErwin Brandstetter
344k67629805
Thanks for you reply! I see, so before the lock can be taken, but after the transaction starts, a different client manged to delete the row. The reason why I wanted theREPEATABLE READtransaction isolation was because in the same transaction I insert a row into a second table. Would it be still safe to do so withREAD COMMITED?
– Ynv
Nov 14 '18 at 18:39
@Ynv: Yes, a different transaction that deleted or updated and already committed. Still safe withREAD COMMITTED? Probably yes, but that all depends on requirements. Safe against what? I suggest you start a new question with defining details. (If this question is answered properly, consider accepting it.)
– Erwin Brandstetter
Nov 14 '18 at 18:51
add a comment |
This quote from the manual discusses your case exactly:
UPDATE,DELETE,SELECT FOR UPDATE, andSELECT FOR SHAREcommands
behave the same asSELECTin terms of searching for target rows: they
will only find target rows that were committed as of the transaction
start time. However, such a target row might have already been updated
(or deleted or locked) by another concurrent transaction by the time
it is found. In this case, the repeatable read transaction will wait
for the first updating transaction to commit or roll back (if it is
still in progress). If the first updater rolls back, then its effects
are negated and the repeatable read transaction can proceed with
updating the originally found row. But if the first updater commits
(and actually updated or deleted the row, not just locked it) then the
repeatable read transaction will be rolled back with the message
ERROR: could not serialize access due to concurrent update
Meaning, your transaction was unable to lock the row to begin with - due to concurrent write access that got there first. SKIP LOCKED cannot save you from this completely as there may not be a lock to skip any more and we still run into a serialization failure if the row has already been changed (and the change committed - hence the lock released) since transaction start.
The same statement should work just fine with default READ COMMITTED transaction isolation. Related:
- Postgres UPDATE … LIMIT 1
answered Nov 13 '18 at 20:47
Erwin BrandstetterErwin Brandstetter
344k67629805
Thanks for you reply! I see, so before the lock can be taken, but after the transaction starts, a different client manged to delete the row. The reason why I wanted theREPEATABLE READtransaction isolation was because in the same transaction I insert a row into a second table. Would it be still safe to do so withREAD COMMITED?
– Ynv
Nov 14 '18 at 18:39
@Ynv: Yes, a different transaction that deleted or updated and already committed. Still safe withREAD COMMITTED? Probably yes, but that all depends on requirements. Safe against what? I suggest you start a new question with defining details. (If this question is answered properly, consider accepting it.)
– Erwin Brandstetter
Nov 14 '18 at 18:51
add a comment |
This quote from the manual discusses your case exactly:
UPDATE,DELETE,SELECT FOR UPDATE, andSELECT FOR SHAREcommands
behave the same asSELECTin terms of searching for target rows: they
will only find target rows that were committed as of the transaction
start time. However, such a target row might have already been updated
(or deleted or locked) by another concurrent transaction by the time
it is found. In this case, the repeatable read transaction will wait
for the first updating transaction to commit or roll back (if it is
still in progress). If the first updater rolls back, then its effects
are negated and the repeatable read transaction can proceed with
updating the originally found row. But if the first updater commits
(and actually updated or deleted the row, not just locked it) then the
repeatable read transaction will be rolled back with the message
ERROR: could not serialize access due to concurrent update
Meaning, your transaction was unable to lock the row to begin with - due to concurrent write access that got there first. SKIP LOCKED cannot save you from this completely as there may not be a lock to skip any more and we still run into a serialization failure if the row has already been changed (and the change committed - hence the lock released) since transaction start.
The same statement should work just fine with default READ COMMITTED transaction isolation. Related:
- Postgres UPDATE … LIMIT 1
answered Nov 13 '18 at 20:47
Erwin BrandstetterErwin Brandstetter
344k67629805
This quote from the manual discusses your case exactly:
UPDATE,DELETE,SELECT FOR UPDATE, andSELECT FOR SHAREcommands
behave the same asSELECTin terms of searching for target rows: they
will only find target rows that were committed as of the transaction
start time. However, such a target row might have already been updated
(or deleted or locked) by another concurrent transaction by the time
it is found. In this case, the repeatable read transaction will wait
for the first updating transaction to commit or roll back (if it is
still in progress). If the first updater rolls back, then its effects
are negated and the repeatable read transaction can proceed with
updating the originally found row. But if the first updater commits
(and actually updated or deleted the row, not just locked it) then the
repeatable read transaction will be rolled back with the message
ERROR: could not serialize access due to concurrent update
Meaning, your transaction was unable to lock the row to begin with - due to concurrent write access that got there first. SKIP LOCKED cannot save you from this completely as there may not be a lock to skip any more and we still run into a serialization failure if the row has already been changed (and the change committed - hence the lock released) since transaction start.
The same statement should work just fine with default READ COMMITTED transaction isolation. Related:
- Postgres UPDATE … LIMIT 1
answered Nov 13 '18 at 20:47
Erwin BrandstetterErwin Brandstetter
344k67629805
edited Nov 13 '18 at 20:58
answered Nov 13 '18 at 20:47
Erwin BrandstetterErwin Brandstetter
344k67629805
answered Nov 13 '18 at 20:47
Erwin BrandstetterErwin Brandstetter
344k67629805
answered Nov 13 '18 at 20:47
Erwin BrandstetterErwin Brandstetter
344k67629805
344k67629805
Thanks for you reply! I see, so before the lock can be taken, but after the transaction starts, a different client manged to delete the row. The reason why I wanted theREPEATABLE READtransaction isolation was because in the same transaction I insert a row into a second table. Would it be still safe to do so withREAD COMMITED?
– Ynv
Nov 14 '18 at 18:39
@Ynv: Yes, a different transaction that deleted or updated and already committed. Still safe withREAD COMMITTED? Probably yes, but that all depends on requirements. Safe against what? I suggest you start a new question with defining details. (If this question is answered properly, consider accepting it.)
– Erwin Brandstetter
Nov 14 '18 at 18:51
add a comment |
Thanks for you reply! I see, so before the lock can be taken, but after the transaction starts, a different client manged to delete the row. The reason why I wanted theREPEATABLE READtransaction isolation was because in the same transaction I insert a row into a second table. Would it be still safe to do so withREAD COMMITED?
– Ynv
Nov 14 '18 at 18:39
@Ynv: Yes, a different transaction that deleted or updated and already committed. Still safe withREAD COMMITTED? Probably yes, but that all depends on requirements. Safe against what? I suggest you start a new question with defining details. (If this question is answered properly, consider accepting it.)
– Erwin Brandstetter
Nov 14 '18 at 18:51
Thanks for you reply! I see, so before the lock can be taken, but after the transaction starts, a different client manged to delete the row. The reason why I wanted the
REPEATABLE READ transaction isolation was because in the same transaction I insert a row into a second table. Would it be still safe to do so with READ COMMITED?– Ynv
Nov 14 '18 at 18:39
Thanks for you reply! I see, so before the lock can be taken, but after the transaction starts, a different client manged to delete the row. The reason why I wanted the
REPEATABLE READ transaction isolation was because in the same transaction I insert a row into a second table. Would it be still safe to do so with READ COMMITED?– Ynv
Nov 14 '18 at 18:39
@Ynv: Yes, a different transaction that deleted or updated and already committed. Still safe with
READ COMMITTED? Probably yes, but that all depends on requirements. Safe against what? I suggest you start a new question with defining details. (If this question is answered properly, consider accepting it.)– Erwin Brandstetter
Nov 14 '18 at 18:51
@Ynv: Yes, a different transaction that deleted or updated and already committed. Still safe with
READ COMMITTED? Probably yes, but that all depends on requirements. Safe against what? I suggest you start a new question with defining details. (If this question is answered properly, consider accepting it.)– Erwin Brandstetter
Nov 14 '18 at 18:51
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%2f53288584%2fselect-for-update-skip-locked-in-repetable-read-transactions%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
