Look at REMOVING id column from cache tables

After some digging into cache table deadlocks I have concluded that REMOVING the auto increment field may improve the frequency of deadlocks. The issue is that when it goes to INSERT into a table it locks the number of ids it will need for the insert. If the query is at all slow then it takes time to calculate this lock.

So, 2 separate processes cannot insert to the same table at the same time because it has to finish one to calculate the next auto-increment.

However, the id field adds no value to these cache tables:
civicrm_group_contact_cache
civicrm_acl_contact_cache
civicrm_cache

With some hesitation I would also add to that list
civicrm_prevnext_cache

I had a go at removing the id from civicrm_group_contact_cache and I will add a PR for the changes I had to make to get past any errors. So far it seems to have led to a reduction in auto-inc errors

I'm also playing with adjusting the indexes on group_contact_cache to

UNIQUE INDEX `UI_contact_group` (`group_id`, `contact_id`),
INDEX `index_contact` (`contact_id`)

The logic being the gap lock will lock less rows for the unique index with group first since it is usually only a single group, but a wide range of contacts

See this one for more on gap locks & also for my next approach
{{SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
session2> BEGIN;
session2> INSERT INTO actor (first_name,last_name) VALUES ('Bob','Davis');Query OK, 1 row affected (0.02 sec)

session1> COMMIT}};
http://blog.9minutesnooze.com/diagnosing-mysql-autoinc-gap-locks-ideeli-tech-blog/