How do I automatically receive real-time alerts when Amazon Redshift locks or blocking PIDS occur?

Resolution

Create an SNS topic and subscribe it to the target endpoints

To receive notifications on your endpoint, create an Amazon Simple Notification Service (Amazon SNS) topic and subscribe the topic to your target endpoints.

Complete the following steps:

1. Open the Amazon SNS console.
2. In the navigation pane, choose Topics, and then choose Create topic.
3. For Type, choose Standard.
4. For Name, enter a name, for example RedshiftBlockingAlertTopic.
5. Choose Create topic.
6. Under Subscriptions, choose Create subscription.
7. On the Protocol dropdown list, select the type of endpoint to subscribe to, for example Email or HTTPS.
8. For Endpoint, enter your endpoint type, such as your email address or web server that you want to receive notifications on.
9. Based on the endpoint type that you subscribed to, confirm the subscription from Amazon SNS. For example, if you chose Email, then you open your email inbox and confirm the subscription in the SNS confirmation email.

Configure IAM permissions for Lambda to access Amazon Redshift and Amazon SNS

To allow AWS Lambda to query Amazon Redshift and send SNS alerts, complete the following steps:

1. Create an AWS Identity and Access Management (IAM) role that's named LambdaRedshiftAlertRole.

2. For Service or use case, choose Lambda.

3. For Permissions policies, select the AmazonRedshiftDataFullAccess, AmazonSNSFullAccess, and AWSLambdaBasicExecutionRole AWS managed policies.

4. In the navigation pane, choose Roles, and then choose the LambdaRedshiftAlertRole role that you created.

5. In Permissions policies, choose Add permissions, and then choose Create inline policy.

6. For Policy editor, choose JSON, and then enter the following policy:

   {
       "Version": "2012-10-17",
       "Statement": [
           {
               "Sid": "AllowRedshiftDataAndCredentials",
               "Effect": "Allow",
               "Action": [
                   "redshift-data:*",
                   "redshift:GetClusterCredentials"
               ],
               "Resource": "*"
           }
       ]
   }

7. Choose Next.

8. For Policy name, enter AllowRedshiftCredentials.

9. Choose Create policy.

Create and deploy a Lambda function

To connect to Amazon Redshift and publish alerts to Amazon SNS, create a Lambda function that uses the Amazon Redshift Data API.

Complete the following steps:

1. Open the Lambda console.

2. In the navigation pane, choose Functions, and then choose Create function.

3. For function name, enter a name for your function, for example RedshiftLockMonitorFunction.

4. On the Runtime dropdown list, choose a supported version of Python.

5. Under Permissions, expand Change default execution role, and then choose Use an existing role.

6. For Existing role, choose LambdaRedshiftAlertRole, and then choose Create function.

7. In the Code source editor, enter the following code:

   import boto3
   import os
   import json
   from datetime import datetime
   redshift_data = boto3.client('redshift-data')
   sns = boto3.client('sns')
   def lambda_handler(event, context):
       cluster_id = os.environ['REDSHIFT_CLUSTER_ID']
       database = os.environ['DATABASE_NAME']
       db_user = os.environ['DB_USER']
       sns_topic_arn = os.environ['SNS_TOPIC_ARN']
       # ---- Your working Amazon Redshift lock detection query ----
       query = """
       SELECT
           a.txn_owner,
           a.txn_db,
           a.xid,
           a.pid,
           a.txn_start,
           a.lock_mode,
           a.relation AS table_id,
           NVL(TRIM(c."name"), d.relname) AS tablename,
           a.granted,
           b.pid AS blocking_pid,
           DATEDIFF(s, a.txn_start, GETDATE())/86400 || ' days ' ||
           DATEDIFF(s, a.txn_start, GETDATE())%86400/3600 || ' hrs ' ||
           DATEDIFF(s, a.txn_start, GETDATE())%3600/60 || ' mins ' ||
           DATEDIFF(s, a.txn_start, GETDATE())%60 || ' secs' AS txn_duration
       FROM svv_transactions a
       LEFT JOIN (
           SELECT pid, relation, granted
           FROM pg_locks
           GROUP BY 1, 2, 3
       ) b
           ON a.relation = b.relation
           AND a.granted = 'f'
           AND b.granted = 't'
       LEFT JOIN (
           SELECT * FROM stv_tbl_perm WHERE slice = 0
       ) c
           ON a.relation = c.id
       LEFT JOIN pg_class d
           ON a.relation = d.oid
       WHERE
           a.relation IS NOT NULL
           AND DATEDIFF(seconds, a.txn_start, GETDATE()) > 120
       ORDER BY
           a.txn_start DESC;
       """
       # Execute query using Amazon Redshift Data API
       response = redshift_data.execute_statement(
           ClusterIdentifier=cluster_id,
           Database=database,
           DbUser=db_user,
           Sql=query
       )
       query_id = response['Id']
       # Wait for query to complete
       desc = redshift_data.describe_statement(Id=query_id)
       while desc['Status'] in ['SUBMITTED', 'PICKED', 'STARTED']:
           desc = redshift_data.describe_statement(Id=query_id)
       # Check results
       if desc['Status'] == 'FINISHED':
           result = redshift_data.get_statement_result(Id=query_id)
           records = result.get('Records', [])
           if len(records) > 0:
               alert_msg = f":warning: Amazon Redshift Lock Alert — Detected at {datetime.utcnow()} UTC\n\n"
               for row in records:
                   alert_msg += (
                       f"Database: {row[1]['stringValue']}, "
                       f"User: {row[0]['stringValue']}, "
                       f"PID: {row[3]['longValue']}, "
                       f"Table: {row[7]['stringValue']}, "
                       f"Blocking PID: {row[9].get('longValue', 'N/A')}, "
                       f"Duration: {row[10]['stringValue']}\n"
                   )
               sns.publish(
                   TopicArn=sns_topic_arn,
                   Subject=":rotating_light: Amazon Redshift Alert: Blocking Lock > 2 min",
                   Message=alert_msg
               )
           else:
               print(":white_check_mark: No long-held locks detected.")
       else:
           print(f":x: Query failed: {desc.get('Error', 'Unknown error')}")
       return {"status": "done"}

8. Choose Deploy.

Add environment variables to your Lambda configuration

To improve security and update your Lambda function without the need to redeploy it, use the Lambda console to create environment variables.

For Environment variables, enter the following key-value pairs:

• Enter REDSHIFT_CLUSTER_ID, and then enter your Amazon Redshift cluster ID.
• Enter REDSHIFT_DATABASE, and then enter your database name.
• Enter REDSHIFT_USER, and then enter your database user.
• Enter REDSHIFT_REGION, and then enter your cluster's AWS Region.
• Enter SNS_TOPIC_ARN, and then enter the Amazon Resource Name (ARN) of your SNS topic for alerts.

Create an EventBridge schedule that automatically invokes your Lambda function

Complete the following steps:

1. Open the EventBridge console.
2. In the navigation pane, choose Schedules, and then choose Create schedule.
3. For Schedule name, enter a name, for example RedshiftLockMonitorSchedule.
4. Under Schedule pattern, choose Recurring schedule.
5. On the Time zone dropdown list, choose your time zone.
6. For Schedule type, choose Rate-based schedule.
7. For Rate expression, enter a value and choose your unit. For example, for Value, enter 2 and for Unit, choose minutes to invoke your Lambda function every 2 minutes.
8. For Flexible time window, choose Off.
9. Choose Next.
10. Under Target detail, choose AWS Lambda Invoke, then select the RedshiftLockMonitorFunction function.
11. Choose Next.
12. On the Action after schedule completion dropdown list, choose None.
13. Choose Next, and then choose Create schedule.

Related information

How do I find and release locks in Amazon Redshift?