I have a MySQL table (call it 'my_table') with a composed primary key with 4 columns (call them 'a', 'b', 'c' and 'd').
At least one time I encountered a deadlock on parallel asynchronous EJB calls calling 'DELETE FROM my_table where a=? and b=?' with different values, so I started to look into how InnoDB table locking works.
I've found no clear documentation on how table locking works with composed keys. Is the whole table locked by the delete, despite the fact that there's no overlap among the actual rows being deleted?
Do I need to do a select to recover the values for c and d and delete batches using the whole primary key?
This is in the context of a complex application which works with 4 different databases. Only MySQL seems to have this issue.
InnoDB never locks the entire table for DML statements. (Unless the DML is hitting all rows.)
There are other locks for DDL statements, such as when ALTER TABLE is modifying/adding columns/indexes/etc. (Some of these have been greatly sped up in MySQL 8.0.)
There is nothing special about a composite key wrt locking.
There is a thing called a "gap lock". For various reasons, the "gap" between two values in the index will be locked. This prevents potential conflicts such as inserting the same new value that does not yet exist, and there is a uniqueness constraint.
Since the PRIMARY KEY is a unique key, you may have hit something like that.
If practical, do SHOW ENGINE INNODB STATUS; to see whether the lock is "gap" or not.
Another thing that can happen is that a lock can start out being weak, then escalate to "eXclusive". This can lead to a deadlock.
Do I need to do a select to recover the values for c and d and delete batches using the whole primary key?
I think you need to explain more precisely what you are doing. Provide the query. Provide SHOW CREATE TABLE.
InnoDB's lock handling is possibly unique to MySQL. It has some quirks. Sometimes it is a bit greedy about what it locks; to compensate, it is possibly faster than the competition.
In any case, check for deadlocks (and timeouts) and deal with them. The hope that these problems are rare enough that having to deal with them is not too much a performance burden.
DELETE FROM my_table where a=? and b=? means that potentially a large number of rows are being deleted. That means that the undo log and MVCC need to do a lot of work. Hence, I recommend trying not to delete (or update) more than 1K rows at a time.