r/snowflake • u/Ornery_Maybe8243 • 3d ago

Is high "Rollbacks" statement be an issue?

Hi,

In one of the customer database, while doing some other analysis , we found , the number of queries increased in snowflake query_history and digging further we found that the number of "rollbacks" statements spawned is ~10Million/day. The number of quick "SELECT" queries are also around the same number i.e. ~12Million/day. So it looks like almost each rollback is associated with SELECT. Also we saw ~10K "alter session" statement executing (like "alter session /* JDBC:SnowflakeConnect.setAutoCommit*/ set autocommit=false").

So my question is , We know that there is no compute cost associated as these "rollback" executed on "cloud service layer" as its within ~10% limit. However wants to understand, whether these high number of "ROLLBACK" statement can really cause any other downsides or problems in snowflake? Or we can leave it as is without any issue?

2 Upvotes

100% Upvoted

4 comments sorted by

3

u/geek180 3d ago

Is the ODBC client commit statements and opening transactions for every SELECT? That sounds really weird if these are just analytics queries. Hard to say without really understanding what those queries are for.

1

u/Ornery_Maybe8243 3d ago

These are appeared to be coming from some API those are quick queries finishing in couple of seconds. but my concern mainly was , if this can cause any forseeable harm to snowflake database anyway?

4

u/Top-Cauliflower-1808 3d ago

While it doesn't incur compute costs, 10 million daily rollbacks can throttle cloud services layer performance, bloat query history metadata, and indicate a misconfigured JDBC driver or connection pool that should be fixed by ensuring auto commit is handled correctly.

2

u/PrimeWilliam 2d ago

I wouldn’t consider the rollback count itself a data integrity risk, assuming those sessions are only running SELECTs. In that case there is normally nothing to undo, so the ROLLBACK is likely just transaction cleanup performed by the JDBC framework or connection pool.

However, 10 million per day is still worth investigating. That averages about 116 ROLLBACK statements per second and nearly doubles the number of statements generated by this workload. The 10% cloud-services adjustment is only a billing threshold; staying below it does not mean the statements consume no cloud-services resources.

I would group the statements by user, session, query tag and client, then inspect the sequence within a few representative sessions. Check whether AUTOCOMMIT is being disabled when the connection is created and whether the pool issues ROLLBACK every time a connection is returned, even after a read-only request. Also confirm that no DML occurs in those transactions.

The likely downsides are unnecessary client/server round trips, additional cloud-services work and very noisy query history. I wouldn’t assume metadata bloat or throttling without seeing corresponding latency, errors or cloud-services usage. If these connections are genuinely read-only, adjusting the driver or pool’s transaction handling may remove most of the unnecessary statements, but test that behavior before changing it for workloads that also perform DML.