GATE IT 2005 | Question: 67

Question

A company maintains records of sales made by its salespersons and pays them commission based on each individual's total sales made in a year. This data is maintained in a table with following schema:

salesinfo = (salespersonid, totalsales, commission)

In a certain year, due to better business results, the company decides to further reward its salespersons by enhancing the commission paid to them as per the following formula:

If commission $\leq 50000,$ enhance it by $2\%$
If $50000 <$ commission $\leq 100000,$ enhance it by $4\%$
If commission $> 100000,$ enhance it by $6\%$

The IT staff has written three different SQL scripts to calculate enhancement for each slab, each of these scripts is to run as a separate transaction as follows:

T1	Update salesinfo Set commission = commission * 1.02 Where commission < = 50000;
T2	Update salesinfo Set commission = commission * 1.04 Where commission > 50000 and commission is < = 100000;
T3	Update salesinfo Set commission = commission * 1.06 Where commission > 100000;

 

Which of the following options of running these transactions will update the commission of all salespersons correctly

• A. Execute T1 followed by T2 followed by T3
• B. Execute T2, followed by T3; T1 running concurrently throughout
• C. Execute T3 followed by T2; T1 running concurrently throughout
• D. Execute T3 followed by T2 followed by T1

Comments

Nice solution👍🏻

---

It's really great question as it's not too hard but still tricky and checks your SQL basics and problem solving skills

Answer 1

Correct Answer $: D$
 

$T3$ followed by $T2$ followed by $T1$ will be correct execution sequence:

other cases some people will get two times increment

eg. if we have $T1$ followed by $T2$

if initial commision is $49500$

then he is belonging to $< 50000$

hence, $49500*1.02 = 50490$

now, he is eligible in second category

then, $50490$*$1.04 = 52509.6$

so, he wil get increment two times. but he is eligible for only one slab of commision.

Comments

brilliant

---

good ans

---

Why option C is not correct?

---

@Beyonder , In option C , suppose T₃   executed first and then T₂ and T₁    are executing concurrently and if T1 execute first and after this T2 , then the same issue will be occur as told by Sankaranarayanan sir..

---

I think concurrent execution will always create trouble and executing transactions serially is much safer!!!

---

wow mazaa aa gya

---

Nice answer

---

if the query is executed concurrently it could be possible that person with sale 49500 gets updated first and then it would again get updated by t2 query as in concurrent execution parallel execution is there

---

Nice

Answer 2

best answer

Answer 3

Three transactions apply slab-based commission multipliers:

• $T_1$: $(\times 1.02)$ if $(\text{commission} \le 50{,}000)$

• $T_2$: $(\times 1.04)$ if $(50{,}000 < \text{commission} \le 100{,}000)$

• $T_3$: $(\times 1.06)$ if $(\text{commission} > 100{,}000)$

The enhancement must be applied exactly once, based on the original commission. Since SQL evaluates WHERE clauses on the current state, execution order and concurrency determine correctness.Example salesperson with original commission = ₹50,000 (should receive only 2%).

---

Option A: $T_1 \rightarrow T_2 \rightarrow T_3$

$$
\begin{array}{|c|c|c|c|}
\hline
\text{Step} & \text{Txn} & \text{Condition} & \text{Commission} \\
\hline
0 & \text{Initial} & - & 50000 \\
\hline
1 & T_1 & 50000 \leq 50000 & 50000 \times 1.02 = 51000 \\
\hline
2 & T_2 & 51000 \in (50000,100000] & 51000 \times 1.04 = 53040 \\
\hline
3 & T_3 & 53040 > 100000 & \text{No change} \\
\hline
\end{array}
$$
 

Fails: Enhanced twice (2% + 4%). Incorrect.

---

Option B: $T_2 \rightarrow T_3$; $T_1$ concurrent

Concurrency permits interleavings. Even if $T_2$ and $T_3$ finish first, $T_1$ running concurrently may:

• Read stale or intermediate values,

• Or interfere with rows updated by $T_2$ or $T_3$.

Because concurrent execution cannot guarantee that all rows are evaluated against the original state, correctness is not ensured.

Fails: Non-serial execution → no correctness guarantee.

---

Option C: $T_3 \rightarrow T_2$; $T_1$ concurrent

$$
\begin{array}{|c|c|c|c|}
\hline
\text{Step} & \text{Txn} & \text{Condition} & \text{Commission} \\
\hline
0 & \text{Initial} & - & 50000 \\
\hline
1 & T_3 & 50000 > 100000 & \text{No change} \\
\hline
2 & T_2 & 50000 > 50000 & \text{No change} \\
\hline
3 & T_1\ (\text{concurrent}) & 50000 \leq 50000 & 51000 \\
\hline
\end{array}
$$

This schedule yields the correct result for this instance. However, since $T_1$ runs concurrently, other interleavings could produce anomalies. The option does not enforce serializability.

Fails: Concurrency → possible inconsistent reads or lost updates.

---

Option D: $T_3 \rightarrow T_2 \rightarrow T_1$ (Serial)

$$
\begin{array}{|c|c|c|c|}
\hline
\text{Step} & \text{Txn} & \text{Condition} & \text{Commission} \\
\hline
0 & \text{Initial} & - & 50000 \\
\hline
1 & T_3 & 50000 > 100000 & \text{No change} \\
\hline
2 & T_2 & 50000 > 50000 & \text{No change} \\
\hline
3 & T_1 & 50000 \leq 50000 & 50000 \times 1.02 = 51000 \\
\hline
\end{array}
$$
 

Correct:

• Transactions run serially in descending slab order.

• No updated value qualifies for any subsequent transaction.

• Each row is updated exactly once, based on its original commission.

---

 

$$
\color{skyblue} \boxed{\text{D. Execute } T_3 \text{ followed by } T_2 \text{ followed by } T_1}
$$