What happens to the Transaction waiting for the lock?

user_10982958 0
Viewed 357

I was asked in an interview, "In a MySQL database, what happens if a Transaction(2) tries to acquire a Shared/Exclusive lock, but another Transaction{1) already has an Exclusive lock on the same Resource?"

My answer was simple, The Transaction(2) waits until the commit or rollback of Transaction(1). Then comes the followup questions. If so,

  1. Where will the second transaction be waiting. (like Blocking queue kind of implementations?)
  2. How the 2nd Transaction will be notified upon the commit or Rollback of Transaction(1)?
  3. If there are more than one transactions waiting for the same resource which was locked by Transaction(1), how the lock resolution be done?
locking transactions mysql
1 Answers

This sort of question is not about the SQL language, it's about specific implementations. The behavior might be different in for example MySQL vs. PostgreSQL.

I can answer for the MySQL case.

If you execute a query in transaction 2 your client calls an API function and waits for it to return. It won't return while it is waiting for the query to execute. That's typical of function calls.

If your transaction needs locks that are already held by transaction 1, your API call blocks. That is, the query does not begin executing while it is waiting for locks.

MySQL's InnoDB storage engine has a default lock wait timeout of 50 seconds (this is configurable). If the lock cannot be acquired before the timeout, your query fails and the API call returns an error.

The transaction is NOT rolled back. You can re-try a given query in the same transaction scope.

MySQL also supports "metadata locks" which are table locks, unlike the row locks of InnoDB. Metadata locks are for statements like DROP TABLE or ALTER TABLE, which waits until there are no transactions reading or writing the table. Likewise, queries cannot acquire a metadata lock if a DDL statement is holding an exclusive metadata lock. Metadata locks also have a timeout, but its default value is much longer: it is 31536000 seconds (1 year).

Bill_Karwin_208600
Related