By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

Problems reading Redshift describe statement about Deadlock

1

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.

Topics
DatabaseAnalytics
Tags
DatabaseAmazon Redshift
Language
English
Julio
asked 9 months ago271 views
1 Answer
1

The relation number is your table ID, i.e the object that the lock is awarded on. The process is is most likely the PID, can you check against stl_connection_log and stl_query (xid) column to identify which one it is?

You can find the ID in https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_TABLE_INFO.html

lukeshea
answered 9 months ago
  • Julio
    9 months ago

    Thanks for your answer!

    As you passed me these tips, I´ve runned some querys and here´s what I´ve found out:

    1. In the sentence that I´ve mentioned earlier, <E> is clearly a table_id (so you´re right), but I don´t know why <B> is not!;
    2. I´ve also tried all the numbers I have in the sentence in PID or XID and none of them were useful.
    3. In the complete error message, it´s possible to me to achieve the PID, but I think that discovering what <B> is would solve my problem!

    I appreciate a lot your support and it´s clearly helping me on the way! Hope I can get new tips by you.

  • lukeshea
    9 months ago

    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.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content