docs.aws.amazon.com
Open in
urlscan Pro
13.35.58.82
Public Scan
URL:
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-waits.lockrelation.html
Submission: On June 19 via api from DE — Scanned from DE
Submission: On June 19 via api from DE — Scanned from DE
Form analysis 0 forms found in the DOM
Text Content
SELECT YOUR COOKIE PREFERENCES
We use essential cookies and similar tools that are necessary to provide our
site and services. We use performance cookies to collect anonymous statistics so
we can understand how customers use our site and make improvements. Essential
cookies cannot be deactivated, but you can click “Customize cookies” to decline
performance cookies.
If you agree, AWS and approved third parties will also use cookies to provide
useful site features, remember your preferences, and display relevant content,
including relevant advertising. To continue without accepting these cookies,
click “Continue without accepting.” To make more detailed choices or learn more,
click “Customize cookies.”
Accept all cookiesContinue without acceptingCustomize cookies
CUSTOMIZE COOKIE PREFERENCES
We use cookies and similar tools (collectively, "cookies") for the following
purposes.
ESSENTIAL
Essential cookies are necessary to provide our site and services and cannot be
deactivated. They are usually set in response to your actions on the site, such
as setting your privacy preferences, signing in, or filling in forms.
PERFORMANCE
Performance cookies provide anonymous statistics about how customers navigate
our site so we can improve site experience and performance. Approved third
parties may perform analytics on our behalf, but they cannot use the data for
their own purposes.
Allow performance category
Allowed
FUNCTIONAL
Functional cookies help us provide useful site features, remember your
preferences, and display relevant content. Approved third parties may set these
cookies to provide certain site features. If you do not allow these cookies,
then some or all of these services may not function properly.
Allow functional category
Allowed
ADVERTISING
Advertising cookies may be set through our site by us or our advertising
partners and help us deliver relevant marketing content. If you do not allow
these cookies, you will experience less relevant advertising.
Allow advertising category
Allowed
Blocking some types of cookies may impact your experience of our sites. You may
review and change your choices at any time by clicking Cookie preferences in the
footer of this site. We and selected third-parties use cookies or similar
technologies as specified in the AWS Cookie Notice.
CancelSave preferences
UNABLE TO SAVE COOKIE PREFERENCES
We will only store essential cookies at this time, because we were unable to
save your cookie preferences.
If you want to change your cookie preferences, try again later using the link in
the AWS console footer, or contact support if the problem persists.
Dismiss
Contact Us
English
Create an AWS Account
1. AWS
2. ...
3. Documentation
4. Amazon RDS
5. User Guide for Aurora
Feedback
Preferences
AMAZON AURORA
USER GUIDE FOR AURORA
* What is Aurora?
* Aurora DB clusters
* Aurora versions
* Regions and Availability Zones
* Supported Aurora features by Region and engine
* Blue/Green Deployments
* Aurora cluster configurations
* Database activity streams
* Exporting cluster data to Amazon S3
* Exporting snapshot data to Amazon S3
* Aurora global databases
* IAM database authentication
* Kerberos authentication
* Aurora machine learning
* Performance Insights
* Zero-ETL integrations
* RDS Proxy
* Secrets Manager integration
* Aurora Serverless v2
* Aurora Serverless v1
* RDS Data API
* Zero-downtime patching (ZDP)
* Engine-native features
* Aurora connection management
* DB instance classes
* Aurora storage and reliability
* Aurora security
* High availability for Amazon Aurora
* Replication with Aurora
* DB instance billing for Aurora
* On-Demand DB instances
* Reserved DB instances
* Setting up your environment
* Getting started
* Creating and connecting to an Aurora MySQL DB cluster
* Creating and connecting to an Aurora PostgreSQL DB cluster
* Tutorial: Create a web server and an Amazon Aurora DB cluster
* Launch an EC2 instance
* Create a DB cluster
* Install a web server
* Tutorials and sample code
* Configuring your Aurora DB cluster
* Creating a DB cluster
* Creating resources with AWS CloudFormation
* Connecting to a DB cluster
* Working with parameter groups
* Overview of parameter groups
* Working with DB cluster parameter groups
* Working with DB parameter groups
* Comparing DB parameter groups
* Specifying DB parameters
* Migrating data to a DB cluster
* Creating an ElastiCache cache from Amazon RDS
* Managing an Aurora DB cluster
* Stopping and starting a cluster
* Connecting an AWS compute resource
* Connecting an EC2 instance
* Connecting a Lambda function
* Modifying an Aurora DB cluster
* Adding Aurora Replicas
* Managing performance and scaling
* Cloning a volume for an Aurora DB cluster
* Integrating with AWS services
* Using Auto Scaling with Aurora Replicas
* Maintaining an Aurora DB cluster
* Rebooting an Aurora DB cluster or instance
* Deleting Aurora clusters and instances
* Tagging RDS resources
* Working with ARNs
* Aurora updates
* Using RDS Extended Support
* RDS Extended Support overview
* RDS Extended Support charges
* Versions with RDS Extended Support
* Responsibilities with RDS Extended Support
* Creating an Aurora DB cluster or a global cluster
* Viewing RDS Extended Support enrollment
* Restoring an Aurora DB cluster or a global cluster
* Using Blue/Green Deployments for database updates
* Overview of Amazon RDS Blue/Green Deployments
* Creating a blue/green deployment
* Viewing a blue/green deployment
* Switching a blue/green deployment
* Deleting a blue/green deployment
* Backing up and restoring an Aurora DB cluster
* Overview of backing up and restoring
* Backup storage
* Creating a DB cluster snapshot
* Restoring from a DB cluster snapshot
* Copying a DB cluster snapshot
* Sharing a DB cluster snapshot
* Exporting DB cluster data to Amazon S3
* Exporting DB cluster snapshot data to Amazon S3
* Point-in-time recovery
* Deleting a DB cluster snapshot
* Tutorial: Restore a DB cluster from a snapshot
* Monitoring metrics in an Aurora DB cluster
* Overview of monitoring
* Viewing cluster status
* Viewing and responding to Amazon Aurora recommendations
* Viewing Amazon Aurora recommendations
* Responding to Amazon Aurora recommendations
* Viewing metrics in the Amazon RDS console
* Viewing combined metrics in the Amazon RDS console
* Monitoring Aurora with CloudWatch
* Overview of Amazon Aurora and Amazon CloudWatch
* Viewing CloudWatch metrics
* Exporting Performance Insights metrics to CloudWatch
* Creating CloudWatch alarms
* Monitoring DB load with Performance Insights
* Overview of Performance Insights
* Database load
* Maximum CPU
* Amazon Aurora DB engine, Region, and instance class support for
Performance Insights
* Pricing and data retention for Performance Insights
* Turning Performance Insights on and off
* Turning on the Performance Schema for Aurora MySQL
* Performance Insights policies
* Analyzing metrics with the Performance Insights dashboard
* Overview of the dashboard
* Accessing the dashboard
* Analyzing DB load
* Analyzing database performance for a period of time
* Analyzing queries
* Overview of the Top SQL tab
* Accessing more SQL text
* Viewing SQL statistics
* Viewing Performance Insights proactive recommendations
* Retrieving metrics with the Performance Insights API
* Logging Performance Insights calls using AWS CloudTrail
* Analyzing performance with DevOps Guru for RDS
* Monitoring the OS with Enhanced Monitoring
* Overview of Enhanced Monitoring
* Setting up and enabling Enhanced Monitoring
* Viewing OS metrics in the RDS console
* Viewing OS metrics using CloudWatch Logs
* Aurora metrics reference
* CloudWatch metrics for Aurora
* CloudWatch dimensions for Aurora
* Availability of Aurora metrics in the Amazon RDS console
* CloudWatch metrics for Performance Insights
* Counter metrics for Performance Insights
* SQL statistics for Performance Insights
* SQL statistics for Aurora MySQL
* SQL statistics for Aurora PostgreSQL
* OS metrics in Enhanced Monitoring
* Monitoring events, logs, and database activity streams
* Viewing logs, events, and streams in the Amazon RDS console
* Monitoring Aurora events
* Overview of events for Aurora
* Viewing Amazon RDS events
* Working with Amazon RDS event notification
* Overview of Amazon RDS event notification
* Granting permissions
* Subscribing to Amazon RDS event notification
* Amazon RDS event notification tags and attributes
* Listing Amazon RDS event notification subscriptions
* Modifying an Amazon RDS event notification subscription
* Adding a source identifier to an Amazon RDS event notification
subscription
* Removing a source identifier from an Amazon RDS event notification
subscription
* Listing the Amazon RDS event notification categories
* Deleting an Amazon RDS event notification subscription
* Creating a rule that triggers on an Amazon Aurora event
* Amazon RDS event categories and event messages for Aurora
* Monitoring Aurora logs
* Viewing and listing database log files
* Downloading a database log file
* Watching a database log file
* Publishing to CloudWatch Logs
* Reading log file contents using REST
* MySQL database log files
* Overview of Aurora MySQL database logs
* Publishing Aurora MySQL logs to Amazon CloudWatch Logs
* Managing table-based Aurora MySQL logs
* Configuring Aurora MySQL binary logging
* Accessing MySQL binary logs
* PostgreSQL database log files
* Monitoring Aurora API calls in CloudTrail
* Monitoring Aurora with Database Activity Streams
* Overview
* Aurora MySQL network prerequisites
* Starting a database activity stream
* Getting the activity stream status
* Stopping a database activity stream
* Monitoring activity streams
* Managing access to activity streams
* Monitoring threats with GuardDuty RDS Protection
* Working with Aurora MySQL
* Overview of Aurora MySQL
* Aurora MySQL version 3 compatible with MySQL 8.0
* New temporary table behavior in Aurora MySQL version 3
* Comparison of Aurora MySQL version 2 and Aurora MySQL version 3
* Comparison of Aurora MySQL version 3 and MySQL 8.0 Community Edition
* Upgrading to Aurora MySQL version 3
* Aurora MySQL version 2 compatible with MySQL 5.7
* Security with Aurora MySQL
* Updating applications for new TLS certificates
* Using Kerberos authentication for Aurora MySQL
* Setting up Kerberos authentication for Aurora MySQL
* Connecting to Aurora MySQL with Kerberos authentication
* Managing a DB cluster in a domain
* Migrating data to Aurora MySQL
* Migrating from an external MySQL database to Aurora MySQL
* Physical migration using Percona XtraBackup and Amazon S3
* Reducing the physical migration time
* Logical migration using mysqldump
* Migrating from a MySQL DB instance to Aurora MySQL
* Migrating an RDS for MySQL snapshot to Aurora
* Migrating from RDS for MySQL to Aurora MySQL using a read replica
* Managing Aurora MySQL
* Managing performance and scaling for Amazon Aurora MySQL
* Backtracking a DB cluster
* Testing Amazon Aurora MySQL using fault injection queries
* Altering tables in Amazon Aurora using Fast DDL
* Displaying volume status for an Aurora DB cluster
* Tuning Aurora MySQL
* Essential concepts for Aurora MySQL tuning
* Tuning Aurora MySQL with wait events
* cpu
* io/aurora_redo_log_flush
* io/aurora_respond_to_client
* io/redo_log_flush
* io/socket/sql/client_connection
* io/table/sql/handler
* synch/cond/innodb/row_lock_wait
* synch/cond/innodb/row_lock_wait_cond
* synch/cond/sql/MDL_context::COND_wait_status
* synch/mutex/innodb/aurora_lock_thread_slot_futex
* synch/mutex/innodb/buf_pool_mutex
* synch/mutex/innodb/fil_system_mutex
* synch/mutex/innodb/trx_sys_mutex
* synch/sxlock/innodb/hash_table_locks
* Tuning Aurora MySQL with thread states
* creating sort index
* sending data
* Tuning Aurora MySQL with Amazon DevOps Guru proactive insights
* The InnoDB history list length increased significantly
* Database is creating temporary tables on disk
* Parallel query for Aurora MySQL
* Advanced Auditing with Aurora MySQL
* Replication with Aurora MySQL
* Using local write forwarding
* Cross-Region replication
* Using binary log (binlog) replication
* Using GTID-based replication
* Integrating Aurora MySQL with AWS services
* Authorizing Aurora MySQL to access AWS services
* Setting up IAM roles to access AWS services
* Creating an IAM policy to access Amazon S3
* Creating an IAM policy to access Lambda
* Creating an IAM policy to access CloudWatch Logs
* Creating an IAM policy to access AWS KMS
* Creating an IAM role to access AWS services
* Associating an IAM role with a DB cluster
* Enabling network communication to AWS services
* Loading data from text files in Amazon S3
* Saving data into text files in Amazon S3
* Invoking a Lambda function from Aurora MySQL
* Publishing Aurora MySQL logs to CloudWatch Logs
* Aurora MySQL lab mode
* Best practices with Aurora MySQL
* Troubleshooting Aurora MySQL performance
* Troubleshooting workload issues
* Logging for Aurora MySQL
* Troubleshooting query performance
* Aurora MySQL reference
* Configuration parameters
* Wait events
* Thread states
* Isolation levels
* Hints
* Stored procedures
* Configuring
* Ending a session or query
* Logging
* Managing the Global Status History
* Replicating
* information_schema tables
* Aurora MySQL updates
* Version Numbers and Special Versions
* Preparing for Aurora MySQL version 2 end of life
* Preparing for Aurora MySQL version 1 end of life
* Upgrading Amazon Aurora MySQL DB clusters
* Upgrading the minor version or patch level of an Aurora MySQL DB
cluster
* Upgrading the Aurora MySQL major version of a DB cluster
* Database engine updates and fixes for Amazon Aurora MySQL
* Working with Aurora PostgreSQL
* The database preview environment
* Security with Aurora PostgreSQL
* Understanding PostgreSQL roles and permissions
* Updating applications for new SSL/TLS certificates
* Using Kerberos authentication
* Setting up
* Managing a DB cluster in a Domain
* Connecting with Kerberos authentication
* Using AD security groups for Aurora PostgreSQL access control
* Migrating data to Aurora PostgreSQL
* Improving query performance with Aurora Optimized Reads
* Using Babelfish for Aurora PostgreSQL
* Babelfish limitations
* Understanding Babelfish architecture and configuration
* Babelfish architecture
* DB cluster parameter group settings for Babelfish
* Collations supported by Babelfish
* Managing collations
* Collation limitations and differences
* Managing Babelfish error handling
* Creating a Babelfish for Aurora PostgreSQL DB cluster
* Migrating a SQL Server database to Babelfish
* Database authentication with Babelfish for Aurora PostgreSQL
* Connecting to a Babelfish DB cluster
* Creating C# or JDBC client connections to Babelfish
* Using a SQL Server client to connect to your DB cluster
* Using a PostgreSQL client to connect to your DB cluster
* Working with Babelfish
* Getting information from the Babelfish system catalog
* Differences between Babelfish for Aurora PostgreSQL and SQL Server
* T-SQL differences in Babelfish
* Transaction Isolation Levels in Babelfish
* Using Babelfish features with limited implementation
* Improving Babelfish query performance
* Using explain plan to improve query performance
* Using T-SQL query hints to improve Babelfish query performance
* Using Aurora PostgreSQL extensions with Babelfish
* Babelfish supports linked servers
* Using Full Text Search in Babelfish
* Babelfish supports Geospatial data types
* Troubleshooting Babelfish
* Turning off Babelfish
* Babelfish versions
* Identifying your version of Babelfish
* Upgrading Babelfish to a new version
* Using Babelfish product version parameter
* Babelfish reference
* Unsupported functionality
* Supported functionality by Babelfish version
* Babelfish procedure reference
* Managing Aurora PostgreSQL
* Testing Amazon Aurora PostgreSQL by using fault injection queries
* Displaying volume status for an Aurora DB cluster
* Specifying the RAM disk for the stats_temp_directory
* Managing temporary files with PostgreSQL
* Tuning with wait events for Aurora PostgreSQL
* Essential concepts for Aurora PostgreSQL tuning
* Aurora PostgreSQL wait events
* Client:ClientRead
* Client:ClientWrite
* CPU
* IO:BufFileRead and IO:BufFileWrite
* IO:DataFileRead
* IO:XactSync
* IPC:DamRecordTxAck
* Lock:advisory
* Lock:extend
* Lock:Relation
* Lock:transactionid
* Lock:tuple
* LWLock:buffer_content (BufferContent)
* LWLock:buffer_mapping
* LWLock:BufferIO (IPC:BufferIO)
* LWLock:lock_manager
* LWLock:MultiXact
* Timeout:PgSleep
* Tuning Aurora PostgreSQL with Amazon DevOps Guru proactive insights
* Database has long running idle in transaction connection
* Best practices with Aurora PostgreSQL
* Diagnosing table and index bloat
* Improved memory management in Aurora PostgreSQL
* Fast failover
* Fast recovery after failover
* Managing connection churn
* Tuning memory parameters for Aurora PostgreSQL
* Analyze resource usage with CloudWatch metrics
* Using logical replication for a major version upgrade
* Troubleshooting storage issues
* Replication with Aurora PostgreSQL
* Using logical replication
* Using Aurora PostgreSQL as a Knowledge Base for Amazon Bedrock
* Integrating Aurora PostgreSQL with AWS services
* Importing data from Amazon S3 into Aurora PostgreSQL
* Exporting PostgreSQL data to Amazon S3
* Invoking a Lambda function from Aurora PostgreSQL
* Lambda function and parameter reference
* Publishing Aurora PostgreSQL logs to CloudWatch Logs
* Monitoring query execution plans for Aurora PostgreSQL
* Managing query execution plans for Aurora PostgreSQL
* Overview of Aurora PostgreSQL query plan management
* Best practices for Aurora PostgreSQL query plan management
* Understanding query plan management
* Capturing Aurora PostgreSQL execution plans
* Using Aurora PostgreSQL managed plans
* Examining Aurora PostgreSQL query plans in the dba_plans view
* Maintaining Aurora PostgreSQL execution plans
* Reference
* Parameter reference for Aurora PostgreSQL query plan management
* Function reference for Aurora PostgreSQL query plan management
* Reference for the apg_plan_mgmt.dba_plans view
* Advanced features in Query Plan Management
* Capturing Aurora PostgreSQL execution plans in Replicas
* Supporting table partition by Query Plan Management
* Working with extensions and foreign data wrappers
* Using Amazon Aurora delegated extension support for PostgreSQL
* Managing large objects more efficiently with the lo module
* Managing spatial data with PostGIS
* Managing partitions with the pg_partman extension
* Scheduling maintenance with the pg_cron extension
* Using pgAudit to log database activity
* Using pglogical to synchronize data
* Supported foreign data wrappers
* Working with Trusted Language Extensions for PostgreSQL
* Functions reference for Trusted Language Extensions
* pgtle.available_extensions
* pgtle.available_extension_versions
* pgtle.extension_update_paths
* pgtle.install_extension
* pgtle.install_update_path
* pgtle.register_feature
* pgtle.register_feature_if_not_exists
* pgtle.set_default_version
* pgtle.uninstall_extension
* pgtle.uninstall_extension
* pgtle.uninstall_extension_if_exists
* pgtle.uninstall_update_path
* pgtle.uninstall_update_path_if_exists
* pgtle.unregister_feature
* pgtle.unregister_feature_if_exists
* Hooks reference for Trusted Language Extensions
* Password check hook (passcheck)
* Aurora PostgreSQL reference
* Collations supported in Aurora PostgreSQL
* Aurora PostgreSQL functions reference
* aurora_db_instance_identifier
* aurora_ccm_status
* aurora_global_db_instance_status
* aurora_global_db_status
* aurora_list_builtins
* aurora_replica_status
* aurora_stat_activity
* aurora_stat_backend_waits
* aurora_stat_bgwriter
* aurora_stat_database
* aurora_stat_dml_activity
* aurora_stat_get_db_commit_latency
* aurora_stat_logical_wal_cache
* aurora_stat_memctx_usage
* aurora_stat_optimized_reads_cache
* aurora_stat_plans
* aurora_stat_reset_wal_cache
* aurora_stat_statements
* aurora_stat_system_waits
* aurora_stat_wait_event
* aurora_stat_wait_type
* aurora_version
* aurora_volume_logical_start_lsn
* aurora_wait_report
* Aurora PostgreSQL parameters
* Aurora PostgreSQL wait events
* Aurora PostgreSQL updates
* Identifying versions of Amazon Aurora PostgreSQL
* Aurora PostgreSQL releases
* Extension versions for Aurora PostgreSQL
* Upgrading Amazon Aurora PostgreSQL DB clusters
* Using a long-term support (LTS) release
* Using Aurora global databases
* Getting started with Aurora global databases
* Managing an Aurora global database
* Connecting to an Aurora global database
* Using write forwarding in an Aurora global database
* Using write forwarding in Aurora MySQL
* Using write forwarding in Aurora PostgreSQL
* Using switchover or failover in an Aurora global database
* Monitoring an Aurora global database
* Using Aurora global databases with other AWS services
* Upgrading an Amazon Aurora global database
* Using RDS Proxy
* Planning where to use RDS Proxy
* RDS Proxy concepts and terminology
* Getting started with RDS Proxy
* Managing an RDS Proxy
* Working with RDS Proxy endpoints
* Monitoring RDS Proxy with CloudWatch
* Working with RDS Proxy events
* RDS Proxy examples
* Troubleshooting RDS Proxy
* Using RDS Proxy with AWS CloudFormation
* Using RDS Proxy with Aurora global databases
* Working with zero-ETL integrations
* Getting started with zero-ETL integrations
* Creating zero-ETL integrations
* Data filtering for zero-ETL integrations
* Adding and querying data
* Viewing and monitoring zero-ETL integrations
* Modifying zero-ETL integrations
* Deleting zero-ETL integrations
* Troubleshooting zero-ETL integrations
* Using Aurora Serverless v2
* How Aurora Serverless v2 works
* Requirements and limitations for Aurora Serverless v2
* Creating an Aurora Serverless v2 DB cluster
* Managing Aurora Serverless v2
* Performance and scaling for Aurora Serverless v2
* Migrating to Aurora Serverless v2
* Using Aurora Serverless v1
* How Aurora Serverless v1 works
* Creating an Aurora Serverless v1 DB cluster
* Restoring an Aurora Serverless v1 DB cluster
* Modifying an Aurora Serverless v1 DB cluster
* Scaling Aurora Serverless v1 DB cluster capacity manually
* Viewing Aurora Serverless v1 DB clusters
* Deleting an Aurora Serverless v1 DB cluster
* Aurora Serverless v1 and Aurora database engine versions
* Using RDS Data API
* Logging RDS Data API calls with AWS CloudTrail
* Using the query editor
* DBQMS API reference
* Using Aurora machine learning
* Using Aurora machine learning with Aurora MySQL
* Using Aurora machine learning with Aurora PostgreSQL
* Code examples
* Actions
* CreateDBCluster
* CreateDBClusterParameterGroup
* CreateDBClusterSnapshot
* CreateDBInstance
* DeleteDBCluster
* DeleteDBClusterParameterGroup
* DeleteDBInstance
* DescribeDBClusterParameterGroups
* DescribeDBClusterParameters
* DescribeDBClusterSnapshots
* DescribeDBClusters
* DescribeDBEngineVersions
* DescribeDBInstances
* DescribeOrderableDBInstanceOptions
* ModifyDBClusterParameterGroup
* Scenarios
* Get started with DB clusters
* Cross-service examples
* Create a lending library REST API
* Create an Aurora Serverless work item tracker
* Best practices with Aurora
* Performing an Aurora proof of concept
* Security
* Database authentication
* Password management with Aurora and Secrets Manager
* Data protection
* Data encryption
* Encrypting Amazon Aurora resources
* AWS KMS key management
* Using SSL/TLS to encrypt a connection
* Rotating your SSL/TLS certificate
* Internetwork traffic privacy
* Identity and access management
* How Amazon Aurora works with IAM
* Identity-based policy examples
* AWS managed policies
* Policy updates
* Cross-service confused deputy prevention
* IAM database authentication
* Enabling and disabling
* Creating and using an IAM policy for IAM database access
* Creating a database account using IAM authentication
* Connecting to your DB cluster using IAM authentication
* Connecting to your DB cluster using IAM authentication with the
AWS drivers
* Connecting using IAM: AWS CLI and mysql client
* Connecting using IAM authentication from the command line: AWS
CLI and psql client
* Connecting using IAM authentication and the AWS SDK for .NET
* Connecting using IAM authentication and the AWS SDK for Go
* Connecting using IAM authentication and the AWS SDK for Java
* Connecting using IAM authentication and the AWS SDK for Python
(Boto3)
* Troubleshooting
* Logging and monitoring
* Compliance validation
* Resilience
* Infrastructure security
* VPC endpoints (AWS PrivateLink)
* Security best practices
* Controlling access with security groups
* Master user account privileges
* Service-linked roles
* Using Amazon Aurora with Amazon VPC
* Working with a DB cluster in a VPC
* Scenarios for accessing a DB cluster in a VPC
* Tutorial: Create a VPC for use with a DB cluster (IPv4 only)
* Tutorial: Create a VPC for use with a DB cluster (dual-stack mode)
* Quotas and constraints
* Troubleshooting
* Amazon RDS API reference
* Using the Query API
* Troubleshooting applications
* Document history
* AWS Glossary
Lock:Relation - Amazon Aurora
AWSDocumentationAmazon RDSUser Guide for Aurora
Supported engine versionsContextLikely causes of increased waitsActions
LOCK:RELATION
PDFRSS
The Lock:Relation event occurs when a query is waiting to acquire a lock on a
table or view (relation) that's currently locked by another transaction.
TOPICS
* Supported engine versions
* Context
* Likely causes of increased waits
* Actions
SUPPORTED ENGINE VERSIONS
This wait event information is supported for all versions of Aurora PostgreSQL.
CONTEXT
Most PostgreSQL commands implicitly use locks to control concurrent access to
data in tables. You can also use these locks explicitly in your application code
with the LOCK command. Many lock modes aren't compatible with each other, and
they can block transactions when they're trying to access the same object. When
this happens, Aurora PostgreSQL generates a Lock:Relation event. Some common
examples are the following:
* Exclusive locks such as ACCESS EXCLUSIVE can block all concurrent access.
Data definition language (DDL) operations such as DROP TABLE, TRUNCATE,
VACUUM FULL, and CLUSTER acquire ACCESS EXCLUSIVE locks implicitly. ACCESS
EXCLUSIVE is also the default lock mode for LOCK TABLE statements that don't
specify a mode explicitly.
* Using CREATE INDEX (without CONCURRENT) on a table conflicts with data
manipulation language (DML) statements UPDATE, DELETE, and INSERT, which
acquire ROW EXCLUSIVE locks.
For more information about table-level locks and conflicting lock modes, see
Explicit Locking in the PostgreSQL documentation.
Blocking queries and transactions typically unblock in one of the following
ways:
* Blocking query – The application can cancel the query or the user can end the
process. The engine can also force the query to end because of a session's
statement-timeout or a deadlock detection mechanism.
* Blocking transaction – A transaction stops blocking when it runs a ROLLBACK
or COMMIT statement. Rollbacks also happen automatically when sessions are
disconnected by a client or by network issues, or are ended. Sessions can be
ended when the database engine is shut down, when the system is out of
memory, and so forth.
LIKELY CAUSES OF INCREASED WAITS
When the Lock:Relation event occurs more frequently than normal, it can indicate
a performance issue. Typical causes include the following:
Increased concurrent sessions with conflicting table locks
There might be an increase in the number of concurrent sessions with queries
that lock the same table with conflicting locking modes.
Maintenance operations
Health maintenance operations such as VACUUM and ANALYZE can significantly
increase the number of conflicting locks. VACUUM FULL acquires an ACCESS
EXCLUSIVE lock, and ANALYSE acquires a SHARE UPDATE EXCLUSIVE lock. Both types
of locks can cause a Lock:Relation wait event. Application data maintenance
operations such as refreshing a materialized view can also increase blocked
queries and transactions.
Locks on reader instances
There might be a conflict between the relation locks held by the writer and
readers. Currently, only ACCESS EXCLUSIVE relation locks are replicated to
reader instances. However, the ACCESS EXCLUSIVE relation lock will conflict with
any ACCESS SHARE relation locks held by the reader. This can cause an increase
in lock relation wait events on the reader.
ACTIONS
We recommend different actions depending on the causes of your wait event.
TOPICS
* Reduce the impact of blocking SQL statements
* Minimize the effect of maintenance operations
* Check for reader locks
REDUCE THE IMPACT OF BLOCKING SQL STATEMENTS
To reduce the impact of blocking SQL statements, modify your application code
where possible. Following are two common techniques for reducing blocks:
* Use the NOWAIT option – Some SQL commands, such as SELECT and LOCK
statements, support this option. The NOWAIT directive cancels the
lock-requesting query if the lock can't be acquired immediately. This
technique can help prevent a blocking session from causing a pile-up of
blocked sessions behind it.
For example: Assume that transaction A is waiting on a lock held by
transaction B. Now, if B requests a lock on a table that’s locked by
transaction C, transaction A might be blocked until transaction C completes.
But if transaction B uses a NOWAIT when it requests the lock on C, it can
fail fast and ensure that transaction A doesn't have to wait indefinitely.
* Use SET lock_timeout – Set a lock_timeout value to limit the time a SQL
statement waits to acquire a lock on a relation. If the lock isn't acquired
within the timeout specified, the transaction requesting the lock is
cancelled. Set this value at the session level.
MINIMIZE THE EFFECT OF MAINTENANCE OPERATIONS
Maintenance operations such as VACUUM and ANALYZE are important. We recommend
that you don't turn them off because you find Lock:Relation wait events related
to these maintenance operations. The following approaches can minimize the
effect of these operations:
* Run maintenance operations manually during off-peak hours.
* To reduce Lock:Relation waits caused by autovacuum tasks, perform any needed
autovacuum tuning. For information about tuning autovacuum, see Working with
PostgreSQL autovacuum on Amazon RDS in the Amazon RDS User Guide.
CHECK FOR READER LOCKS
You can see how concurrent sessions on a writer and readers might be holding
locks that block each other. One way to do this is by running queries that
return the lock type and relation. In the table you can find a sequence of
queries to two such concurrent sessions, a writer session (left-hand column) and
a reader session (right-hand column).
The replay process waits for the duration of max_standby_streaming_delay before
cancelling the reader query. As shown in the example, the lock timeout of 100ms
is well below the default max_standby_streaming_delay of 30 seconds. The lock
times out before it's an issue.
Writer session Reader session
export WRITER=aurorapg1.12345678910.us-west-1.rds.amazonaws.com
psql -h $WRITER
psql (15devel, server 10.14)
Type "help" for help.
export READER=aurorapg2.12345678910.us-west-1.rds.amazonaws.com
psql -h $READER
psql (15devel, server 10.14)
Type "help" for help.
The writer session creates table t1 on the writer instance. The ACCESS EXCLUSIVE
lock is acquired on the writer immediately, assuming that there are no
conflicting queries on the writer.
postgres=> CREATE TABLE t1(b integer);
CREATE TABLE
The reader session sets a lock timeout interval of 100 milliseconds.
postgres=> SET lock_timeout=100;
SET
The reader session tries to read data from table t1 on the reader instance.
postgres=> SELECT * FROM t1;
b
---
(0 rows)
The writer session drops t1.
postgres=> BEGIN;
BEGIN
postgres=> DROP TABLE t1;
DROP TABLE
postgres=>
The query times out and is canceled on the reader.
postgres=> SELECT * FROM t1;
ERROR: canceling statement due to lock timeout
LINE 1: SELECT * FROM t1;
^
The reader session queries pg_locks and pg_stat_activity to determine the cause
of the error. The result indicates that the aurora wal replay process is holding
an ACCESS EXCLUSIVE lock on table t1.
postgres=> SELECT locktype, relation, mode, backend_type
postgres-> FROM pg_locks l, pg_stat_activity t1
postgres-> WHERE l.pid=t1.pid AND relation = 't1'::regclass::oid;
locktype | relation | mode | backend_type
----------+----------+---------------------+-------------------
relation | 68628525 | AccessExclusiveLock | aurora wal replay
(1 row)
Javascript is disabled or is unavailable in your browser.
To use the Amazon Web Services Documentation, Javascript must be enabled. Please
refer to your browser's Help pages for instructions.
Document Conventions
Lock:extend
Lock:transactionid
Did this page help you? - Yes
Thanks for letting us know we're doing a good job!
If you've got a moment, please tell us what we did right so we can do more of
it.
Did this page help you? - No
Thanks for letting us know this page needs work. We're sorry we let you down.
If you've got a moment, please tell us how we can make the documentation better.
DID THIS PAGE HELP YOU?
Yes
No
Provide feedback
NEXT TOPIC:
Lock:transactionid
PREVIOUS TOPIC:
Lock:extend
NEED HELP?
* Try AWS re:Post
* Connect with an AWS IQ expert
PrivacySite termsCookie preferences
© 2024, Amazon Web Services, Inc. or its affiliates. All rights reserved.
ON THIS PAGE
* Supported engine versions
* Context
* Likely causes of increased waits
* Actions