How can I force MySQL to obtain a table-lock for a transaction?
I'm trying to perform an operation on a MySQL database table using the InnoDB storage engine. This operation is an INSERT-or-UPDATE type operation where I have an incoming set of data and there may be some data already in the table which must be updated. For example, I might have this table:
test_table
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| value | varchar(255) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
... and some sample data:
+----+-------+
| id | value |
+----+-------+
| 1 | foo |
| 2 | bar |
| 3 | baz |
+----+-------+
Now, I want to "merge" the following values:
2, qux
4, corge
My code ultimately ends up issuing the following queries:
BEGIN;
SELECT id, value FROM test WHERE id=2 FOR UPDATE;
UPDATE test SET id=2, value='qux' WHERE id=2;
INSERT INTO test (id, value) VALUES (4, 'corge');
COMMIT;
(I'm not precisely sure what happens with the SELECT ... FOR UPDATE
and the UPDATE
because I'm using MySQL's Connector/J library for Java and simply calling the updateRow
method on a ResultSet
. For the sake of argument, let's assume that the queries above are actually what are being issued to the server.)
Note: the above table is a trivial example to illustrate my question. The real table is more complicated and I'm not using the PK as the field to match when executing SELECT ... FOR UPDATE
. So it's not obvious whether the record needs to be INSERTed or UPDATEd by just looking at the incoming data. The database MUST be consulted to determine whether to use an INSERT/UPDATE.
The above queries work just fine most of the time. However, when there are more records to be "merged", the SELECT ... FOR UPDATE
and INSERT
lines can be interleaved, where I cannot predict whether SELECT ... FOR UPDATE
or INSERT
will be issued and in what order.
The result is that sometimes transactions deadlock because one thread has locked a part of the table for the UPDATE
operation and is waiting on a table lock (for the INSERT
, which requires a lock on the primary-key index), while another thread has already obtained a table lock for the primary key (presumably because it issued an INSERT
query) and is now waiting for a row-lock (or, more likely, a page-level lock) which is held by the first thread.
This is the only place in the code where this table is updated and there are no explicit locks currently being obtained. The ordering of the UPDATE
versus INSERT
seems to be the root of the issue.
There are a few possibilities I can think of to "fix" this.
- Detect the deadlock (MySQL throws an error) and simply re-try. This is my current implementation because the problem is somewhat rare. It happens a few times per day.
- Use
LOCK TABLES
to obtain a table-lock before the merge process andUNLOCK TABLES
afterward. This evidently won't work with MariaDB Galera -- which is likely in our future for this product. - Change the code to always issue
INSERT
queries first. This would result in any table-level locks being acquired first and avoid the deadlock.
The problem with #3 is that it will require more complicated code in a method that is already fairly complicated (a "merge" operation is inherently complex). That more-complicated code also means roughly double the number of queries (SELECT
to determine if the row id already exists, then later, another SELECT ... FOR UPDATE
/UPDATE
to actually update it). This table is under a reasonable amount of contention, so I'd like to avoid issuing more queries if possible.
Is there a way to force MySQL to obtain a table-level lock without using LOCK TABLES
? That is, in a way that will work if we move to Galera?
mysql locking rowlocking
|
show 4 more comments
I'm trying to perform an operation on a MySQL database table using the InnoDB storage engine. This operation is an INSERT-or-UPDATE type operation where I have an incoming set of data and there may be some data already in the table which must be updated. For example, I might have this table:
test_table
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| value | varchar(255) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
... and some sample data:
+----+-------+
| id | value |
+----+-------+
| 1 | foo |
| 2 | bar |
| 3 | baz |
+----+-------+
Now, I want to "merge" the following values:
2, qux
4, corge
My code ultimately ends up issuing the following queries:
BEGIN;
SELECT id, value FROM test WHERE id=2 FOR UPDATE;
UPDATE test SET id=2, value='qux' WHERE id=2;
INSERT INTO test (id, value) VALUES (4, 'corge');
COMMIT;
(I'm not precisely sure what happens with the SELECT ... FOR UPDATE
and the UPDATE
because I'm using MySQL's Connector/J library for Java and simply calling the updateRow
method on a ResultSet
. For the sake of argument, let's assume that the queries above are actually what are being issued to the server.)
Note: the above table is a trivial example to illustrate my question. The real table is more complicated and I'm not using the PK as the field to match when executing SELECT ... FOR UPDATE
. So it's not obvious whether the record needs to be INSERTed or UPDATEd by just looking at the incoming data. The database MUST be consulted to determine whether to use an INSERT/UPDATE.
The above queries work just fine most of the time. However, when there are more records to be "merged", the SELECT ... FOR UPDATE
and INSERT
lines can be interleaved, where I cannot predict whether SELECT ... FOR UPDATE
or INSERT
will be issued and in what order.
The result is that sometimes transactions deadlock because one thread has locked a part of the table for the UPDATE
operation and is waiting on a table lock (for the INSERT
, which requires a lock on the primary-key index), while another thread has already obtained a table lock for the primary key (presumably because it issued an INSERT
query) and is now waiting for a row-lock (or, more likely, a page-level lock) which is held by the first thread.
This is the only place in the code where this table is updated and there are no explicit locks currently being obtained. The ordering of the UPDATE
versus INSERT
seems to be the root of the issue.
There are a few possibilities I can think of to "fix" this.
- Detect the deadlock (MySQL throws an error) and simply re-try. This is my current implementation because the problem is somewhat rare. It happens a few times per day.
- Use
LOCK TABLES
to obtain a table-lock before the merge process andUNLOCK TABLES
afterward. This evidently won't work with MariaDB Galera -- which is likely in our future for this product. - Change the code to always issue
INSERT
queries first. This would result in any table-level locks being acquired first and avoid the deadlock.
The problem with #3 is that it will require more complicated code in a method that is already fairly complicated (a "merge" operation is inherently complex). That more-complicated code also means roughly double the number of queries (SELECT
to determine if the row id already exists, then later, another SELECT ... FOR UPDATE
/UPDATE
to actually update it). This table is under a reasonable amount of contention, so I'd like to avoid issuing more queries if possible.
Is there a way to force MySQL to obtain a table-level lock without using LOCK TABLES
? That is, in a way that will work if we move to Galera?
mysql locking rowlocking
Why do you need toSET ID=2
when that's already the ID?
– Barmar
Nov 14 '18 at 21:34
Is there a reason you're not usingINSERT ... ON DUPLICATE KEY UPDATE ...
?
– Barmar
Nov 14 '18 at 21:35
@Barmar I don't know why Connector/J re-assigns the PK. It just does (bugs.mysql.com/bug.php?id=71143).
– Christopher Schultz
Nov 14 '18 at 21:39
@Barmar I'm not usingINSERT ... ON DUPLICATE KEY UPDATE
because it will never replace an existingvalue
in the table... only create a new record. If I want to replace "bar" with "qux" in the example,INSERT ... ON DUPLICATE KEY
cannot be used at all.
– Christopher Schultz
Nov 14 '18 at 21:42
1
Of course it will, that's the whole point.
– Barmar
Nov 14 '18 at 21:51
|
show 4 more comments
I'm trying to perform an operation on a MySQL database table using the InnoDB storage engine. This operation is an INSERT-or-UPDATE type operation where I have an incoming set of data and there may be some data already in the table which must be updated. For example, I might have this table:
test_table
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| value | varchar(255) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
... and some sample data:
+----+-------+
| id | value |
+----+-------+
| 1 | foo |
| 2 | bar |
| 3 | baz |
+----+-------+
Now, I want to "merge" the following values:
2, qux
4, corge
My code ultimately ends up issuing the following queries:
BEGIN;
SELECT id, value FROM test WHERE id=2 FOR UPDATE;
UPDATE test SET id=2, value='qux' WHERE id=2;
INSERT INTO test (id, value) VALUES (4, 'corge');
COMMIT;
(I'm not precisely sure what happens with the SELECT ... FOR UPDATE
and the UPDATE
because I'm using MySQL's Connector/J library for Java and simply calling the updateRow
method on a ResultSet
. For the sake of argument, let's assume that the queries above are actually what are being issued to the server.)
Note: the above table is a trivial example to illustrate my question. The real table is more complicated and I'm not using the PK as the field to match when executing SELECT ... FOR UPDATE
. So it's not obvious whether the record needs to be INSERTed or UPDATEd by just looking at the incoming data. The database MUST be consulted to determine whether to use an INSERT/UPDATE.
The above queries work just fine most of the time. However, when there are more records to be "merged", the SELECT ... FOR UPDATE
and INSERT
lines can be interleaved, where I cannot predict whether SELECT ... FOR UPDATE
or INSERT
will be issued and in what order.
The result is that sometimes transactions deadlock because one thread has locked a part of the table for the UPDATE
operation and is waiting on a table lock (for the INSERT
, which requires a lock on the primary-key index), while another thread has already obtained a table lock for the primary key (presumably because it issued an INSERT
query) and is now waiting for a row-lock (or, more likely, a page-level lock) which is held by the first thread.
This is the only place in the code where this table is updated and there are no explicit locks currently being obtained. The ordering of the UPDATE
versus INSERT
seems to be the root of the issue.
There are a few possibilities I can think of to "fix" this.
- Detect the deadlock (MySQL throws an error) and simply re-try. This is my current implementation because the problem is somewhat rare. It happens a few times per day.
- Use
LOCK TABLES
to obtain a table-lock before the merge process andUNLOCK TABLES
afterward. This evidently won't work with MariaDB Galera -- which is likely in our future for this product. - Change the code to always issue
INSERT
queries first. This would result in any table-level locks being acquired first and avoid the deadlock.
The problem with #3 is that it will require more complicated code in a method that is already fairly complicated (a "merge" operation is inherently complex). That more-complicated code also means roughly double the number of queries (SELECT
to determine if the row id already exists, then later, another SELECT ... FOR UPDATE
/UPDATE
to actually update it). This table is under a reasonable amount of contention, so I'd like to avoid issuing more queries if possible.
Is there a way to force MySQL to obtain a table-level lock without using LOCK TABLES
? That is, in a way that will work if we move to Galera?
mysql locking rowlocking
I'm trying to perform an operation on a MySQL database table using the InnoDB storage engine. This operation is an INSERT-or-UPDATE type operation where I have an incoming set of data and there may be some data already in the table which must be updated. For example, I might have this table:
test_table
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| value | varchar(255) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
... and some sample data:
+----+-------+
| id | value |
+----+-------+
| 1 | foo |
| 2 | bar |
| 3 | baz |
+----+-------+
Now, I want to "merge" the following values:
2, qux
4, corge
My code ultimately ends up issuing the following queries:
BEGIN;
SELECT id, value FROM test WHERE id=2 FOR UPDATE;
UPDATE test SET id=2, value='qux' WHERE id=2;
INSERT INTO test (id, value) VALUES (4, 'corge');
COMMIT;
(I'm not precisely sure what happens with the SELECT ... FOR UPDATE
and the UPDATE
because I'm using MySQL's Connector/J library for Java and simply calling the updateRow
method on a ResultSet
. For the sake of argument, let's assume that the queries above are actually what are being issued to the server.)
Note: the above table is a trivial example to illustrate my question. The real table is more complicated and I'm not using the PK as the field to match when executing SELECT ... FOR UPDATE
. So it's not obvious whether the record needs to be INSERTed or UPDATEd by just looking at the incoming data. The database MUST be consulted to determine whether to use an INSERT/UPDATE.
The above queries work just fine most of the time. However, when there are more records to be "merged", the SELECT ... FOR UPDATE
and INSERT
lines can be interleaved, where I cannot predict whether SELECT ... FOR UPDATE
or INSERT
will be issued and in what order.
The result is that sometimes transactions deadlock because one thread has locked a part of the table for the UPDATE
operation and is waiting on a table lock (for the INSERT
, which requires a lock on the primary-key index), while another thread has already obtained a table lock for the primary key (presumably because it issued an INSERT
query) and is now waiting for a row-lock (or, more likely, a page-level lock) which is held by the first thread.
This is the only place in the code where this table is updated and there are no explicit locks currently being obtained. The ordering of the UPDATE
versus INSERT
seems to be the root of the issue.
There are a few possibilities I can think of to "fix" this.
- Detect the deadlock (MySQL throws an error) and simply re-try. This is my current implementation because the problem is somewhat rare. It happens a few times per day.
- Use
LOCK TABLES
to obtain a table-lock before the merge process andUNLOCK TABLES
afterward. This evidently won't work with MariaDB Galera -- which is likely in our future for this product. - Change the code to always issue
INSERT
queries first. This would result in any table-level locks being acquired first and avoid the deadlock.
The problem with #3 is that it will require more complicated code in a method that is already fairly complicated (a "merge" operation is inherently complex). That more-complicated code also means roughly double the number of queries (SELECT
to determine if the row id already exists, then later, another SELECT ... FOR UPDATE
/UPDATE
to actually update it). This table is under a reasonable amount of contention, so I'd like to avoid issuing more queries if possible.
Is there a way to force MySQL to obtain a table-level lock without using LOCK TABLES
? That is, in a way that will work if we move to Galera?
mysql locking rowlocking
mysql locking rowlocking
asked Nov 14 '18 at 21:05
Christopher SchultzChristopher Schultz
14.5k24156
14.5k24156
Why do you need toSET ID=2
when that's already the ID?
– Barmar
Nov 14 '18 at 21:34
Is there a reason you're not usingINSERT ... ON DUPLICATE KEY UPDATE ...
?
– Barmar
Nov 14 '18 at 21:35
@Barmar I don't know why Connector/J re-assigns the PK. It just does (bugs.mysql.com/bug.php?id=71143).
– Christopher Schultz
Nov 14 '18 at 21:39
@Barmar I'm not usingINSERT ... ON DUPLICATE KEY UPDATE
because it will never replace an existingvalue
in the table... only create a new record. If I want to replace "bar" with "qux" in the example,INSERT ... ON DUPLICATE KEY
cannot be used at all.
– Christopher Schultz
Nov 14 '18 at 21:42
1
Of course it will, that's the whole point.
– Barmar
Nov 14 '18 at 21:51
|
show 4 more comments
Why do you need toSET ID=2
when that's already the ID?
– Barmar
Nov 14 '18 at 21:34
Is there a reason you're not usingINSERT ... ON DUPLICATE KEY UPDATE ...
?
– Barmar
Nov 14 '18 at 21:35
@Barmar I don't know why Connector/J re-assigns the PK. It just does (bugs.mysql.com/bug.php?id=71143).
– Christopher Schultz
Nov 14 '18 at 21:39
@Barmar I'm not usingINSERT ... ON DUPLICATE KEY UPDATE
because it will never replace an existingvalue
in the table... only create a new record. If I want to replace "bar" with "qux" in the example,INSERT ... ON DUPLICATE KEY
cannot be used at all.
– Christopher Schultz
Nov 14 '18 at 21:42
1
Of course it will, that's the whole point.
– Barmar
Nov 14 '18 at 21:51
Why do you need to
SET ID=2
when that's already the ID?– Barmar
Nov 14 '18 at 21:34
Why do you need to
SET ID=2
when that's already the ID?– Barmar
Nov 14 '18 at 21:34
Is there a reason you're not using
INSERT ... ON DUPLICATE KEY UPDATE ...
?– Barmar
Nov 14 '18 at 21:35
Is there a reason you're not using
INSERT ... ON DUPLICATE KEY UPDATE ...
?– Barmar
Nov 14 '18 at 21:35
@Barmar I don't know why Connector/J re-assigns the PK. It just does (bugs.mysql.com/bug.php?id=71143).
– Christopher Schultz
Nov 14 '18 at 21:39
@Barmar I don't know why Connector/J re-assigns the PK. It just does (bugs.mysql.com/bug.php?id=71143).
– Christopher Schultz
Nov 14 '18 at 21:39
@Barmar I'm not using
INSERT ... ON DUPLICATE KEY UPDATE
because it will never replace an existing value
in the table... only create a new record. If I want to replace "bar" with "qux" in the example, INSERT ... ON DUPLICATE KEY
cannot be used at all.– Christopher Schultz
Nov 14 '18 at 21:42
@Barmar I'm not using
INSERT ... ON DUPLICATE KEY UPDATE
because it will never replace an existing value
in the table... only create a new record. If I want to replace "bar" with "qux" in the example, INSERT ... ON DUPLICATE KEY
cannot be used at all.– Christopher Schultz
Nov 14 '18 at 21:42
1
1
Of course it will, that's the whole point.
– Barmar
Nov 14 '18 at 21:51
Of course it will, that's the whole point.
– Barmar
Nov 14 '18 at 21:51
|
show 4 more comments
1 Answer
1
active
oldest
votes
I think you may be able to do what you want by acquiring a set of row and gap locks:
START TRANSACTION;
SELECT id, value
FROM test
WHERE id in (2, 4) -- list all the IDs you need to UPSERT
FOR UPDATE;
UPDATE test SET value = 'qux' WHERE id = 2;
INSERT INTO test (id, value) VALUES (4, 'corge');
COMMIT;
The SELECT
query will lock the rows that already exist, and create gap locks for the rows that don't exist yet. The gap locks will prevent other transactions from creating those rows.
I will have to update the question with a more complicated example. I believeINSERT ... ON DUPLICATE KEY
will not work for my use-case.
– Christopher Schultz
Nov 14 '18 at 21:53
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%2f53308704%2fhow-can-i-force-mysql-to-obtain-a-table-lock-for-a-transaction%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
I think you may be able to do what you want by acquiring a set of row and gap locks:
START TRANSACTION;
SELECT id, value
FROM test
WHERE id in (2, 4) -- list all the IDs you need to UPSERT
FOR UPDATE;
UPDATE test SET value = 'qux' WHERE id = 2;
INSERT INTO test (id, value) VALUES (4, 'corge');
COMMIT;
The SELECT
query will lock the rows that already exist, and create gap locks for the rows that don't exist yet. The gap locks will prevent other transactions from creating those rows.
I will have to update the question with a more complicated example. I believeINSERT ... ON DUPLICATE KEY
will not work for my use-case.
– Christopher Schultz
Nov 14 '18 at 21:53
add a comment |
I think you may be able to do what you want by acquiring a set of row and gap locks:
START TRANSACTION;
SELECT id, value
FROM test
WHERE id in (2, 4) -- list all the IDs you need to UPSERT
FOR UPDATE;
UPDATE test SET value = 'qux' WHERE id = 2;
INSERT INTO test (id, value) VALUES (4, 'corge');
COMMIT;
The SELECT
query will lock the rows that already exist, and create gap locks for the rows that don't exist yet. The gap locks will prevent other transactions from creating those rows.
I will have to update the question with a more complicated example. I believeINSERT ... ON DUPLICATE KEY
will not work for my use-case.
– Christopher Schultz
Nov 14 '18 at 21:53
add a comment |
I think you may be able to do what you want by acquiring a set of row and gap locks:
START TRANSACTION;
SELECT id, value
FROM test
WHERE id in (2, 4) -- list all the IDs you need to UPSERT
FOR UPDATE;
UPDATE test SET value = 'qux' WHERE id = 2;
INSERT INTO test (id, value) VALUES (4, 'corge');
COMMIT;
The SELECT
query will lock the rows that already exist, and create gap locks for the rows that don't exist yet. The gap locks will prevent other transactions from creating those rows.
I think you may be able to do what you want by acquiring a set of row and gap locks:
START TRANSACTION;
SELECT id, value
FROM test
WHERE id in (2, 4) -- list all the IDs you need to UPSERT
FOR UPDATE;
UPDATE test SET value = 'qux' WHERE id = 2;
INSERT INTO test (id, value) VALUES (4, 'corge');
COMMIT;
The SELECT
query will lock the rows that already exist, and create gap locks for the rows that don't exist yet. The gap locks will prevent other transactions from creating those rows.
edited Nov 14 '18 at 22:22
answered Nov 14 '18 at 21:52
BarmarBarmar
428k36250352
428k36250352
I will have to update the question with a more complicated example. I believeINSERT ... ON DUPLICATE KEY
will not work for my use-case.
– Christopher Schultz
Nov 14 '18 at 21:53
add a comment |
I will have to update the question with a more complicated example. I believeINSERT ... ON DUPLICATE KEY
will not work for my use-case.
– Christopher Schultz
Nov 14 '18 at 21:53
I will have to update the question with a more complicated example. I believe
INSERT ... ON DUPLICATE KEY
will not work for my use-case.– Christopher Schultz
Nov 14 '18 at 21:53
I will have to update the question with a more complicated example. I believe
INSERT ... ON DUPLICATE KEY
will not work for my use-case.– Christopher Schultz
Nov 14 '18 at 21:53
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%2f53308704%2fhow-can-i-force-mysql-to-obtain-a-table-lock-for-a-transaction%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
Why do you need to
SET ID=2
when that's already the ID?– Barmar
Nov 14 '18 at 21:34
Is there a reason you're not using
INSERT ... ON DUPLICATE KEY UPDATE ...
?– Barmar
Nov 14 '18 at 21:35
@Barmar I don't know why Connector/J re-assigns the PK. It just does (bugs.mysql.com/bug.php?id=71143).
– Christopher Schultz
Nov 14 '18 at 21:39
@Barmar I'm not using
INSERT ... ON DUPLICATE KEY UPDATE
because it will never replace an existingvalue
in the table... only create a new record. If I want to replace "bar" with "qux" in the example,INSERT ... ON DUPLICATE KEY
cannot be used at all.– Christopher Schultz
Nov 14 '18 at 21:42
1
Of course it will, that's the whole point.
– Barmar
Nov 14 '18 at 21:51