At some specific moments in the week, some of my scheduled queries fail and the main reason is deadlocking process. As I go further on this question, it always appear this scenario to me:
ERROR: deadlock detected\n Detail: Process <A> waits for AccessShareLock on relation <B> of database <C>; blocked by process <D>.\nProcess <D> waits for AccessShareLock on relation <E> of database <C>; blocked by process <A>.
As I almost never catch up the error at the moment, I would like to know how can I translate the sentence above to something like:
"I´m having a deadlock problem because querys A and B are trying to modify the table C";
As I´m trying this for a reasonable time, I´ve already understood some of the features in the message, but it´s not clear to me what are the Process and the Relation Numbers, as they do not fit neither on PID nor XID;
With this answer, I will probably end up this cases! Thanks for all the further help.
Thanks for your answer!
As you passed me these tips, I´ve runned some querys and here´s what I´ve found out:
I appreciate a lot your support and it´s clearly helping me on the way! Hope I can get new tips by you.
Are you running any cross-database queries? If you have an ra3 instance type this is possible. If you are dc2 it is not.
A single transaction (iirc) can run across multiple databases, and the svv_table_info will only show you the table in that specific database, so you should change your connection if it's a cross-db query. This relation could also possibly be a temporary table that does not exist beyond the session for the query which may be why we see the deadlock.
If you have a support level for your account you can also raise a case, if you share the full error message there (I understand why you may want to edit it in a public forum) they can also help look for the PID/XID I find it unusual that the lock isn't related to one of these as transactions are what are awarded locks as they are opened and closed.