mysql - To what extend is table design responsible for the occurrence of deadlocks? -

we error sqlstate[40001]: serialization failure: 1213 deadlock found when trying lock; try restarting transaction. websites show solution retrying try/catch, such mysql deadlock detection via php. question is; extend table design responsible occurrence of such deadlocks? in particular interested in following example.

table   - id (int, primary key)   -  (int, foreign key -- references b.a)   - b  (int, foreign key -- references b.a) table b   - id (int, primary key)   - type (enum)   -  (int) 

a record in table a may contain value a.a , a.b both may or may not reference same record in table b. suppose deadlock occurs on insert in table a, can cause of deadlock due acquiring lock twice same table? amount of deadlocks less if split table b table b , table b', based on value of type? in answer please don't argue bad design.

1. some websites show solution retrying try/catch

   one should always write application code handles deadlocks, , usual approach indeed retry transaction. documented under how cope deadlocks:

   deadlocks classic problem in transactional databases, not dangerous unless frequent cannot run transactions @ all. normally, must write applications prepared re-issue transaction if gets rolled because of deadlock.

   [ deletia ]

   you can cope deadlocks , reduce likelihood of occurrence following techniques:

   [ deletia ]

   • always prepared re-issue transaction if fails due deadlock. deadlocks not dangerous. try again.

2. to extend table design responsible occurrence of such deadlocks?

   table design of course contributing factor, motivated remodel schema (except introducing appropriate indexes if not present) purely because of deadlocks. not first remedy.

   instead, excessive deadlocks can resolved in one's application code: ensuring guilty transactions acquire locks in consistent order; reducing size of transactions committed , locks released asap; or utilising lower isolation level. failing that, 1 can serialise one's transactions.

3. suppose deadlock occurs on insert in table a, can cause of deadlock due acquiring lock twice same table?

   no: locks held session; once session holds lock, doesn't need reacquire it.

4. will amount of deadlocks less if split table b table b , table b', based on value of type?

   i cannot think of reason why such redesign alone reduce deadlocks, although potentially increase them (entails more records overall, requiring more locks).