SAP SQL Anywhere and its GUI Sybase Central (now SQL Central) have been part of my 5 most important tools for 4 years now. A few times, I noticed some oddities, but it wasn't until yesterday that I realized what was going on.

First, Interactive SQL starts transactions which are not automatically committed. By default, commits happen when closing or disconnecting (see the SQL Anywhere tab in Options). There is no warning to that effect. Second, the default SQL Anywhere isolation level for database transactions (at least in versions 11 to 17) is 0, "read uncommitted": http://dcx.sap.com/index.html#sqla170/en/html/3bf0ef526c5f1014b372935839b121fc.html Which means that the changes from uncommitted transactions are all visible, from different sessions and even from different users. Which is why I did not realize how Interactive SQL behaved before now.

So far, this may be surprising, but perhaps nothing incorrect. Unfortunately, this comes with a ultimate surprise. When you execute a query in Interactive SQL in these default configurations, the transaction started is not committed. And after some time, it appears that the transaction is quietly cancelled, automatically. I reproduced this at the first attempt. If the abortion was indeed triggered by a timeout, I do not know how long the delay is, but it is inferior to 16 hours. The Interactive SQL window was still open, there was no popup, nor any indication that the transaction had expired. The Interactive SQL closed normally, without indicating that the transaction had timed out.

This happened with Sybase Central 16 and SQL Anywhere 11. Commit after every statement will workaround this, but will not cause automatic commits when modifying with the results table (either modifying cells or deleting rows).

Transactions are great, but when implemented properly. Thinking about all the manipulations which we must have lost due to this bug leaves a taste which is quite bitter, and certainly not ACID.