[
https://issues.apache.org/jira/browse/IGNITE-23034?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Andrey Khitrin updated IGNITE-23034:
------------------------------------
Description:
{*}Steps to reproduce{*}:
1. Start AI3 cluster with *3 or more* nodes
2. Create a simple table with an index:
{code:sql}
CREATE TABLE indexed_kids(id INTEGER, name VARCHAR(100), primary key (id))
CREATE INDEX indexed_kids_index on indexed_kids using SORTED (name)
INSERT INTO indexed_kids (id, name) VALUES (1, 'Europe')
{code}
3. Run 2 parallel transactions that try to insert values with the same value
for the *indexed column* (not PK!). For example:
{code:sql}
-- First transaction
INSERT INTO indexed_kids (id, name) VALUES (9, 'Kolobok')
-- Second transaction
INSERT INTO indexed_kids (id, name) VALUES (12, 'Kolobok')
{code}
{*}Expected behavior{*}:
We expect both transactions to pass, in general.
{*}Actual behavior{*}:
Looks like it depends on 3 metrics: cluster size, amount of table partitions
and amount of table replicas. In some cases, one transaction may fail with
*SQLException* ("
Failed to acquire a lock due to a possible deadlock")
For example, in a cluster of 1 or 2 nodes, the second transaction always fail.
In a cluster of 3 nodes, it may pass on default zone settings, but fails again
when we increase amount of table partitions and/or replicas.
was:
*Steps to reproduce*:
1. Start AI3 cluster with *3 or more* nodes
2. Create a simple table with an index:
{code:sql}
CREATE TABLE indexed_kids(id INTEGER, name VARCHAR(100), primary key (id))
CREATE INDEX indexed_kids_index on indexed_kids using SORTED (name)
INSERT INTO indexed_kids (id, name) VALUES (1, 'Europe')
{code}
3. Run 2 parallel transactions that try to insert values with the same value
for the *indexed column* (not PK!). For example:
{code:sql}
-- First transaction
INSERT INTO indexed_kids (id, name) VALUES (9, 'Kolobok')
-- Second transaction
INSERT INTO indexed_kids (id, name) VALUES (12, 'Kolobok')
{code}
*Expected behavior*:
Only the first transaction must success, and the second one must fail.
*Actual behavior*:
When a cluster size is 3 or greater, both transactions success, inserting two
rows with the same value into an indexed column.
Important:
* For clusters with size of 1 or 2 nodes, behavior is correct (only 1
transaction succeeds)
* When we insert values with the same PK values, behavior is correct (only 1
transaction succeeds)
Priority: Major (was: Critical)
Summary: Issuses with concurrent inserts that depend on cluster size
and zone settings (was: Index restrictions are ignored on insert in a cluster
of 3+ nodes)
> Issuses with concurrent inserts that depend on cluster size and zone settings
> -----------------------------------------------------------------------------
>
> Key: IGNITE-23034
> URL: https://issues.apache.org/jira/browse/IGNITE-23034
> Project: Ignite
> Issue Type: Bug
> Components: sql
> Affects Versions: 3.0
> Reporter: Andrey Khitrin
> Priority: Major
> Labels: ignite-3, index, transaction
>
> {*}Steps to reproduce{*}:
> 1. Start AI3 cluster with *3 or more* nodes
> 2. Create a simple table with an index:
> {code:sql}
> CREATE TABLE indexed_kids(id INTEGER, name VARCHAR(100), primary key (id))
> CREATE INDEX indexed_kids_index on indexed_kids using SORTED (name)
> INSERT INTO indexed_kids (id, name) VALUES (1, 'Europe')
> {code}
> 3. Run 2 parallel transactions that try to insert values with the same value
> for the *indexed column* (not PK!). For example:
> {code:sql}
> -- First transaction
> INSERT INTO indexed_kids (id, name) VALUES (9, 'Kolobok')
> -- Second transaction
> INSERT INTO indexed_kids (id, name) VALUES (12, 'Kolobok')
> {code}
> {*}Expected behavior{*}:
> We expect both transactions to pass, in general.
> {*}Actual behavior{*}:
> Looks like it depends on 3 metrics: cluster size, amount of table partitions
> and amount of table replicas. In some cases, one transaction may fail with
> *SQLException* ("
> Failed to acquire a lock due to a possible deadlock")
> For example, in a cluster of 1 or 2 nodes, the second transaction always
> fail. In a cluster of 3 nodes, it may pass on default zone settings, but
> fails again when we increase amount of table partitions and/or replicas.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
