Under what scenarios we use SERIALIZABLE Isolation level? I have seen some answers on website that says, when you want transaction to be completely isolated, we usually go for this.
在什么情况下我们使用SERIALIZABLE隔离级别？我在网站上看到了一些答案，当你想要将交易完全隔离时，我们通常会这样做。
What i would like to know from your own experience is that, when you've used this in your projects/or you've seen this to be used in other projects and what was the specific requirement that was not getting fulfilled by other isolation levels?
根据您自己的经验，我想知道的是，当您在项目中使用它时，或者您已经看到将其用于其他项目时，其他隔离级别未满足的具体要求是什么？
2 个解决方案
#1
7
SERIALIZABLE is useful when you build complex reports which require several queries.
当您构建需要多个查询的复杂报表时，SERIALIZABLE非常有用。
In READ COMMITTED or REPEATABLE READ the queries could see the changes made between the transaction started and the moment they ran.
在READ COMMITTED或REPEATABLE READ中，查询可以看到在事务开始和运行之间所做的更改。
pg_dump, the PostgreSQL dump utility, starts it job with issuing SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, to guarantee that the state of database being dumped would be that of the moment the utility was run, and the subsequent changes to the database could not interfere with the dump.
pg_dump，PostgreSQL转储实用程序，通过发出SET TRANSACTION ISOLATION LEVEL SERIALIZABLE启动它的工作，以保证转储的数据库的状态将是实用程序运行时的状态，并且对数据库的后续更改不会干扰倾倒。
#2
2
SERIALIZABLE transactions are useful if you want a simple proof of correctness in concurrent loads. Since the definition of the serializable transaction isolation level in the SQL standard (since SQL-92) is that the behavior of any concurrent set of serializable transactions must be consistent with some serial (one-at-a-time) order of execution, any transaction which can be shown to do the right thing when it is run alone, will do the right thing as part of any mix of serializable transactions.
如果要在并发加载中简单地证明正确性，SERIALIZABLE事务很有用。由于SQL标准（自SQL-92）中可序列化事务隔离级别的定义是任何并发可序列化事务集的行为必须与某些串行（一次一个）执行顺序一致，可以在单独运行时显示正确的事务的事务将作为任何可序列化事务组合的一部分做正确的事情。
There is a cost to this protection -- transactions must be blocked or rolled back for retry at times to ensure serializable transaction isolation, and information must be tracked to determine when it is necessary to take such actions. In some development environments, with a small number of transaction types and a small number of developers, it is often more cost-effective to use a less strict isolation level and manage race conditions explicitly in application code. Once you have dozens of programmers working against a schema with hundreds of tables and tens of thousands of transaction types, the cost of determining where race conditions exist can become overwhelming at less strict isolation levels and it generally becomes more cost-effective to use serializable transactions.
这种保护需要成本 - 必须阻止或回滚事务以便重试以确保可序列化的事务隔离，并且必须跟踪信息以确定何时需要采取此类操作。在某些开发环境中，使用少量事务类型和少量开发人员，使用不太严格的隔离级别并在应用程序代码中明确管理竞争条件通常更具成本效益。一旦有数十名程序员在使用数百个表和数万种事务类型的模式上工作，确定竞争条件存在的成本在不太严格的隔离级别下会变得无法控制，并且使用可序列化事务通常会更具成本效益。
Currently, the most frequently implemented method of providing serializable transactions is strict two-phase locking (S2PL), which relies on blocking locks held until the end of each transaction, and deadlock detection with rollbacks to break deadlocks. In loads with very little write contention optimistic concurrency control (OCC) can be used. It tracks a "read set" during the course of the transaction and rolls back if any other transaction modifies the read set. Some database products refer to snapshot isolation as serializable, although it does not actually provide the guarantees required by the SQL standard. A new technique called Serializable Snapshot Isolation (SerializableSI or SSI), was first described in an academic paper presented at the 2008 ACM SIGMOD and is used in PostgreSQL version 9.1 and later. It uses snapshot isolation plus tracking of read-write dependency patterns to determine when a transaction must be canceled. There are other techniques which are seen less often in production. Each of these has its own set of advantages and disadvantages, providing a different break-even point for a question like this.
目前，提供可序列化事务的最常用的方法是严格的两阶段锁定（S2PL），它依赖于阻塞直到每个事务结束时的锁，以及带有回滚的死锁检测来打破死锁。在具有非常少的写争用的负载中，可以使用乐观并发控制（OCC）。它在事务过程中跟踪“读取集”，并在任何其他事务修改读取集时回滚。某些数据库产品将快照隔离称为可序列化，但它实际上并未提供SQL标准所要求的保证。一种称为可序列化快照隔离（SerializableSI或SSI）的新技术首先在2008 ACM SIGMOD上发表的学术论文中描述，并在PostgreSQL 9.1及更高版本中使用。它使用快照隔离和跟踪读写依赖关系模式来确定何时必须取消事务。还有其他技术在生产中较少见。每个都有自己的优点和缺点，为这样的问题提供不同的收支平衡点。
#1
7
SERIALIZABLE is useful when you build complex reports which require several queries.
当您构建需要多个查询的复杂报表时，SERIALIZABLE非常有用。
In READ COMMITTED or REPEATABLE READ the queries could see the changes made between the transaction started and the moment they ran.
在READ COMMITTED或REPEATABLE READ中，查询可以看到在事务开始和运行之间所做的更改。
pg_dump, the PostgreSQL dump utility, starts it job with issuing SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, to guarantee that the state of database being dumped would be that of the moment the utility was run, and the subsequent changes to the database could not interfere with the dump.
pg_dump，PostgreSQL转储实用程序，通过发出SET TRANSACTION ISOLATION LEVEL SERIALIZABLE启动它的工作，以保证转储的数据库的状态将是实用程序运行时的状态，并且对数据库的后续更改不会干扰倾倒。
#2
2
SERIALIZABLE transactions are useful if you want a simple proof of correctness in concurrent loads. Since the definition of the serializable transaction isolation level in the SQL standard (since SQL-92) is that the behavior of any concurrent set of serializable transactions must be consistent with some serial (one-at-a-time) order of execution, any transaction which can be shown to do the right thing when it is run alone, will do the right thing as part of any mix of serializable transactions.
如果要在并发加载中简单地证明正确性，SERIALIZABLE事务很有用。由于SQL标准（自SQL-92）中可序列化事务隔离级别的定义是任何并发可序列化事务集的行为必须与某些串行（一次一个）执行顺序一致，可以在单独运行时显示正确的事务的事务将作为任何可序列化事务组合的一部分做正确的事情。
There is a cost to this protection -- transactions must be blocked or rolled back for retry at times to ensure serializable transaction isolation, and information must be tracked to determine when it is necessary to take such actions. In some development environments, with a small number of transaction types and a small number of developers, it is often more cost-effective to use a less strict isolation level and manage race conditions explicitly in application code. Once you have dozens of programmers working against a schema with hundreds of tables and tens of thousands of transaction types, the cost of determining where race conditions exist can become overwhelming at less strict isolation levels and it generally becomes more cost-effective to use serializable transactions.
这种保护需要成本 - 必须阻止或回滚事务以便重试以确保可序列化的事务隔离，并且必须跟踪信息以确定何时需要采取此类操作。在某些开发环境中，使用少量事务类型和少量开发人员，使用不太严格的隔离级别并在应用程序代码中明确管理竞争条件通常更具成本效益。一旦有数十名程序员在使用数百个表和数万种事务类型的模式上工作，确定竞争条件存在的成本在不太严格的隔离级别下会变得无法控制，并且使用可序列化事务通常会更具成本效益。
Currently, the most frequently implemented method of providing serializable transactions is strict two-phase locking (S2PL), which relies on blocking locks held until the end of each transaction, and deadlock detection with rollbacks to break deadlocks. In loads with very little write contention optimistic concurrency control (OCC) can be used. It tracks a "read set" during the course of the transaction and rolls back if any other transaction modifies the read set. Some database products refer to snapshot isolation as serializable, although it does not actually provide the guarantees required by the SQL standard. A new technique called Serializable Snapshot Isolation (SerializableSI or SSI), was first described in an academic paper presented at the 2008 ACM SIGMOD and is used in PostgreSQL version 9.1 and later. It uses snapshot isolation plus tracking of read-write dependency patterns to determine when a transaction must be canceled. There are other techniques which are seen less often in production. Each of these has its own set of advantages and disadvantages, providing a different break-even point for a question like this.
目前，提供可序列化事务的最常用的方法是严格的两阶段锁定（S2PL），它依赖于阻塞直到每个事务结束时的锁，以及带有回滚的死锁检测来打破死锁。在具有非常少的写争用的负载中，可以使用乐观并发控制（OCC）。它在事务过程中跟踪“读取集”，并在任何其他事务修改读取集时回滚。某些数据库产品将快照隔离称为可序列化，但它实际上并未提供SQL标准所要求的保证。一种称为可序列化快照隔离（SerializableSI或SSI）的新技术首先在2008 ACM SIGMOD上发表的学术论文中描述，并在PostgreSQL 9.1及更高版本中使用。它使用快照隔离和跟踪读写依赖关系模式来确定何时必须取消事务。还有其他技术在生产中较少见。每个都有自己的优点和缺点，为这样的问题提供不同的收支平衡点。