# US 2023/0054501 A1 · Using ad-hoc stored procedures for online transaction processing

> Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt

- Number: US 2023/0054501 A1
- Document ID: US20230054501A1
- Status: Published application
- Kind: A1
- Application number: 17/478,233
- Filed: 2021-09-17
- Published: 2023-02-23
- Earliest claimed: 2021-08-19
- Assignees: Twilio Inc.
- Applicants: Twilio Inc.
- Inventors: Mayank Lahiri; Jayakrishnan Balakrishnan Nair; Padmaja U. Mantravadi
- Web page: https://patentgrep.com/patent/US20230054501A1

## CPC

- G06F 16/2343: {Locking methods, e.g. distributed locking or locking implementation details}
- G06F 16/2358: {Change logging, detection, and notification (replication G06F16/27)}
- G06F 16/2365: {Ensuring data consistency and integrity}
- G06F 16/2379: {Updates performed during online database operations; commit processing}
- G06F 16/2443: {Stored procedures}
- G06F 16/252: {between a Database Management System and a front-end application}
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)

## Abstract

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.

## Claims

### Claim 1

A method, comprising:
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.

### Claim 2 (depends on 1)

The method of claim 1, wherein executing the ad-hoc stored procedure further comprises releasing the temporary lock, and wherein update operations between acquiring and releasing the lock are performed without an intervening transfer of execution control to a client of the database.

### Claim 3 (depends on 1)

The method of claim 1, wherein the ad-hoc stored procedure accepts no parameters.

### Claim 4 (depends on 1)

The method of claim 1, wherein creating the ad-hoc stored procedure further comprises:
computing a digest of a text of the ad-hoc stored procedure; and
generating, using the digest, an identifier of the ad-hoc stored procedure.

### Claim 5 (depends on 1)

The method of claim 1, wherein creating the ad-hoc stored procedure further comprises:
computing a digest of the one or more values; and
generating, using the digest, an identifier of the ad-hoc stored procedure.

### Claim 6 (depends on 1)

The method of claim 1, wherein the dataset comprises one or more records of a table of the database.

### Claim 7 (depends on 1)

The method of claim 1, wherein the one or more atomically-executable update operations update, using the one or more values, one or more records of one or more tables of the database.

### Claim 8 (depends on 1)

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.

### Claim 9 (depends on 1)

The method of claim 1, further comprising:
responsive to detecting a failure of an operation of the one or more atomically-executable update operations, rolling back database updates that have been performed by preceding operations of the one or more atomically-executable update operations.

### Claim 10

A system, comprising:
a memory; and
a processing device, coupled to the memory, the processing device to:
identify one or more values for updating a dataset stored in a database;
create 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;
execute the ad-hoc stored procedure, wherein the executing comprises acquiring a temporary lock of at least part of the dataset; and
drop the ad-hoc stored procedure.

### Claim 11 (depends on 10)

The system of claim 10, wherein executing the ad-hoc stored procedure further comprises releasing the temporary lock, and wherein update operations between acquiring and releasing the lock are performed without an intervening transfer of execution control to a client of the database.

### Claim 12 (depends on 10)

The system of claim 10, wherein the ad-hoc stored procedure accepts no parameters.

### Claim 13 (depends on 10)

The system of claim 10, wherein the one or more atomically-executable update operations update, using the one or more values, one or more records of one or more tables of the database.

### Claim 14 (depends on 10)

The system of claim 10, 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.

### Claim 15 (depends on 10)

The system of claim 10, wherein the processing device is further to:
responsive to detecting a failure of an operation of the one or more atomically-executable update operations, roll back database updates that have been performed by preceding operations of the one or more atomically-executable update operations.

### Claim 16

A non-transitory computer-readable storage medium comprising executable instructions that, when executed by a computer system, cause the computer system to:
identify one or more values for updating a dataset stored in a database;
create 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;
execute the ad-hoc stored procedure, wherein the executing comprises acquiring a temporary lock of at least part of the dataset; and
drop the ad-hoc stored procedure.

### Claim 17 (depends on 16)

The non-transitory computer-readable storage medium of claim 16, wherein executing the ad-hoc stored procedure further comprises releasing the temporary lock, and wherein update operations between acquiring and releasing the lock are performed without an intervening transfer of execution control to a client of the database.

### Claim 18 (depends on 16)

The non-transitory computer-readable storage medium of claim 16, wherein the one or more atomically-executable update operations update, using the one or more values, one or more records of one or more tables of the database.

### Claim 19 (depends on 16)

The non-transitory computer-readable storage medium of claim 16, 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.

### Claim 20 (depends on 16)

The non-transitory computer-readable storage medium of claim 16, 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 one or more atomically-executable update operations, rolling back database updates that have been performed by preceding operations of the one or more atomically-executable update operations.