在redshift中处理锁 [英] handle locks in redshift

问题描述

我有一个Python脚本，可以在Redshift中执行多个sql脚本(一个接一个).这些sql脚本中的某些表可以多次查询.对于前.表t1可以在一个脚本中选择，也可以在另一脚本中删除/重新创建.整个过程在一个事务中运行.现在，有时，我会遇到死锁检测到的错误，并且整个事务都会回滚.如果表上有死锁，我想等待表被释放，然后重试sql执行.对于其他类型的错误，我想回滚事务.从文档看来，表锁直到事务结束才释放.我想实现所有数据更改或不进行任何数据更改(通过使用事务来完成)，但也想处理死锁.关于如何实现此目标的任何建议?

I have a python script that executes multiple sql scripts (one after another) in Redshift. Some of the tables in these sql scripts can be queried multiple times. For ex. Table t1 can be SELECTed in one script and can be dropped/recreated in another script. This whole process is running in one transaction. Now, sometimes, I am getting deadlock detected error and the whole transaction is rolled back. If there is a deadlock on a table, I would like to wait for the table to be released and then retry the sql execution. For other types of errors, I would like to rollback the transaction. From the documentation, it looks like the table lock isn't released until end of transaction. I would like to achieve all or no data changes (which is accomplished by using transaction) but also would like to handle deadlocks. Any suggestion on how this can be accomplished?

推荐答案

我将使用重试循环在一个事务中执行您要引用的所有SQL.以下是我用来处理并发问题和重试的逻辑(为简便起见，使用伪代码).我没有系统无限期地等待锁定被释放.取而代之的是，我通过在一段时间后重试来在应用程序中处理它.

I would execute all of the SQL you are referring to in one transaction with a retry loop. Below is the logic I use to handle concurrency issues and retry (pseudocode for brevity). I do not have the system wait indefinitely for the lock to be released. Instead I handle it in the application by retrying over time.

begin transaction
while not successful and count < 5
    try 
        execute sql
        commit
    except
        if error code is '40P01' or '55P03'
            # Deadlock or lock not available
            sleep a random time (200 ms to 1 sec) * number of retries
        else if error code is '40001' or '25P02'
            # "In failed sql transaction" or serialized transaction failure
            rollback
            sleep a random time (200 ms to 1 sec) * number of retries
            begin transaction
        else if error message is 'There is no active transaction'
            sleep a random time (200 ms to 1 sec) * number of retries
            begin transaction
    increment count

关键组件正在捕获每种错误类型 ，知道哪些情况需要回滚，并具有重试的指数补偿.

The key components are catching every type of error, knowing which cases require a rollback, and having an exponential backoff for retries.

这篇关于在redshift中处理锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！