You are developing an order processing system supported by an RDBMS and wrote a piece of SQL code as a transaction to update the customer’s lifetime value as follows:
1> UPDATE Customers SET LifeTimeValue = LifeTimeValue + 99 WHERE CustomerId = 1
2> UPDATE Invoices SET TotalAmount = 99 WHERE InvoiceId = 1
However, your colleague wrote the same code in the reverse sequence. If a customer may have many invoices, which of the following will occur most likely? (Source: Wentz QOTD)
C. Race condition
D. Loss of referential integrity
Kindly be reminded that the suggested answer is for your reference only. It doesn’t matter whether you have the right or wrong answer. What really matters is your reasoning process and justifications.
My suggested answer is C. Race condition.
This question describes a common race condition, deadlock in RDBMS. Please refer to this post, Deadlock in SQL Server for detail.
In a relational database, a one-to-many relationship exists between two tables, the Master table and the Detail table, when one row in the Master table may be linked with many rows in the Detail table, but one row in Detail table is linked to only one row in Master table.
In this question, Customers is the Master table while Invoices is the Detail table.
Loss of referential integrity will lead to an orphaned record, a record in the Detail table whose foreign key value references a non-existent primary key value in the Master table.
The SQL transaction is updating Customers.LifeTimeValue and Invoices.TotalAmount. It is not updating the Foreign Key or Primary Key, or inserting new records of customer or invoice, so it won’t lead to loss of referential integrity. Moreover, the SQL operations are protected by a transaction, so it won’t lead to loss of referential integrity.
- SQL Server Deadlocks by Example
- Deadlock in SQL Server
- Secure Coding in C and C++ (Race conditions)
- One-to-many (data model)
- What is an Orphaned Record?
A BLUEPRINT FOR YOUR SUCCESS IN CISSP
My new book, The Effective CISSP: Security and Risk Management, helps CISSP aspirants build a solid conceptual security model. It is not only a tutorial for information security but also a study guide for the CISSP exam and informative reference for security professionals.
- It is available on Amazon.
- Readers from countries or regions not supported by Amazon can get your copy from the author’s web site.