用PYTHON写一个监控数据库事务锁定情况，并自动解锁长时间被锁定的事务，同时将执行情况邮件反馈运维人员

以下是一个使用Python编写的监控数据库事务锁定情况并自动解锁长时间被锁定的事务的示例代码，并将执行情况通过邮件反馈给运维人员：

import psycopg2
import smtplib
from email.mime.text import MIMEText
from email.header import Header

# 监控函数
def monitor_database_locks(db_host, db_port, db_name, db_user, db_password, email_receiver):
    # 邮件相关信息
    smtp_server = 'your_smtp_server'
    smtp_port = 587
    smtp_username = 'your_smtp_username'
    smtp_password = 'your_smtp_password'
    sender_email = 'your_sender_email@example.com'
    subject = '数据库事务锁定情况监控报告'

    # 连接数据库
    conn = psycopg2.connect(host=db_host, port=db_port, dbname=db_name, user=db_user, password=db_password)
    cursor = conn.cursor()

    # 查询锁定的事务
    cursor.execute("SELECT pid, query_start, state, query FROM pg_stat_activity WHERE state = 'idle in transaction';")
    locked_transactions = cursor.fetchall()

    # 解锁长时间被锁定的事务
    for transaction in locked_transactions:
        pid = transaction[0]
        query_start = transaction[1]
        state = transaction[2]
        query = transaction[3]

        # 判断是否长时间被锁定（示例：超过5分钟）
        if (datetime.datetime.now() - query_start).total_seconds() > 300:
            # 执行解锁操作（示例：终止事务）
            cursor.execute(f"SELECT pg_terminate_backend({pid});")
            conn.commit()

            # 发送邮件通知解锁情况
            content = f'已解锁事务：\n事务ID：{pid}\n锁定时间：{query_start}\n查询语句：{query}'
            send_email(subject, content, email_receiver)

    # 关闭数据库连接
    cursor.close()
    conn.close()

# 发送邮件函数
def send_email(subject, content, receiver_email):
    message = MIMEText(content, 'plain', 'utf-8')
    message['From'] = Header(sender_email)
    message['To'] = Header(receiver_email)
    message['Subject'] = Header(subject)

    try:
        smtp_obj = smtplib.SMTP(smtp_server, smtp_port)
        smtp_obj.starttls()
        smtp_obj.login(smtp_username, smtp_password)
        smtp_obj.sendmail(sender_email, receiver_email, message.as_string())
        smtp_obj.quit()
        print('邮件已发送！')
    except smtplib.SMTPException:
        print('邮件发送失败！')

# 主函数
def main():
    # 数据库连接信息
    db_host = 'your_db_host'
    db_port = 5432
    db_name = 'your_db_name'
    db_user = 'your_db_user'
    db_password = 'your_db_password'

    # 邮件接收人
    email_receiver = 'your_receiver_email@example.com'

    # 启动监控
    monitor_database_locks(db_host, db_port, db_name, db_user, db_password, email_receiver)

if __name__ == '__main__':
    main()

请注意，您需要根据您的实际情况修改代码中的占位符 `'your_smtp_server'`，`'your_smtp_username'`，`'your_smtp_password'`，`'your_sender_email@example.com'`，`'your_db_host'`，`'your_db_name'`，`'your_db_user'`，`'your_db_password'` 和 `'your_receiver_email@example.com'` 为实际的值。

此示例代码使用了 `psycopg2` 模块来连接和查询PostgreSQL数据库的锁定事务，并通过判断是否长时间被锁定来进行解锁操作。解锁操作示例中使用了 `pg_terminate_backend()` 函数来终止事务。同时，在解锁操作后会发送邮件通知解锁情况。

希望对您有帮助！如有任何问题，请随时提问。