Skip to content

JA6. TSQL

Statement

  • Review your week’s activities and your observations.
  • Discuss one example of a transaction that you engaged in within the past month and what database the data was stored in.

Question 1

  • This was the 6th week of the course, it was all about Transact SQL Programming.
  • The readings included workflows, TP monitors, Main-Memory databases, real-time and long-duration transactions, and more.
  • The discussion assignment asked us to give an actual example of a transaction workflow of a student submitting an assignment to their professor. I used a tool called draw.io to draw the workflow and gave a detailed explanation of it.

Question 2

  • At my place of work, we use a nonrelational database called DynamoDB of Amazon. DynamoDB is known for its performance by giving a lot of the overhead and regulatory functions that a normal RDMS would do to the user.
  • Thus there are no transactions in DynamoDB; well, they support transactions but did not implement them in our code.
  • So we store all our data in DynamoDB, and we use another Amazon service called AWS Cognito to handle user authentication and authorization.
  • And there was this function to change user email in our code; the function represent a transactional workflow of 3 steps:
    1. Change the email in the user table (processing entity: DynamoDB)
    2. Change the email in the user’s profile table (processing entity: Cognito)
    3. Finally, follow every place that the email is used and change it, including open sessions, open tickets, pending communications, ..etc (processing entity: Application Server which initiates requests to multiple vendors, including some DynamoDB tables).
  • The workflow is strictly sequential in the first two steps, so the second step should not be triggered unless the first step is successful.
  • The third step is a bit tricky, it is not sequential, it is parallel, and it is not a single transaction, it is a collection of transactions.
  • The third step must not be triggered unless the first two steps are successful; but once it is triggered, its individual subtasks can go in parallel and can fail independently (some of them can fail and some can succeed).
  • I have created compensation transactions for the first two tasks, that is, if the first two tasks fail, the compensation transactions will be triggered to reverse the changes made by the first two tasks. If only the first task fails, the compensation transaction will be triggered to reverse the changes made by the first task.
  • There is no way to track and compensate for the third step because it is very complicated and the system is not designed to handle it.
  • Usually, if something fails during the third step, the user will need to contact the support team to fix it manually.

References

  • Silberschatz, A., Korth, H.F., & Sudarshan, S. (2001). Database System Concepts (4th ed.). New York, NY: McGraw-Hill. Available at Database System Concepts 4th Edition By Silberschatz-Korth-Sudarshan.pdf Chapeter 24: Transaction Processing.