Explain the difference between optimistic and pessimistic locking in a relational database like MySQL.
