Using ad-hoc stored procedures for online transaction processing
An example method of using ad-hoc stored procedures for OLTP operations includes: identifying, by a processing device, one or more values for updating a dataset stored in a database; creating an ad-hoc stored procedure comprising a sequence of commands specifying atomically-executable database update operations with respect to the dataset, wherein the atomically-executable database update operations utilize the one or more values that are encoded into the sequence of commands by respective inline constant values; executing the ad-hoc stored procedure, wherein the executing comprises acquiring a temporary lock of at least part of the dataset; and dropping the ad-hoc stored procedure.
1 . A method, comprising:
generating, by a processing device, based on one or more templates, a create procedure command for updating a dataset stored in a database, wherein each template of the one or more templates comprises a text of a database update command, the text comprising one or more placeholders for respective data values;
creating, by executing the create procedure command, an ad-hoc stored procedure comprising a sequence of database access commands specifying atomically-executable database update operations with respect to the dataset, wherein the atomically-executable database update operations utilize one or more values that are encoded into the sequence of database access commands by respective inline constant values substituting the one or more placeholders, wherein a procedure name of the ad-hoc stored procedure comprises a digest of the one or more values that are encoded into the sequence of commands by respective inline constant values; and
executing the ad-hoc stored procedure, wherein the executing comprises:
acquiring a temporary lock of at least part of the dataset,
transforming a loop within the ad-hoc stored procedure into a linear sequence of operations,
performing the atomically-executable database update operations with respect to the dataset, and
releasing the temporary lock, with no intervening transfers of execution control to a client between acquiring and releasing the temporary lock.
2 . The method of claim 1 , wherein the ad-hoc stored procedure accepts no parameters.
3 . The method of claim 1 , wherein the dataset comprises one or more records of a table of the database.
4 . The method of claim 1 , wherein the atomically-executable database update operations update, using the one or more values, one or more records of one or more tables of the database.
5 . The method of claim 1 , wherein the ad-hoc stored procedure comprises: a transaction start command, one or more database update commands referencing the one or more values, and a transaction commit command.
6 . The method of claim 1 , further comprising:
responsive to detecting a failure of an operation of the atomically-executable database update operations, rolling back database updates that have been performed by preceding operations of the atomically-executable database update operations.
7 . The method of claim 1 , wherein the procedure name of the ad-hoc stored procedure further comprises at least one of: a predefined prefix including one or more alphanumeric characters or a predefined suffix including one or more alphanumeric characters.
8 . The method of claim 1 , further comprising:
keeping the ad-hoc stored procedure in the database; and
re-using the ad-hoc stored procedure.
9 . A system, comprising:
a memory; and
a processing device, coupled to the memory, the processing device to:
generate, based on one or more templates, a create procedure command for updating a dataset stored in a database, wherein each template of the one or more templates comprises a text of a database update command, the text comprising one or more placeholders for respective data values;
create, by executing the create procedure command, an ad-hoc stored procedure comprising a sequence of database access commands specifying atomically-executable database update operations with respect to the dataset, wherein the atomically-executable database update operations utilize one or more values that are encoded into the sequence of database access commands by respective inline constant values substituting the one or more placeholders, wherein a procedure name of the ad-hoc stored procedure comprises a digest of the one or more values that are encoded into the sequence of commands by respective inline constant values; and
execute the ad-hoc stored procedure, wherein the executing comprises:
acquiring a temporary lock of at least part of the dataset,
transforming a loop within the ad-hoc stored procedure into a linear sequence of operations,
performing the atomically-executable database update operations with respect to the dataset, and
releasing the temporary lock, with no intervening transfers of execution control to a client between acquiring and releasing the temporary lock.
10 . The system of claim 9 , wherein the ad-hoc stored procedure accepts no parameters.
11 . The system of claim 9 , wherein the atomically-executable database update operations update, using the one or more values, one or more records of one or more tables of the database.
12 . The system of claim 9 , wherein the ad-hoc stored procedure comprises: a transaction start command, one or more database update commands referencing the one or more values, and a transaction commit command.
13 . The system of claim 9 , wherein the processing device is further to: responsive to detecting a failure of an operation of the atomically-executable database update operations, roll back database updates that have been performed by preceding operations of the atomically-executable database update operations.
14 . A non-transitory computer-readable storage medium comprising executable instructions that, when executed by a computer system, cause the computer system to:
generate, based on one or more templates, a create procedure command for updating a dataset stored in a database, wherein each template of the one or more templates comprises a text of a database update command, the text comprising one or more placeholders for respective data values;
create, by executing the create procedure command, an ad-hoc stored procedure comprising a sequence of database access commands specifying atomically-executable database update operations with respect to the dataset, wherein the atomically-executable database update operations utilize one or more values that are encoded into the sequence of database access commands by respective inline constant values substituting the one or more placeholders, wherein a procedure name of the ad-hoc stored procedure comprises a digest of the one or more values that are encoded into the sequence of commands by respective inline constant values; and
execute the ad-hoc stored procedure, wherein the executing comprises:
acquiring a temporary lock of at least part of the dataset,
transforming a loop within the ad-hoc stored procedure into a linear sequence of operations,
performing the atomically-executable database update operations with respect to the dataset,
and releasing the temporary lock, with no intervening transfers of execution control to a client between acquiring and releasing the temporary lock.
15 . The non-transitory computer-readable storage medium of claim 14 , wherein the atomically-executable database update operations update, using the one or more values, one or more records of one or more tables of the database.
16 . The non-transitory computer-readable storage medium of claim 14 , wherein the ad-hoc stored procedure comprises: a transaction start command, one or more database update commands referencing the one or more values, and a transaction commit command.
17 . The non-transitory computer-readable storage medium of claim 14 , further comprising executable instructions that, when executed by the computer system, cause the computer system to:
responsive to detecting a failure of an operation of the atomically-executable database update operations, rolling back database updates that have been performed by preceding operations of the atomically-executable database update operations.
18 . The non-transitory computer-readable storage medium of claim 14 , wherein the ad-hoc stored procedure accepts no parameters.
19 . The non-transitory computer-readable storage medium of claim 14 , wherein the dataset comprises one or more records of a table of the database.