on 2013 Jul 24 11:18 PM
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
THANK YOU for posting the solution! It should be a real head-slapping moment for people (like me) who didn't suggest that answer before, because all the so-called experts should remember that running without a transaction log makes commits slowwwww, as in point 19 here:
http://sqlanywhere.blogspot.ca/2008/04/how-to-make-sql-anywhere-slow.html
The DBTools behavior sounds like a bug because the docs for a_create_db say this... http://dcx.sybase.com/index.html#sa160/en/dbprogramming/programming-sadbtoolscpp-a-create-db-str.htm...
logname const char * New transaction log name. Equivalent to dbinit -t option.
That implies it should work like dbinit, where the default is to create a transaction log. With dbinit you have to specify -n to suppress the log, and that's not even possible with a_create_db.
If it's not a bug, it certainly violates The Watcom Rule: "Watcom does things the way they should be done."
Me? No, I am responding to Yukiko's statement... according to the API docs there is no way to say whether the default log file is created or not. There is only a field for specifying the explicit log file spec. I assume Yukiko left that field empty (a safe assumption IMO). He is reporting that's the reason the log file was not created.
Perhaps it is worth an independent test ... hint ... hint ... Volker 🙂
I've tested this and can confirm calling a_create_db with a NULL or empty string for logname will result in a database without a transaction log. It seems that dbinit performs this logic before making the API call.
The current documentation certainly makes it look like 'logfile' is the exact equivalent of the -t option on dbinit. I’ve added a comment to the dcx page for now, but I’ll see if we can get the page updated to be more descriptive of the actual behavior in the future.
Thanks for the follow up from everybody! As concluded, the documentation for a_create_db is not clear and should be updated. I suppose there should also be the possibility not to have a transaction log which is probably what was intended (but not documented) with the NULL/empty value.
I checked and Mikel is correct: a NULL or empty logname creates a database without a log. Note that there is no field in a_create_db that specifies whether you want a log or not so the desired behaviour is inferred from the value of the logname field. Some magic value or values needs to mean "no log".
Personally, I generally don't like magic values like these and in this case it also means that code for generating the default log name needs to be replicated outside of the dbtools API. If I had written the API, I might have had a separate boolean to indicate whether a log was wanted or not. I don't think it's worth changing the interface but the doc could be improved.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The "similar incident" is not similar at all; it talks about checkpoints which are completely different from commits.
User | Count |
---|---|
70 | |
10 | |
9 | |
6 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.