Symptoms:
When the deadlock happened, user could not do any operation as they got no response from system.
This issue also happened last year and it seems that it could be happened repeatedly. As the "metadata_cache" table being deadlock 3 times within 2 years and happened different time of day, it seems like random occurrence.
Investigation:
After we looking at the activity monitor in SQL Server Management Studio, we found that there were quite a few processes with "DELETE FROM metadata_cache WHERE id = 'xxxx-xxx-xx-xxx' " having deadlock. We also found that there are 3 records in metadata_cache table with the same type "meta_hash_base".
Action after encountering the issue:
The server being rebooted, the metadata_cache table was truncated and then asked user to login to the system. The records in metadata_cache table were then rebuilt and the system resumed.
Issue simulation:
I tried to create multiple "meta_hash_base" records in metadata_cache table. After I login to the system using a new browser instance, the system is then no response and the metadata_cache table being deadlock.
I would like to ask few questions regarding the metadata_cache table:
1. What is the role of metadata cache table? (There is an article on SugarCRM developer guide but it seems focus on the file and architecture on application server)
2. The operation that involved lookup on metadata cache table. (e.g. View the record in some modules)
3. How often will the entries in metadata cache table be updated / deleted / inserted?
4. Could we set the field "Type" as the key so that meta_hash_base entry will not be duplicated?
Thank you.
Environment: SugarCRM Pro 7.6.2.1, MS SQL Server 2014, PHP 5.4.45
