5.6 Locking Performance
Deadlocks are rare
- up to 1% - 2% of transactions deadlock
The one exception to this is lock conversions
- r-lock a record and later upgrade to w-lock
- e.g., Ti = read(x) … write(x)
- if two txns do this concurrently, they’ll deadlock (both get an r-lock on x before either gets a w-lock)
- To avoid lock conversion deadlocks, get a w-lock first and down-grade to an r-lock if you don’t need to write.
- Use SQL Update statement or explicit program hints