Using an SQL Anywhere database (v12.0.1) and have 2-3 open connections. When one of the connections executes a COMMIT, other connections (which are performing reads on unrelated tables) are blocked until the commit completes.
In fact, even opening an Interactive SQL session to the database and executing command such as CALL sa_conn_info( ); blocks the client until the commit on the other connection completes. This is making it quite difficult to debug the issue as the whole database seems to be blocked until the commit completes.
Configuration parameters are set to their default values. Some key parameters that I have checked are:
isolation level = 0 cooperative_commits = On cooperative_commit_timeout = 250ms delayed_commits = Off
Does anybody have any idea of any database configuration options or anything else which may be causing this issue?
Thanks for the comments/suggestions from everybody! It turns out that the solution was very simple.
The problem was that the database was not using a transaction log. As I understand, this results in a checkpoint being performed on each commit. In this particular case, it had a very severe performance impact (commits were sometimes taking up to 60 seconds to complete).
In our case, we are using the DBTools C API to create (an embedded) database and a transaction log is not used by default unless explicitly specified.
answered 31 Jul '13, 01:22
answered 23 Jul '14, 15:11