Consider limiting iceberg client pool size for default sqlite backend

[CrawX]

Hi team,

thanks for this very useful project. I've been having good success testing various things with iceberg using this catalog.

Recently, I've been getting 500s under somewhat heavy load:

2023-08-10T14:44:52.707 ERROR [org.apache.iceberg.rest.RESTCatalogServlet] - Error processing REST request
org.apache.iceberg.exceptions.RESTException: Unhandled error: ErrorResponse(code=500, type=UncheckedSQLException, message=Unknown failure)

I was able to find the following in the stack trace:

Caused by: org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked)
 at org.sqlite.core.DB.newSQLException(DB.java:1179)
 at org.sqlite.core.DB.newSQLException(DB.java:1190)
 at org.sqlite.core.DB.execute(DB.java:985)
 at org.sqlite.core.DB.executeUpdate(DB.java:1054)
 at org.sqlite.jdbc3.JDBC3PreparedStatement.lambda$executeLargeUpdate$2(JDBC3PreparedStatement.java:118)                                                                             iceberg-rest-s3-aws  |  at org.sqlite.jdbc3.JDBC3Statement.withConnectionTimeout(JDBC3Statement.java:454)
 at org.sqlite.jdbc3.JDBC3PreparedStatement.executeLargeUpdate(JDBC3PreparedStatement.java:117)
 at org.sqlite.jdbc3.JDBC3PreparedStatement.executeUpdate(JDBC3PreparedStatement.java:99)
 at org.apache.iceberg.jdbc.JdbcTableOperations.lambda$updateTable$0(JdbcTableOperations.java:164)
 at org.apache.iceberg.ClientPoolImpl.run(ClientPoolImpl.java:58)
 at org.apache.iceberg.ClientPoolImpl.run(ClientPoolImpl.java:51)
 at org.apache.iceberg.jdbc.JdbcTableOperations.updateTable(JdbcTableOperations.java:153)
 at org.apache.iceberg.jdbc.JdbcTableOperations.doCommit(JdbcTableOperations.java:115)
 ... 43 more
 at org.apache.iceberg.rest.RESTCatalogAdapter.execute(RESTCatalogAdapter.java:401)
 at org.apache.iceberg.rest.RESTCatalogServlet.execute(RESTCatalogServlet.java:100)
 at org.apache.iceberg.rest.RESTCatalogServlet.doPost(RESTCatalogServlet.java:78)

My guess was that there is some sort of connection pooling of JDBC connections going on and sqlite doesn't play nice with that. I checked the source of JdbcClientPool and saw that the default pool size is 2, this increased my suspicions.
I set it to 1 using CATALOG_CLIENTS=1 and haven't had these errors since.

I propose to set this to 1 if the default sqlite JDBC connection string is used. I'd be happy to draft a PR if someone else can confirm my suspicions.

Cheers,
Johannes

[jonashaag]

Thank you, same problem here, setting CATALOG_CLIENTS=1 fixed it for me