Use case involving transactions that span multiple resources

  • 1
  • Question
  • Updated 6 years ago
I try to manage the receipt of financial messages that contain infos such as the client name. Infos that are not relevant to the transaction matter are not showed.

The database tables are something like that:
CLIENT(ID, CLIENT_NAME)
MESSAGE(ID, CLIENT_NAME, PAYLOAD)
HISTORY(ID, FK_MESSAGE_ID, TIMESTAMP, STATE)
FILE(ID, FK_MESSAGE_ID, FILE_NAME, FILE_PATH)

The use case definition is the following:

a1) start transaction t1
a2) read message m from queue QUEUE.IN
a3) read property "client" from message m
a4) write an entry to table MESSAGE, with CLIENT_NAME=m.client and PAYLOAD=m.payload,
a5) write an entry to table HISTORY with FK_MESSAGE_ID=id of the newly created entry in MESSAGE,TIMESTAMP the timestamp of the operation and STATE="RECEIVED"
a6) commit transaction t1

if m.client exists in table CLIENT then

b1) start transaction t2
b2) create a file with an arbitrary name in directory "/correct" that contains the message payload
b3) write a new entry in table FILE that points to the corresponding record in table MESSAGE via FK_MESSAGE_ID with FILE_NAME and FILE_PATH as in point b2)
b4) write an entry in table HISTORY with the timestamp of the operation and STATE="CENSUS_OK"
b5) commit transaction t2

else

c1) start transaction t2
c2) create a file with an arbitrary name in directory "/error" that contains the message payload
c3) write a new entry in table FILE that points to the corresponding record in table MESSAGE via FK_MESSAGE_ID with FILE_NAME and FILE_PATH as in point c2)
c4) write an entry in table HISTORY with the timestamp of the operation and STATE="CENSUS_KO"
c5) commit transaction t2

in case of error in any of the two transactions everything must be rolled back to previous state, in particular:
t1: put the message on the same queue for a retrial, and after a number
of unsuccessful retrials, put the message on a dead letter queue QUEUE.ERROR
t2: the attempt of file creation must obviously cancelled

It goes without saying that all databases updates must be rolled back
Photo of ebozzali

ebozzali

  • 2 Posts
  • 0 Reply Likes
  • unsure

Posted 6 years ago

  • 1
Photo of Asankha Perera

Asankha Perera, Founder and CTO

  • 384 Posts
  • 23 Reply Likes
Hi Ermanno

A complete POC and a detailed writeup for this is presented in this article "Transactions spanning multiple resources - an Example"

cheers
asankha