Detection and prevention of writing conflicts within nested query statements

A computer program product for detection of writing conflicts in a database system, the writing conflict detection being for database control statements comprising a parent statement and a set of invoked nested statements, the relative definition of the parent statement and each of the nested statements creating a set of table access scopes, each of the table access scopes containing one or more of the parent statement and the nested statements, the program product comprising a computer usable medium tangibly embodying computer readable program code means comprising:

• code means for maintaining a set of statement table access information in association with each database control statement, the table access information for each of the said statements recording the identity of tables to be accessed by the execution of the statement and recording the access mode of the said table access;
• code means for generating, at execution time, a set of cumulative table access information associated with the parent statement, each element in the set of cumulative table access information recording information specifying a table, a table access scope and an access mode, the set of cumulative table access information being initialized with the table and access mode information from the statement table access information for the parent statement, and information specifying the table access scope containing the parent statement;
• code means for, prior to execution of each statement in the set of nested statements, comparing the table and the access mode information for a sequence of selected elements in the set of statement table access information for the nested statement with the table and access mode information for each element in the set of cumulative table access information, until either a writing conflict is determined for the nested statement by the comparison of a one of the selected elements or each element in the set of statement table access information for the nested statement has been compared with each element in the set of cumulative table access information for the parent statement;
• and
• code means for, when no writing conflict is determined for a specified nested statement, adding to the set of cumulative table access information:
• i. the information contained in each element in the set of statement table access information for the specified nested statement;
• and
• ii. information specifying the table access scope containing the specified nested statement.

A computer-implemented method for detection of writing conflicts in a relational database system for database control statements comprising a parent statement and a set of invoked nested statements, the method comprising the steps of:

• a. maintaining a set of statement table access information in association with each database control statement;
• b. generating, at execution time, a set of cumulative table access information associated with the parent statement comprising information from the statement table access information for the parent statement;
• and
• c. comparing, prior to execution of each statement in the set of nested statements, the set of statement table access information for the nested statement with the set of cumulative table access information to determine whether a writing conflict will occur on execution of the nested statement.

A computer-implemented method for detection of writing conflicts in a relational database system, the writing conflict detection being for database control statements comprising a parent statement and a set of invoked nested statements, the relative definition of the parent statement and each of the nested statements creating a set of table access scopes, each of the table access scopes containing one or more of the parent statement and the nested statements, the method comprising the steps of:

• a. maintaining a set of statement table access information in association with each database control statement, the table access information for each of the said statements recording the identity of tables to be accessed by the execution of the statement and recording the access mode of the said table access;
• b. generating, at execution time, a set of cumulative table access information associated with the parent statement, each element in the set of cumulative table access information recording information specifying a table, a table access scope and an access mode, the set of cumulative table access information being initialized with the table and access mode information from the statement table access information for the parent statement, and information specifying the table access scope containing the parent statement;
• and
• c. comparing, prior to execution of each statement in the set of nested statements, the table and the access mode information for selected elements in the set of statement table access information for the nested statement with the table and access mode information for each element in the set of cumulative table access information, until either a writing conflict is determined for the nested statement or each element in the set of statement table access information for the nested statement has been compared with each element in the set of cumulative table access information for the parent statement.

A computer-implemented method for carrying out writing conflict detection in a compiled SQL database system, for a parent SQL statement having a set of invoked nested SQL statements, the parent and nested SQL statements each being defined to be within one of a set of table access scopes, the method comprising the steps of:

• a. generating, at compile time, for each statement, a table access data structure associated with the statement, the table access data structure comprising an entry for tables accessed by the statement, each entry comprising a table identifier and an access mode identifier;
• b. generating, at execution time, a list of tables associated with the parent statement, each entry in the list of tables comprising a table identifier, a table access scope identifier and an access mode identifier, the list of tables being initialized with the information from the table access data structure entries of the parent statement;
• and
• c. before executing each statement in the set of nested statements,
• i. comparing the tables and access modes identified by the entries in the table access data structure for the nested statement with the entries in the list of tables, until a conflict is declared or until each entry in the table access data structure has been compared with the entries in the list of tables;
• and
• ii. when no conflict is declared, adding to the list of tables the information contained in each entry in the table access data structure for the nested statement and the table access scope defined to contain the nested statement.

A computer program product comprising a computer readable medium carrying program means for carrying out writing conflict detection in a compiled SQL database system, for a parent SQL statement having a set of invoked nested SQL statements, the parent and nested SQL statements each being defined to be within one of a set of table access scopes, on a computer system, the computer program product comprising:

• code means for generating, at compile time, for each statement, a table access data structure associated with the statement, the table access data structure comprising an entry for tables accessed by the statement, each entry comprising a table identifier and an access mode identifier;
• code means for generating, at execution time, a list of tables associated with the parent statement, each entry in the list of tables comprising a table identifier, a table access scope identifier and an access mode identifier, the list of tables being initialized with the information from the table access data structure entries of the parent statement;
• and
• code means for, before executing each statement in the set of nested statements,
• i. comparing the tables and access modes identified by the entries in the table access data structure for the nested statement with the entries in the list of tables, until a conflict is declared or until each entry in the table access data structure has been compared with the entries in the list of tables;
• and
• ii. when no conflict is declared, adding to the list of tables the information contained in each entry in the table access data structure for the nested statement and the table access scope defined to contain the nested statement.

A computer-implemented method for carrying out writing conflict detection in a compiled SQL database system, for a parent SQL statement having a set of invoked nested SQL statements, the parent and nested SQL statements each being defined to be within one of a set of table access scopes, the method comprising the steps of:

• a. generating for each statement, at the time of compilation, a table access data structure associated with the statement, the table access data structure comprising an entry corresponding to, and comprising the table identifier for, each permanent table and each declared global temporary table accessed by the statement, and the mode of access identifier for each entry reflecting the mode in which the table corresponding to the entry is accessed by the execution of the statement;.
• b. generating, at execution time, a list of tables associated with the parent statement, each entry in the list of tables comprising a table identifier, a table access scope identifier and an access mode identifier, the list of tables being initialized with the information from the table access data structure entries of the parent statement;
• and
• c. before executing each statement in the set of nested statements,
• i. comparing the tables and access modes identified by the entries in the table access data structure for the nested statement with the entries in the list of tables, until a conflict is declared or until each entry in the table access data structure has been compared with the entries in the list of tables, the step of comparing a selected entry in the table access data structure and the entries in the list of tables comprising
• a. looking up the table access data structure entry table identifier in the table identifiers in the entries in the list of tables, and when there is a match with a located list of tables entry,
• in the case when the table access scope of the nested statement matches the table access scope identifier of the located list of tables entry declaring there to be no conflict for the table access data structure entry,
• in the case when the table access scope of the nested statement does not match the table access scope identifier of the located list of tables entry, and in the case when table access scope entry access mode identifier and the located list of tables entry access mode identifier both correspond to the read value, declaring there to be no conflict for the table access data structure entry and
• otherwise declaring there to be a conflict for the table access data structure entry;
• and
• ii. when no conflict is declared, adding to the list of tables the information contained in each entry in the table access data structure for the nested statement and the table access scope defined to contain the nested statement.

A computer-implemented method for carrying out writing conflict detection in a compiled SQL database system, for a parent SQL statement having a set of invoked nested SQL statements, the parent and nested SQL statements each being defined to be within one of a set of table access scopes and each having an associated table access data structure, the table access data structure comprising an entry corresponding to, and comprising the table identifier for, each permanent table and each declared global temporary table accessed by the statement, and the mode of access identifier for each entry reflecting the mode in which the table corresponding to the entry is accessed by the execution of the statement, the method comprising the steps of:

• a. generating, at execution time, a list of tables associated with the parent statement, each entry in the list of tables comprising a table identifier, a table access scope identifier and an access mode identifier, the list of tables being initialized with the information from the table access data structure entries of the parent statement;
• and
• b. before executing each statement in the set of nested statements,
• i. comparing the tables and access modes identified by the entries in the table access data structure for the nested statement with the entries in the list of tables, until a conflict is determined or until each entry in the table access data structure has been compared with the entries in the list of tables, the step of comparing a selected entry in the table access data structure and the entries in the list of tables comprising
• looking up the table access data structure entry table identifier in the table identifiers in the entries in the list of tables, and when there is a match with a located list of table entry,
• in the case when the table access scope of the nested statement matches the table access scope identifier of the located list of tables entry declaring there to be no conflict for the table access data structure entry,
• in the case when the table access scope of the nested statement does not match the table access scope identifier of the located list of tables entry, and when table access scope entry access mode identifier and the located list of tables entry access mode identifier both correspond to the read value, declaring there to be no conflict for the table access data structure entry and
• otherwise declaring there to be a conflict for the table access data structure entry;
• and
• ii. when no conflict is determined, adding to the list of tables the information contained in each entry in the table access data structure for the nested statement and the table access scope defined to contain the nested statement.

A computer program product comprising a computer readable medium carrying program means for carrying out writing conflict detection in a compiled SQL database system, for a parent SQL statement having a set of invoked nested SQL statements, the parent and nested SQL statements each being defined to be within one of a set of table access scopes on a computer system, the computer program product comprising:

• code means for generating for each statement, at the time of compilation, a table access data structure associated with the statement, the table access data structure comprising an entry corresponding to, and comprising the table identifier for, each permanent table and each declared global temporary table accessed by the statement, and the mode of access identifier for each entry reflecting the mode in which the table corresponding to the entry is accessed by the execution of the statement;
• code means for generating, at execution time, a list of tables associated with the parent statement, each entry in the list of tables comprising a table identifier, a table access scope identifier and an access mode identifier, the list of tables being initialized with the information from the table access data structure entries of the parent statement;
• and
• code means for, before executing each statement in the set of nested statements,
• i. comparing the tables and access modes identified by the entries in the table access data structure for the nested statement with the entries in the list of tables, until a conflict is declared or until each entry in the table access data structure has been compared with the entries in the list of tables, the step of comparing a selected entry in the table access data structure and the entries in the list of tables comprising
• looking up the table access data structure entry table identifier in the table identifiers in the entries in the list of tables, and when there is a match with a located list of tables entry,
• in the case when the table access scope of the nested statement matches the table access scope identifier of the located list of tables entry declaring there to be no conflict for the table access data structure entry,
• in the case when the table access scope of the nested statement does not match the table access scope identifier of the located list of tables entry, and in the case when table access scope entry access mode identifier and the located list of tables entry access mode identifier both correspond to the read value, declaring there to be no conflict for the table access data structure entry and
• otherwise declaring there to be a conflict for the table access data structure entry;
• and
• ii. when no conflict is declared, adding to the list of tables the information contained in each entry in the table access data structure for the nested statement and the table access scope defined to contain the nested statement.

A computer program product comprising a computer readable medium carrying program means for carrying out writing conflict detection in a compiled SQL database system, for a parent SQL statement having a set of invoked nested SQL statements, the parent and nested SQL statements each being defined to be within one of a set of table access scopes and each having an associated table access data structure, the table access data structure comprising an entry corresponding to, and comprising the table identifier for, each permanent table and each declared global temporary table accessed by the statement, and the mode of access identifier for each entry reflecting the mode in which the table corresponding to the entry is accessed by the execution of the statement on a computer system, the computer program product comprising:

• code means for generating, at execution time, a list of tables associated with the parent statement, each entry in the list of tables comprising a table identifier, a table access scope identifier and an access mode identifier, the list of tables being initialized with the information from the table access data structure entries of the parent statement;
• and
• code means for, before executing each statement in the set of nested statements,
• i. comparing the tables and access modes identified by the entries in the table access data structure for the nested statement with the entries in the list of tables, until a conflict is determined or until each entry in the table access data structure has been compared with the entries in the list of tables, the step of comparing a selected entry in the table access data structure and the entries in the list of tables comprising
• looking up the table access data structure entry table identifier in the table identifiers in the entries in the list of tables, and when there is a match with a located list of table entry,
• in the case when the table access scope of the nested statement matches the table access scope identifier of the located list of tables entry declaring there to be no conflict for the table access data structure entry,
• in the case when the table access scope of the nested statement does not match the table access scope identifier of the located list of tables entry, and when table access scope entry access mode identifier and the located list of tables entry access mode identifier both correspond to the read value, declaring there to be no conflict for the table access data structure entry and
• otherwise declaring there to be a conflict for the table access data structure entry;
• and
• ii. when no conflict is determined, adding to the list of tables the information contained in each entry in the table access data structure for the nested statement and the table access scope defined to contain the nested statement