By default, grid transactions fail if the transactions attempt to write to a set of rows where the primary partitions for the rows are on different nodes.
However, you can enable write operations that target multiple nodes to run as individual transactions by temporarily setting the grid to distributed write mode by using the SET GRID WRITE DISTRIBUTED statement, see SET GRID WRITE.
In the following example, MYTABLE is a table that is partitioned across several nodes. The requirement is to delete all records in the table where the year is set to 2020 regardless of which node hosts the primary replication unit for the partition for the individual rows. The following high level steps demonstrate how the distributed write operation is executed:
1 The client application issues the following statements:
SET GRID WRITE DISTRIBUTED DELETE FROM MYTABLE WHERE YEAR = 2020;
2 The driver routes both statements to the current grid node.
3 The current node switches to distributed write mode and requests that the statement
DELETE FROM MYTABLE WHERE YEAR = 2020;
is executed in parallel by all grid nodes.
4 Each grid node executes the statement (but for only the rows for which it hosts the primary replication unit for the partition).
5 After successful execution, each node indicates to the requesting node that it is ready to commit.
6 When all nodes are ready, the requesting node commits each transaction in every node and returns the results to the driver.
7 When all write operations are complete, the client application issues the following statement:
SET GRID WRITE DEFAULT;
Note SELECT statements fail when the grid is in distributed write mode, so it is important to reset the grid to the default write mode when the transaction is complete.
Examples
The following DELETE and UPDATE operations (that are unlikely to succeed in default write mode) would succeed if the grid is set to distributed write mode:
SET GRID WRITE DISTRIBUTED DELETE FROM MYTABLE WHERE ID < 10000 COMMIT WORK; SET GRID WRITE DEFAULT;
SET GRID WRITE DISTRIBUTED UPDATE MYTABLE SET STATUS = 'New' WHERE ID > 10000 COMMIT WORK; SET GRID WRITE DEFAULT;