[I've posted this on Stack Overflow: https://stackoverflow.com/questions/63194116/upsert-with-non-unique-index]
I need to implement concurrent-safe UPSERT using a non-unique key and
avoid unnecessary auto-increment of ID.
Traditional INSERT ... ON DUPLICATE KEY [1] doesn't work for me, so I'm performing:
INSERT INTO table (col1, col2, col3, col4, col5)
SELECT 1, 2, 'value3', 'value4', 'value5'
WHERE NOT EXISTS (SELECT 1
FROM table
WHERE col3 = 'value3'
AND col4 = 'value4'
AND col5 = 'value5')
then if it results in no row inserted, I'm performing:
UPDATE table
SET col1 = col1 + 1,
col2 = MAX(col2, 2)
WHERE col3 = 'value3'
AND col4 = 'value4'
AND col5 = 'value5'
There's an index:
CREATE INDEX ON table (col3, col4, col5)
It is non-unique as there are legacy data that does not allow me to
declare it unique. Newer records, however, should not have duplicated (col3, col4, col5) rows.
Unsurprisingly, using the given INSERT statement I'm getting mixed
results trying to execute it concurrently from two sessions. I can see
the second session blocking until the first one commits its transaction,
but then the second transaction is also able to insert a new row
sometimes (or sometimes it achieves the expected of avoiding to insert a duplicate (col3, col4, col5) row).
I need to implement concurrent-safe UPSERT...
Traditional INSERT ... ON DUPLICATE KEY [1] doesn't work for me...
There's an index:
CREATE INDEX ON table (col3, col4, col5)
It is non-unique as there are legacy data that does not allow me to declare it unique.
On 02/08/2020 19:15, J.O. Aho wrote:
On 02/08/2020 18.55, Stanimir Stamenkov wrote:I don't know how SQL manages it, but if you have two asynchronous
[I've posted this on Stack Overflow:
https://stackoverflow.com/questions/63194116/upsert-with-non-unique-index] >>>
I need to implement concurrent-safe UPSERT using a non-unique key and
avoid unnecessary auto-increment of ID.
Traditional INSERT ... ON DUPLICATE KEY [1] doesn't work for me, so
I'm performing:
INSERT INTO table (col1, col2, col3, col4, col5)
SELECT 1, 2, 'value3', 'value4', 'value5'
WHERE NOT EXISTS (SELECT 1
FROM table
WHERE col3 = 'value3'
AND col4 = 'value4'
AND col5 = 'value5')
then if it results in no row inserted, I'm performing:
UPDATE table
SET col1 = col1 + 1,
col2 = MAX(col2, 2)
WHERE col3 = 'value3'
AND col4 = 'value4'
AND col5 = 'value5'
There's an index:
CREATE INDEX ON table (col3, col4, col5)
It is non-unique as there are legacy data that does not allow me to
declare it unique. Newer records, however, should not have duplicated
(col3, col4, col5) rows.
Unsurprisingly, using the given INSERT statement I'm getting mixed
results trying to execute it concurrently from two sessions. I can
see the second session blocking until the first one commits its
transaction, but then the second transaction is also able to insert a
new row sometimes (or sometimes it achieves the expected of avoiding
to insert a duplicate (col3, col4, col5) row).
Maybe the blocking is more then way you are testing things than what in
reality happens.
I would suggest you use
LOCK TABLES table WRITE;
<do what you did before>
UNLOCK TABLES;
LOCK TABLES table WRITE;
UNLOCK TABLES;
https://www.mysqltutorial.org/mysql-table-locking/
https://dev.mysql.com/doc/refman/5.7/en/lock-tables.html
instances of read-modify-write, where what is written depends on what is read, then you MUST make the read-modify-write cycle ATOMIC - that is uninterruptible, by another process doing the same.
You need locking, and the attendant danger that if aprocess crashes with locks set...
On 02/08/2020 18.55, Stanimir Stamenkov wrote:
[I've posted this on Stack Overflow:
https://stackoverflow.com/questions/63194116/upsert-with-non-unique-index] >>
I need to implement concurrent-safe UPSERT using a non-unique key and
avoid unnecessary auto-increment of ID.
Traditional INSERT ... ON DUPLICATE KEY [1] doesn't work for me, so
I'm performing:
INSERT INTO table (col1, col2, col3, col4, col5)
SELECT 1, 2, 'value3', 'value4', 'value5'
WHERE NOT EXISTS (SELECT 1
FROM table
WHERE col3 = 'value3'
AND col4 = 'value4'
AND col5 = 'value5')
then if it results in no row inserted, I'm performing:
UPDATE table
SET col1 = col1 + 1,
col2 = MAX(col2, 2)
WHERE col3 = 'value3'
AND col4 = 'value4'
AND col5 = 'value5'
There's an index:
CREATE INDEX ON table (col3, col4, col5)
It is non-unique as there are legacy data that does not allow me to
declare it unique. Newer records, however, should not have duplicated
(col3, col4, col5) rows.
Unsurprisingly, using the given INSERT statement I'm getting mixed
results trying to execute it concurrently from two sessions. I can
see the second session blocking until the first one commits its
transaction, but then the second transaction is also able to insert a
new row sometimes (or sometimes it achieves the expected of avoiding
to insert a duplicate (col3, col4, col5) row).
Maybe the blocking is more then way you are testing things than what in reality happens.
I would suggest you use
LOCK TABLES table WRITE;
<do what you did before>
UNLOCK TABLES;
LOCK TABLES table WRITE;
UNLOCK TABLES;
https://www.mysqltutorial.org/mysql-table-locking/ https://dev.mysql.com/doc/refman/5.7/en/lock-tables.html
On August 2, 2020 17:28, The Natural Philosopher wrote:
On 02/08/2020 19:15, J.O. Aho wrote:
On 02/08/2020 18.55, Stanimir Stamenkov wrote:I don't know how SQL manages it, but if you have two asynchronous
[I've posted this on Stack Overflow:
https://stackoverflow.com/questions/63194116/upsert-with-non-unique-index] >>>>
I need to implement concurrent-safe UPSERT using a non-unique key and
avoid unnecessary auto-increment of ID.
Traditional INSERT ... ON DUPLICATE KEY [1] doesn't work for me, so
I'm performing:
INSERT INTO table (col1, col2, col3, col4, col5)
SELECT 1, 2, 'value3', 'value4', 'value5'
WHERE NOT EXISTS (SELECT 1
FROM table
WHERE col3 = 'value3'
AND col4 = 'value4'
AND col5 = 'value5')
then if it results in no row inserted, I'm performing:
UPDATE table
SET col1 = col1 + 1,
col2 = MAX(col2, 2)
WHERE col3 = 'value3'
AND col4 = 'value4'
AND col5 = 'value5'
There's an index:
CREATE INDEX ON table (col3, col4, col5)
It is non-unique as there are legacy data that does not allow me to
declare it unique. Newer records, however, should not have duplicated >>>> (col3, col4, col5) rows.
Unsurprisingly, using the given INSERT statement I'm getting mixed
results trying to execute it concurrently from two sessions. I can
see the second session blocking until the first one commits its
transaction, but then the second transaction is also able to insert a
new row sometimes (or sometimes it achieves the expected of avoiding
to insert a duplicate (col3, col4, col5) row).
Maybe the blocking is more then way you are testing things than what in
reality happens.
I would suggest you use
LOCK TABLES table WRITE;
<do what you did before>
UNLOCK TABLES;
LOCK TABLES table WRITE;
UNLOCK TABLES;
https://www.mysqltutorial.org/mysql-table-locking/
https://dev.mysql.com/doc/refman/5.7/en/lock-tables.html
instances of read-modify-write, where what is written depends on what is
read, then you MUST make the read-modify-write cycle ATOMIC - that is
uninterruptible, by another process doing the same.
You need locking, and the attendant danger that if aprocess crashes with
locks set...
I'd roll the whole thing in a transaction
START TRANSACTION;
-- your sql goes here
COMMIT;
On 02/08/2020 22:46, Lew Pitcher wrote:
I'd roll the whole thing in a transactionI don't use SQL often enough to remember that it has transactions these
START TRANSACTION;
-- your sql goes here
COMMIT;
days :-(
Note that you appear to need to be using innoDB or another advanced
engine and not MyISAM if you want to use transactions
On 03/08/2020 11.05, The Natural Philosopher wrote:
On 02/08/2020 22:46, Lew Pitcher wrote:
I'd roll the whole thing in a transactionI don't use SQL often enough to remember that it has transactions
START TRANSACTION;
-- your sql goes here
COMMIT;
these days :-(
Yeah, it's quite recently they introduced xa transactions in MySQL, 2005.
Note that you appear to need to be using innoDB or another advanced
engine and not MyISAM if you want to use transactions
Yes, you need a db-engine like InnoDB or XtraDB which both support xa transactions.
On 02.08.2020 18:55, Stanimir Stamenkov wrote:
I need to implement concurrent-safe UPSERT...
Traditional INSERT ... ON DUPLICATE KEY [1] doesn't work for me...
There's an index:
CREATE INDEX ON table (col3, col4, col5)
It is non-unique as there are legacy data that does not allow me to declare >> it unique.
That is the point where I would solve it.
1. add a new column `is_legacy_data`, i.e. TINYINT DEFAULT 0 that will be 0 for all new columns
2. for legacy columns with (col3, col4, col5) being not unique, set the new column to a value that makes (col3, col4, col5, `is_legacy_data`) unique
3. add the unique index on (col3, col4, col5, `is_legacy_data`)
4. be happy with INSERT ... ON DUPLICATE KEY ...
I would suggest you use
LOCK TABLES table WRITE;
<do what you did before>
UNLOCK TABLES;
LOCK TABLES table WRITE;
UNLOCK TABLES;
https://www.mysqltutorial.org/mysql-table-locking/ https://dev.mysql.com/doc/refman/5.7/en/lock-tables.html
Sun, 2 Aug 2020 20:15:03 +0200, /J.O. Aho/:
I would suggest you use
LOCK TABLES table WRITE;
<do what you did before>
UNLOCK TABLES;
LOCK TABLES table WRITE;
UNLOCK TABLES;
https://www.mysqltutorial.org/mysql-table-locking/
https://dev.mysql.com/doc/refman/5.7/en/lock-tables.html
Wouldn't this basically block concurrent inserts/updates? I don't want
to block all on the same table trying to insert/update different sets of data.
Sun, 2 Aug 2020 20:15:03 +0200, /J.O. Aho/:
I would suggest you use
LOCK TABLES table WRITE;
<do what you did before>
UNLOCK TABLES;
LOCK TABLES table WRITE;
UNLOCK TABLES;
https://www.mysqltutorial.org/mysql-table-locking/
https://dev.mysql.com/doc/refman/5.7/en/lock-tables.html
Wouldn't this basically block concurrent inserts/updates? I don't want
to block all on the same table trying to insert/update different sets of data.
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 293 |
Nodes: | 16 (2 / 14) |
Uptime: | 236:47:33 |
Calls: | 6,624 |
Files: | 12,172 |
Messages: | 5,319,838 |