以下 Java 範例說明可能會發生的意外行為。這段程式碼會使用 Mutation API 將兩個資料列插入「Albums」中。程式碼片段接著呼叫 executeUpdate() 來更新新插入的資料列，並呼叫 executeQuery() 來讀取已更新的專輯。
staticvoidupdateMarketingBudget(DatabaseClientdbClient){dbClient.readWriteTransaction().run(newTransactionCallable<Void>(){@OverridepublicVoidrun(TransactionContexttransaction)throwsException{transaction.buffer(Mutation.newInsertBuilder("Albums").set("SingerId").to(1).set("AlbumId").to(1).set("AlbumTitle").to("Total Junk").set("MarketingBudget").to(800).build());transaction.buffer(Mutation.newInsertBuilder("Albums").set("SingerId").to(1).set("AlbumId").to(2).set("AlbumTitle").to("Go Go Go").set("MarketingBudget").to(200).build());// This UPDATE will not include the Albums inserted above.Stringsql="UPDATE Albums SET MarketingBudget = MarketingBudget * 2"+" WHERE SingerId = 1";longrowCount=transaction.executeUpdate(Statement.of(sql));System.out.printf("%d records updated.\n",rowCount);// Read a newly updated record.sql="SELECT SingerId, AlbumId, AlbumTitle FROM Albums"+" WHERE SingerId = 1 AND MarketingBudget < 1000";ResultSetresultSet=transaction.executeQuery(Statement.of(sql));while(resultSet.next()){System.out.printf("%s %s\n",resultSet.getString("FirstName"),resultSet.getString("LastName"));}returnnull;}});}
[[["容易理解","easyToUnderstand","thumb-up"],["確實解決了我的問題","solvedMyProblem","thumb-up"],["其他","otherUp","thumb-up"]],[["難以理解","hardToUnderstand","thumb-down"],["資訊或程式碼範例有誤","incorrectInformationOrSampleCode","thumb-down"],["缺少我需要的資訊/範例","missingTheInformationSamplesINeed","thumb-down"],["翻譯問題","translationIssue","thumb-down"],["其他","otherDown","thumb-down"]],["上次更新時間：2025-09-05 (世界標準時間)。"],[],[],null,["# Compare DML and Mutations\n\nData Manipulation\nLanguage (DML) and Mutations are two APIs in Spanner that you can use\nto modify data. Each offer similar data manipulation features. This page\ncompares both approaches.\n\nWhat is Data Manipulation Language (DML)?\n-----------------------------------------\n\nThe Data Manipulation Language (DML) in Spanner lets you\nmanipulate data in your database tables using `INSERT`, `UPDATE`, and `DELETE`\nstatements. You can run DML statements using the\n[client libraries](/spanner/docs/reference/libraries), the\n[Google Cloud console](/spanner/docs/create-query-database-console#run_a_query), and [gcloud spanner](/sdk/gcloud/reference/spanner#execute_sql_statements).\n\nSpanner offers the following two implementations of DML execution, each with different\nproperties.\n\n- **Standard DML** - suitable for standard [Online Transaction Processing (OLTP)](https://en.wikipedia.org/wiki/Online_transaction_processing)\n workloads.\n\n For more information, including code samples, see [Using DML](/spanner/docs/dml-tasks#using-dml)\n- **Partitioned DML** - designed for bulk updates and deletes as in the following\n examples.\n\n - Periodic cleanup and garbage collection. Examples are deleting old rows or\n setting columns to NULL.\n\n - Backfilling new columns with default values. An example is using an UPDATE\n statement to set a new column's value to False where it is NULL.\n\n For more information, including code samples, see [Using Partitioned DML](/spanner/docs/dml-tasks#partitioned-dml).\n\n You can use batch writes for a large number of write operations without read\n operations that don't require atomic transactions. For more information,\n see [Modify data using batch writes](/spanner/docs/batch-write).\n\nWhat are mutations?\n-------------------\n\nA mutation represents a sequence of inserts, updates, and deletes that Spanner applies atomically to different rows and tables in a database.\nYou can include operations that apply to different rows, or different tables, in\na mutation. After you define one or more mutations that contain one or more\nwrites, you must apply the mutation to commit the write(s). Each change is\napplied in the order in which they were added to the mutation.\n\nFor more information, including code samples, see\n[Inserting, updating, and deleting data using mutations](/spanner/docs/modify-mutation-api).\n\nFeature comparison between DML and mutations\n--------------------------------------------\n\nThe following table summarizes DML and mutation support of common database\noperation and features.\n\nDML and mutations diverge in their support for the following features:\n\n- **Read Your Writes**: Reading uncommitted results within an active\n transaction. Changes you make using DML statements are visible to\n subsequent statements in the same transaction. This is\n different from using mutations, where changes are not visible in any reads\n (including reads done in the same transaction) until the transaction commits.\n This is because mutations in a transaction are buffered client-side (locally)\n and sent to the server as part of the commit operation. As a result, mutations\n in the commit request are not visible to SQL or DML statements within the same\n transaction.\n\n- **Constraint Checking**: Spanner checks constraints after every\n DML statement. This is different from using mutations, where Spanner\n buffers mutations in the client until commit and checks constraints at commit\n time. Evaluating constraints after each DML statement allows Spanner\n to guarantee that the data returned by a subsequent query in the same\n transaction returns data that is consistent with the schema.\n\n- **SQL Syntax**: DML provides a conventional way to manipulate data. You can\n reuse SQL skills to alter the data using the DML API.\n\nBest practice - avoid mixing DML and mutation in the same transaction\n---------------------------------------------------------------------\n\nIf a transaction contains both DML statements and mutations in the commit\nrequest, Spanner executes the DML statements before the mutations. To avoid\nhaving to account for the order of execution in your client library code, you\nshould use either DML statements or the mutations in a single transaction, but\nnot both.\n\nThe following Java example illustrates potentially surprising behavior. The code\ninserts two rows into Albums using the Mutation API. The snippet, then calls\n`executeUpdate()` to update the newly inserted rows and calls `executeQuery()`\nto read updated albums. \n\n static void updateMarketingBudget(DatabaseClient dbClient) {\n dbClient\n .readWriteTransaction()\n .run(\n new TransactionCallable\u003cVoid\u003e() {\n @Override\n public Void run(TransactionContext transaction) throws Exception {\n transaction.buffer(\n Mutation.newInsertBuilder(\"Albums\")\n .set(\"SingerId\")\n .to(1)\n .set(\"AlbumId\")\n .to(1)\n .set(\"AlbumTitle\")\n .to(\"Total Junk\")\n .set(\"MarketingBudget\")\n .to(800)\n .build());\n transaction.buffer(\n Mutation.newInsertBuilder(\"Albums\")\n .set(\"SingerId\")\n .to(1)\n .set(\"AlbumId\")\n .to(2)\n .set(\"AlbumTitle\")\n .to(\"Go Go Go\")\n .set(\"MarketingBudget\")\n .to(200)\n .build());\n\n // This UPDATE will not include the Albums inserted above.\n String sql =\n \"UPDATE Albums SET MarketingBudget = MarketingBudget * 2\"\n + \" WHERE SingerId = 1\";\n long rowCount = transaction.executeUpdate(Statement.of(sql));\n System.out.printf(\"%d records updated.\\n\", rowCount);\n\n // Read a newly updated record.\n sql =\n \"SELECT SingerId, AlbumId, AlbumTitle FROM Albums\"\n + \" WHERE SingerId = 1 AND MarketingBudget \u003c 1000\";\n ResultSet resultSet =\n transaction.executeQuery(Statement.of(sql));\n while (resultSet.next()) {\n System.out.printf(\n \"%s %s\\n\",\n resultSet.getString(\"FirstName\"),\n resultSet.getString(\"LastName\"));\n }\n return null;\n }\n });\n }\n\nIf you were to execute this code, you'd see *0 records updated*. Why? This\nhappens because the changes we made using Mutations are not visible to\nsubsequent statements until the transaction commits. Ideally, we should have\nbuffered writes only at the very end of the transaction.\n\nWhat's next?\n------------\n\n- Learn how to modify data [Using DML](/spanner/docs/dml-tasks#using-dml).\n\n- Learn how to modify data [Using mutations](/spanner/docs/modify-mutation-api).\n\n- To find the mutation count for a transaction, see\n [Retrieving commit statistics for a transaction](/spanner/docs/commit-statistics).\n\n- Learn about [Data Manipulation Language (DML) best practices](/spanner/docs/dml-best-practices)."]]
