Entender o MVCC (Controle de Simultaneidade de Várias Versões) e os instantâneos

A maioria dos sistemas de gerenciamento de banco de dados (DBMS) usa bloqueios para impor o controle de simultaneidade, mas o PostgreSQL complementa o bloqueio com uma abordagem alternativa. O PostgreSQL usa um sistema chamado MVCC (Controle de Simultaneidade de Várias Versões) que permite que várias versões da mesma linha existam para melhorar a simultaneidade. Com esse sistema, cada consulta vê um instantâneo anterior dos dados que é transacionalmente consistente. O MVCC garante que uma transação que está lendo dados não bloqueie uma transação que esteja gravando dados e vice-versa.

Por exemplo, a Conexão A está executando uma consulta que verifica todas as linhas de uma tabela. Ao mesmo tempo, a Conexão B está executando uma consulta que atualiza algumas das linhas. O MVCC permite que ambas as consultas são executadas ao mesmo tempo criando outra versão das linhas afetadas. Dessa forma, a Conexão B pode executar as atualizações sem afetar a Conexão A. Esse processo é realizado porque cada versão de linha tem um valor xmin para a transação visível desde e um valor xmax para a transação visível até. Com o MVCC, a Conexão A ignorará as modificações que ocorreram após o início da consulta Conexão A.

Transações

As transações em um DBMS são uma unidade atômica de trabalho, portanto, a transação é confirmada em sua totalidade ou não é confirmada em absoluto. As transações também são usadas para fins de concorrência. Com os níveis de isolamento, o efeito que uma transação pode ter em outras transações simultâneas pode ser definido.

Você inicia uma transação com BEGIN TRANSACTION ou START TRANSACTION. Você conclui uma transação com COMMIT, para salvar todas as alterações feitas na transação ou ROLLBACK, para desfazer as alterações feitas pela transação. Por exemplo:

BEGIN TRANSACTION;
    UPDATE production.workorder
        SET stockedqty=7
        WHERE workorderid=1;
COMMIT;