Bug 36885 - SQLite Transaction Timeouts on Xamarin.iOS when running on the device
Summary: SQLite Transaction Timeouts on Xamarin.iOS when running on the device
Status: RESOLVED ANSWERED
Alias: None
Product: iOS
Classification: Xamarin
Component: General ()
Version: XI 9.2
Hardware: Macintosh Mac OS
: Normal normal
Target Milestone: (C7)
Assignee: Rolf Bjarne Kvinge [MSFT]
URL:
Depends on:
Blocks:
 
Reported: 2015-12-11 11:04 UTC by Prashant Cholachagudda
Modified: 2015-12-16 10:53 UTC (History)
4 users (show)

Tags:
Is this bug a regression?: ---
Last known good build:

Notice (2018-05-24): bugzilla.xamarin.com is now in read-only mode.

Please join us on Visual Studio Developer Community and in the Xamarin and Mono organizations on GitHub to continue tracking issues. Bugzilla will remain available for reference in read-only mode. We will continue to work on open Bugzilla bugs, copy them to the new locations as needed for follow-up, and add the new items under Related Links.

Our sincere thanks to everyone who has contributed on this bug tracker over the years. Thanks also for your understanding as we make these adjustments and improvements for the future.


Please create a new report on Developer Community or GitHub with your current version information, steps to reproduce, and relevant error messages or log files if you are hitting an issue that looks similar to this resolved bug and you do not yet see a matching new report.

Related Links:
Status:
RESOLVED ANSWERED

Description Prashant Cholachagudda 2015-12-11 11:04:22 UTC
# Steps to reproduce

Download the sample code from - https://github.com/tomhamming/xamarin-bundle-sqlite

Open a database connection and configure it as WAL journaling with a 10 second (or so) transaction timeout.
Start a transaction and do some nontrivial work (simulated as a sleep in this case)
On another thread, open another connection with the same configuration to the same DB file. Open a transaction.

# Expected behavior
Expected behaviour is that the second transaction should block until the first one finishes, or return SQLITE_BUSY if the first transaction lasts longer than the second one's timeout value. This is what happens under Xamarin on the simulator, and under Objective-C on both the simulator and device.

But under Xamarin on the device, the second transaction fails with SQLITE_BUSY immediately

# Actual behavior
Second transaction should wait for the first transaction to complete 

# Supplemental info (logs, images, videos)
https://github.com/tomhamming/xamarin-bundle-sqlite/blob/master/README.md
Comment 2 Sebastien Pouliot 2015-12-11 13:27:35 UTC
Apple does not use the default build settings for sqlite3, including different default for concurrency http://sqlite.org/threadsafe.html - that should not be an issue in this case (since you're not looking to serialize the calls) but it can explain difference you get with the simulator (and other sqlite environments).


If you want to use a custom libsqlite (linked staticaly) then _all_ `DllImport` must be set to __Internal (where the code will end up) and at least one is missing the linked test case.

SqlTester/ViewController.cs

>        [DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl, CharSet = CharSet.Unicode)]

^ you'll end up using two different libraries (and that won't work).
Comment 3 Tom Hamming 2015-12-11 15:01:01 UTC
I've tried the __Internal DllImport statements and they don't work. The current revision of the example repo is configured to use the included sqlite library, which is at version 3.9.2, but it is still linking against the sqlite in iOS (3.8.10.2).
Comment 4 Tom Hamming 2015-12-11 15:22:26 UTC
Correction: *now* all the DllImport statements are __Internal. But it's still not statically linking against the included sqlite file.
Comment 5 Sebastien Pouliot 2015-12-14 21:27:09 UTC
@Rolf I'm sure that used to work but maybe recent changes affected how we deal with libsqlite3 ?
Comment 6 Tom Hamming 2015-12-14 21:29:43 UTC
It's notable that under iOS 9.2, the issue manifests both on the device and simulator, including when running from Xcode, when linking against the iOS sqlite library (under 9.1, it was only a problem running on a device from Xamarin). Everything works fine from Xcode if I bundle my own sqlite in the Xcode project. So perhaps the workaround here is to get Xamarin.iOS to link against a bundled sqlite version.
Comment 7 Rolf Bjarne Kvinge [MSFT] 2015-12-15 12:44:16 UTC
One problem is that the symbols in libot_sqlite.a are private:

> nm -m libot_sqlite.a | grep sqlite3_libversion
000000000000d1bb (__TEXT,__text) private external _sqlite3_libversion

and that won't work with Xamarin.iOS. Since symbols are looked up dynamically (using dlsym, which won't find private symbols), Xamarin.iOS requires symbols to be public.

@Tom, can you try rebuilding sqlite with public symbols and see if that works?
Comment 8 Tom Hamming 2015-12-15 15:26:57 UTC
@Rolf hot dog, that did it! For reference, the build setting in Xcode is called "Symbols Hidden by Default" under "Apple LLVM - Code Generation". It might be worthwhile to put that on the page about linking native libraries (https://developer.xamarin.com/guides/ios/advanced_topics/native_interop/). That would have saved me a lot of time.

Thanks for looking at this.
Comment 9 Rolf Bjarne Kvinge [MSFT] 2015-12-16 10:53:21 UTC
Great! I'll mark this as resolved then.

I'll also see if I can update the documentation.