Inserir, atualizar e excluir dados usando a linguagem de manipulação de dados (DML)

Nesta página, descrevemos como inserir, atualizar e excluir dados do Spanner usando instruções da linguagem de manipulação de dados (DML). É possível executar instruções DML usando as bibliotecas de cliente, o consoleGoogle Cloud e a ferramenta de linha de comando gcloud. É possível executar as instruções DML particionadas usando as bibliotecas de cliente e a ferramenta de linha de comando gcloud.

Para ver a referência completa da sintaxe de DML, consulte Sintaxe da linguagem de manipulação de dados para bancos de dados do dialeto GoogleSQL ou Linguagem de manipulação de dados do PostgreSQL para bancos de dados do dialeto PostgreSQL.

Usar DML

A DML é compatível com as instruções INSERT, UPDATE e DELETE no consoleGoogle Cloud , Google Cloud CLI e nas bibliotecas de cliente.

Bloqueio

As instruções DML são executadas dentro de transações de leitura e gravação. Quando o Spanner lê dados, ele adquire bloqueios de leitura compartilhados em partes limitadas dos intervalos de linha lidos por você. Especificamente, ele adquire esses bloqueios apenas nas colunas acessadas. Os bloqueios podem incluir dados que não satisfazem a condição de filtro da cláusula WHERE.

Quando o Spanner modifica dados usando instruções DML, ele adquire bloqueios exclusivos nos dados específicos que você está modificando. Além disso, ele adquire bloqueios compartilhados da mesma maneira que quando você lê dados. Se a solicitação incluir intervalos de linhas grandes ou uma tabela inteira, os bloqueios compartilhados poderão impedir que outras transações sejam concluídas em paralelo.

Para modificar os dados da forma mais eficiente possível, use uma cláusula WHERE que permita que o Spanner leia somente as linhas necessárias. É possível atingir essa meta com um filtro na chave principal ou na chave de um índice secundário. A cláusula WHERE limita o escopo dos bloqueios compartilhados e permite que o Spanner processe a atualização de maneira mais eficiente.

Por exemplo, suponha que um dos músicos da tabela Singers altere seu nome e você precise atualizar essa informação no banco de dados. É possível executar a instrução DML a seguir, mas ela força o Spanner a verificar a tabela inteira e adquire os bloqueios compartilhados que cobrem toda a tabela. Como resultado, o Spanner precisa ler mais dados do que o necessário, e as transações simultâneas não conseguem modificar os dados em paralelo:

-- ANTI-PATTERN: SENDING AN UPDATE WITHOUT THE PRIMARY KEY COLUMN
-- IN THE WHERE CLAUSE

UPDATE Singers SET FirstName = "Marcel"
WHERE FirstName = "Marc" AND LastName = "Richards";

Para tornar a atualização mais eficiente, inclua a coluna SingerId na cláusula WHERE. A coluna SingerId é a única coluna de chave primária da tabela Singers:

-- ANTI-PATTERN: SENDING AN UPDATE THAT MUST SCAN THE ENTIRE TABLE

UPDATE Singers SET FirstName = "Marcel"
WHERE FirstName = "Marc" AND LastName = "Richards"

Se não houver um índice em FirstName ou LastName, será necessário verificar toda a tabela para encontrar os cantores de destino. Se você não quiser adicionar um índice secundário para tornar a atualização mais eficiente, inclua a coluna SingerId na cláusula WHERE.

A coluna SingerId é a única coluna de chave primária da tabela Singers. Para encontrar, execute SELECT em uma transação separada de somente leitura antes da transação de atualização:

  SELECT SingerId
  FROM Singers
  WHERE FirstName = "Marc" AND LastName = "Richards"

  -- Recommended: Including a seekable filter in the where clause

  UPDATE Singers SET FirstName = "Marcel"
  WHERE SingerId = 1;

Simultaneidade

O Spanner executa sequencialmente todas as instruções SQL (SELECT, INSERT, UPDATE e DELETE) dentro de uma transação. Elas não são executadas simultaneamente. A única exceção é que o Spanner pode executar várias instruções SELECT simultaneamente porque elas são operações somente leitura.

Limites de transação

Uma transação que inclui declarações DML tem os mesmos limites que qualquer outra transação. Se você tiver alterações em grande escala, considere o uso de DML particionada.

• Se as instruções DML de uma transação resultarem em mais de 80.000 mutações, a instrução DML que faz a transação superar o limite vai retornar um erro BadUsage com uma mensagem sobre muitas mutações.

• Se as instruções DML de uma transação resultarem em uma transação maior que 100 MiB, a instrução DML que faz a transação superar o limite vai retornar um erro BadUsage com uma mensagem sobre a transação exceder o limite de tamanho.

Mutações realizadas usando DML não são retornadas ao cliente. Elas são mescladas na solicitação de confirmação quando ela é confirmada e contam para os limites de tamanho máximo. Mesmo que o tamanho da solicitação de confirmação enviada seja pequeno, a transação ainda poderá exceder o limite de tamanho permitido.

Executar instruções no console do Google Cloud

Siga as etapas a seguir para executar uma instrução DML no consoleGoogle Cloud .

1. Acesse a página Instâncias do Spanner.

   Acesse a página "Instâncias"

2. Selecione o projeto na lista suspensa na barra de ferramentas.

3. Clique no nome da instância que contém seu banco de dados para ir para a página Detalhes da instância.

4. Na guia Visão geral, clique no nome do seu banco de dados. A página Detalhes do banco de dados é exibida.

5. Clique em Spanner Studio.

6. Digite uma instrução DML. Por exemplo, a instrução a seguir adiciona uma nova linha à tabela Singers.

   INSERT Singers (SingerId, FirstName, LastName)
   VALUES (1, 'Marc', 'Richards')
   

7. Clique em Executar consulta. O console Google Cloud mostra o resultado.

Executar instruções com a Google Cloud CLI

Para executar instruções DML, use o comando gcloud spanner databases execute-sql. O exemplo a seguir adiciona uma nova linha à tabela Singers.

gcloud spanner databases execute-sql example-db --instance=test-instance \
    --sql="INSERT Singers (SingerId, FirstName, LastName) VALUES (1, 'Marc', 'Richards')"

Modificar dados usando a biblioteca de cliente

Para executar instruções DML com a biblioteca de cliente:

• Crie uma transação de leitura/gravação.
• Chame o método da biblioteca de cliente para a execução da DML e transmita a instrução DML.
• Use o valor de retorno do método de execução da DML para receber o número de linhas inseridas, atualizadas ou excluídas.

O exemplo de código a seguir insere uma nova linha na tabela Singers.

void DmlStandardInsert(google::cloud::spanner::Client client) {
  using ::google::cloud::StatusOr;
  namespace spanner = ::google::cloud::spanner;
  std::int64_t rows_inserted;
  auto commit_result = client.Commit(
      [&client, &rows_inserted](
          spanner::Transaction txn) -> StatusOr<spanner::Mutations> {
        auto insert = client.ExecuteDml(
            std::move(txn),
            spanner::SqlStatement(
                "INSERT INTO Singers (SingerId, FirstName, LastName)"
                "  VALUES (10, 'Virginia', 'Watson')"));
        if (!insert) return std::move(insert).status();
        rows_inserted = insert->RowsModified();
        return spanner::Mutations{};
      });
  if (!commit_result) throw std::move(commit_result).status();
  std::cout << "Rows inserted: " << rows_inserted;
  std::cout << "Insert was successful [spanner_dml_standard_insert]\n";
}