Writes are blocked when parallel transactions are running at PXC 8.0.27

Hi there ,
I have a 3 node cluster - PXC 8.0.27.
Transaction 1 :Iam inserting 1 CRORE rows on table t1 at node1.
Transaction 2 :Inserting 500 rows on table t2 at same node1.

I see the 500 ROWS are not inserted on table t2 at node1(blocked) until the 1 crore rows are committed at all the nodes.
1.Is this behavior expected …?
2.Is this happening due to huge writes blocking the other transactions…?
3.Is there any alternative to execute the transactions parallelly without blocking…?
4.Is there any possibility of deadlocks since other transactions were blocked…?


Note : Both the transactions are executed parallelly.
1 Like

I had to look up the word crore :smiley: it means 10 Million.

Are you inserting the 1 crore rows as part of a single transaction? (ie: BEGIN; INSERT … 1 crore rows; COMMIT) Or are you running 1 crore individual INSERT statements? Can you please share more details of how you are running this simulation?

I would expect txn2 (500 rows) to finish before tx1 (10 million rows), thus replicating to other nodes before tx1 finishes.

3: Yes. You need to read up on the new streaming replication as part of PXC 8
4: Not unless t1 and t2 share a Foreign Key

1 Like

@matthewb
yes iam inserting 1crore(10 million) transactions as part of single trasaction i.e
BEGIN …

1 CRORE TRANSACTIONS …

END.

1 Like

Like this?

txn1:  BEGIN;
INSERT INTO table1 (id, name) VALUES (1, 'John');
INSERT INTO table1 (id, name) VALUES (2, 'John');
....
INSERT INTO table1 (id, name) VALUES (10000000, 'John');
COMMIT;

txn2:  BEGIN;
INSERT INTO table2 (id, name) VALUES (1, 'Bob');
INSERT INTO table2 (id, name) VALUES (2, 'Bob');
....
INSERT INTO table2 (id, name) VALUES (500, 'Bob');
COMMIT;

You have 2 different sessions opened to MySQL? Open a 3rd session and ‘SHOW PROCESSLIST’ while both tx1 and tx2 are running, can you paste that output here?

1 Like