Transactional Message Handling via Database Row Locking
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Modern database systems face increased computational burden when handling transient data, such as messages and instructions, which can lead to inefficiencies and the need for complex mechanisms to ensure transactional atomicity, especially in on-demand service environments.
Innovation Solution
The system enqueues messages specifying transactions, inserts rows into a database with a pending status, and performs final processing based on lock acquisition, including rollback, commit, requeue, or cleanup operations, while autocommitting rows to offload transient data processing and reduce database load.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a database system is used to store transient data such as messages and instructions, then transactional atomicity is ensured, but the computational burden on the database system increases
Solution Approach 1:
The patent segments data into two distinct categories: persistent data stored in the database and transient data (messages) handled by the messaging system. This segmentation allows the database to focus only on persistent data, reducing its computational burden while maintaining transactional atomicity for persistent operations. The messaging system handles transient data independently without requiring database involvement for each message operation.
Solution Approach 2:
The patent extracts transient data handling from the database system and places it in a dedicated messaging system. By taking out the message storage and retrieval functions from the database, the database is relieved of the computational overhead associated with managing transient data, including enqueuing, dequeuing, and deleting message records.
2Use of energy by moving object
If a messaging system does not utilize a database to ensure transactional atomicity, then computational burden on the database is reduced, but the messaging system lacks transactional atomicity or must implement complex mechanisms
Solution Approach 1:
The patent introduces an intermediary mechanism where the messaging system uses a separate message store (not the transactional database) to guarantee message delivery. This intermediary structure allows the messaging system to achieve transactional atomicity through its own dedicated storage mechanism, eliminating the need for complex distributed transaction protocols while maintaining reliability.
3Loss of information
If the database stores transient data for messaging operations, then message tracking and status monitoring are enabled, but the database load increases with the number of messages and transactions
Solution Approach 1:
The patent segments the storage responsibility by creating a dedicated message store for transient message data, separate from the persistent database. This allows comprehensive message tracking and status monitoring to occur in the message store without increasing the persistent database load. The database only contains essential persistent records, while the message store handles the voluminous transient messaging data.
Data Source
AI summary
In accordance with embodiments disclosed herein, there are provided methods, systems, and apparatuses including, for supporting transactional message handling in an on-demand service environment including, for example: enqueuing a message specifying a transaction to be processed via a host organization; inserting a row into a database of the host organization associating the message with a status of pending, wherein the row is autocommitted to the database upon insertion; updating the status for the row to ready if a commit operation for the transaction is initiated; requesting a lock on the row; and performing final processing for the transaction based on the status for the message and based further on whether the lock is obtained for the row. Final processing may include, for example, a transaction roll back, a transaction commit, a transaction requeue, a termination of transaction processing, or an orphaned transaction clean up.