Hi there ,
I have a 3 node cluster -
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.
I had to look up the word
crore 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
yes iam inserting 1crore(10 million) transactions as part of single trasaction i.e
1 CRORE TRANSACTIONS …
INSERT INTO table1 (id, name) VALUES (1, 'John');
INSERT INTO table1 (id, name) VALUES (2, 'John');
INSERT INTO table1 (id, name) VALUES (10000000, 'John');
INSERT INTO table2 (id, name) VALUES (1, 'Bob');
INSERT INTO table2 (id, name) VALUES (2, 'Bob');
INSERT INTO table2 (id, name) VALUES (500, 'Bob');
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?