skip to content

In JMeter's JDBC Request, what do the Commit, Rollback and AutoCommit(false) query types do?

level: seniorimportance: nice to knowfreq 31%

answer

  1. Four tags run no SQL at all
  2. They only change connection state
  3. The chain spans several samplers
  4. It depends on owning your connection

basics

~10 s

They ignore the SQL Query field completely and only change the connection's state. You place them as separate JDBC Request samplers around other samplers to wrap several statements in one transaction.

solid answer

~1 min

`Query Type` offers nine tags, and four of them — `Commit`, `Rollback`, `AutoCommit(false)` and `AutoCommit(true)` — are special: the manual notes that they ignore the given SQL statement and change the state of the connection only. Their response data is just the name of the operation. You use them by chaining samplers: an `AutoCommit(false)` request, then the statements, then a `Commit` or a `Rollback`. That works across samplers because JMeter configures its DBCP pool so that returning a connection neither commits nor rolls back what is open on it. Two settings on the JDBC Connection Configuration have to line up for the transaction to survive the gap. `Max Number of Connections` must be `0`, so the thread owns a pool of exactly one connection and every borrow gets the same physical connection back. And **Auto Commit must be unticked** — JMeter passes that checkbox to DBCP as the pool's `defaultAutoCommit`, and DBCP re-applies it on *every* borrow, so with the shipped ticked default the next sampler's borrow flips auto-commit back on and commits what the chain had open. With a shared pool the next borrow may hand the thread a different connection, and the open transaction is left on the wrong one.

code

text · 9 lines
text
Thread Group
  JDBC Request  Query Type: AutoCommit(false)   SQL Query: (ignored)
  JDBC Request  Query Type: Prepared Update Statement
                SQL Query : insert into ledger(acct, amount) values (?, ?)
                Parameter values: ${acct},${amount}
                Parameter types : INTEGER,DECIMAL
  JDBC Request  Query Type: Update Statement
                SQL Query : update balances set amount = amount - 10 where acct = 42
  JDBC Request  Query Type: Commit               SQL Query: (ignored)

go deeper

for a junior

Recall that the Query Type list contains more than select and update, and that Commit, Rollback and the AutoCommit tags act on the connection rather than running the SQL you typed.

for a middle

Explain the four-sampler chain and why each step is a separate row in the results file, and distinguish the query type from the Auto Commit checkbox on the connection configuration.

for a senior

Show that you check Max Number of Connections before building such a chain, and that you know a failed middle sampler can leave a transaction open until the connection closes at the end of the test.

for a principal

Decide when a plan may model a multi-statement transaction at all, given the extra samplers, borrows and result rows each iteration then costs, and what the team does about plans that write to a shared database.

Four of the `Query Type` tags on a JMeter JDBC Request do not run SQL at all. ## The nine query types The drop-down offers `Select Statement`, `Update Statement`, `Callable Statement`, `Prepared Select Statement`, `Prepared Update Statement`, `Commit`, `Rollback`, `AutoCommit(false)` and `AutoCommit(true)`, plus an *Edit* entry that lets you type a variable reference which must evaluate to one of those names. `Update Statement` covers inserts and deletes as well as updates — there is no separate tag for them. The last four are the state-changing ones. They ignore whatever sits in `SQL Query`, act on the borrowed connection, and return the operation's own name as the sample's response data. An `Update Statement`, by contrast, returns a body of the form `N updates`. ## Building a transaction across samplers The idiom is a chain of samplers on one thread: 1. **`AutoCommit(false)`** — turns auto-commit off on this thread's connection. 2. One or more **`Update Statement`** or **`Prepared Update Statement`** requests carrying the real work. 3. **`Commit`** — or **`Rollback`** if the plan is meant to leave no trace. 4. Optionally **`AutoCommit(true)`** to restore the connection's normal state before the next iteration. Each step is its own JDBC Request and so its own row in the results file, which is the point: you can see the cost of the commit separately from the cost of the statements. ## Why it survives the gap between samplers Every JDBC Request borrows a connection at the start of the sample and closes it at the end, which returns it to the pool. That would normally end an open transaction, and JMeter takes two explicit steps so it does not: the pool it builds is configured neither to auto-commit on return nor to roll back on return. The connection goes back to the pool with the transaction still open, and the next sampler on the same thread picks it up where it was left. That covers the return. The borrow is the other half: DBCP resets the connection's auto-commit to the pool's default each time it hands it out, so `Auto Commit` on the config element must be unticked as well — otherwise the `AutoCommit(false)` sampler's effect is erased at the very next borrow. "The same thread" is doing the load-bearing work in that sentence, and it depends on `Max Number of Connections`: | `Max Number of Connections` | What the next sampler borrows | |---|---| | `0` (the default) | the thread's own pool of one — always the same physical connection | | a positive N | whatever the shared pool hands out next, possibly another thread's connection | So the transaction chain is dependable with `0` and an unticked `Auto Commit`, and unreliable with a shared pool. This is one of the concrete reasons the manual recommends zero in most cases. ## Things that go wrong - **A leftover `AutoCommit(false)`.** With per-thread connections and `Auto Commit` unticked, the setting persists for the rest of the run on that thread, so a chain that ends without `Commit` or `AutoCommit(true)` leaves later iterations inside an open transaction. - **An abandoned transaction.** If a sampler in the middle fails and the plan moves on, the `Commit` may never run and the rows stay locked at the database until the connection is closed at the end of the test. - **Expecting the SQL to run.** Text left in the `SQL Query` field of a `Commit` sampler is not executed and not reported; the sample looks successful because the commit succeeded. - **Confusing the field with the config element's checkbox.** `Auto Commit` on the JDBC Connection Configuration is the pool's default, and DBCP re-imposes it on every borrow; the `AutoCommit(false)` query type changes one connection mid-run and only survives the next borrow if the checkbox agrees. ## When to reach for them These tags exist for plans whose unit of work is genuinely a multi-statement transaction and where you want the commit measured on its own. If the unit of work is a single statement, leave `Auto Commit` on at the config element and skip the chain entirely — four samplers where one would do is four rows per iteration in the results file and three more connection borrows.

  • Why does an open transaction survive between two JDBC Request samplers?
    Because each sampler returns its connection to the pool at the end of the sample, and JMeter builds that pool so it neither auto-commits nor rolls back on return. The connection goes back with the transaction still open. With `Max Number of Connections` at 0 the thread borrows the same single connection again, and with `Auto Commit` unticked DBCP leaves that connection's auto-commit alone on the borrow, so the next sampler continues the same transaction.
  • What does the Edit entry in the Query Type drop-down let you do?
    It lets you supply a value instead of picking a tag, and the manual states it should be a variable reference that evaluates to one of the listed query types. It is how a plan chooses between, say, a select and an update from a property or a variable, rather than duplicating the sampler.

saying these in an interview costs you the question

  • Expects the SQL Query field to run on a Commit sampler
  • Chains a transaction over a shared connection pool
  • Confuses the config element's Auto Commit with the query type
  • Leaves AutoCommit(false) set for the rest of the run
  • Assumes Update Statement has no insert or delete equivalent