Skip to main content
Expanded on the root of the problem
Source Link

I failed to understand the extent of the transactions.

Before I do the INSERT, I do a write-locking select on the external index value in both transactions. I would have expected T2 to block T1 at that point, but that is not the case, both transactions successfully acquire a lock on the gap. Neither transaction can promote its lock to insert intention; T1 will block when it tries, then T2 will deadlock when it tries, thereby letting T1 succeed.

The behavior I found, and continue to find surprising, is that write locks on index gap values aren't exclusive. While that's certainly a correct way to implement serializability, it carries an attendant risk of deadlock that you would forego if write locks on gap values were exclusive as they are for record values. I wonder if I continue to miss something fundamental.

I failed to understand the extent of the transactions.

Before I do the INSERT, I do a write-locking select on the external index value in both transactions. I would have expected T2 to block T1 at that point, but that is not the case, both transactions successfully acquire a lock on the gap. Neither transaction can promote its lock to insert intention; T1 will block when it tries, then T2 will deadlock when it tries, thereby letting T1 succeed.

I failed to understand the extent of the transactions.

Before I do the INSERT, I do a write-locking select on the external index value in both transactions. I would have expected T2 to block T1 at that point, but that is not the case, both transactions successfully acquire a lock on the gap. Neither transaction can promote its lock to insert intention; T1 will block when it tries, then T2 will deadlock when it tries, thereby letting T1 succeed.

The behavior I found, and continue to find surprising, is that write locks on index gap values aren't exclusive. While that's certainly a correct way to implement serializability, it carries an attendant risk of deadlock that you would forego if write locks on gap values were exclusive as they are for record values. I wonder if I continue to miss something fundamental.

Source Link

I failed to understand the extent of the transactions.

Before I do the INSERT, I do a write-locking select on the external index value in both transactions. I would have expected T2 to block T1 at that point, but that is not the case, both transactions successfully acquire a lock on the gap. Neither transaction can promote its lock to insert intention; T1 will block when it tries, then T2 will deadlock when it tries, thereby letting T1 succeed.