Re: [sqlite] System.Data.SQLite and pooling problem
Thanks for the explanation, makes sense. We were using the new backup support but had to revert due to the access violations, was causing IIS to reset. The plan is to use the New Backup support once the access violation problem is solved. I'm still trying to reproduce and verify the problem is solved on our end with your fix in place. Btw, on an unrelated note, the is there a reason the System.Data.Sqlite source files are not directly included by the System.Data.Sqlite.csproj file instead of using a targets file? Just makes it a pain to view or make changes to the source in visual studio. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Monday, April 30, 2012 2:01 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] System.Data.SQLite and pooling problem Alexander Spence wrote: > > (I'm the one that filed the Sqlite Backup API Ticket.. Actually just merged > that code in and found the access violations.) > Are you using the new backup API support in the latest System.Data.SQLite release or some custom code? > > Your fix looks a lot better than global mutexes. If you don't mind explaining > since it's not obvious to me, what caused the race condition? > Thread 1 could be in the SQLite3.Close method, calling the SQLiteBase.ResetConnection method. Thread 2 could be in a GC thread, calling Dispose on the SQLiteConnectionHandle object, which ends up calling SQLiteConnectionHandle.ReleaseHandle, which calls SQLiteBase.CloseConnection (i.e. this has the potential to pull the rug out from under the SQLiteBase.ResetConnection method in the other thread). -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Please NOTE: This electronic message, including any attachments, may include privileged, confidential and/or inside information owned by Demand Media, Inc. Any distribution or use of this communication by anyone other than the intended recipient(s) is strictly prohibited and may be unlawful. If you are not the intended recipient, please notify the sender by replying to this message and then delete it from your system. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite and pooling problem
I work for Pluck, we handle social media integration for the NFL (Comments, Ratings, Etc.) We actually handled about 2k rps at the LB with about 10 servers taking up to 300 rps. We have a setup that does not require a centralized server over our 300 some customers (This solution was inherited from people that did not expect to server this much traffic). For our high traffic widgets, we are synchronizing in memory databases with ones on disk, (I'm the one that filed the Sqlite Backup API Ticket.. Actually just merged that code in and found the access violations.) Your fix looks a lot better than global mutexes. If you don't mind explaining since it's not obvious to me, what caused the race condition? Alex -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Monday, April 30, 2012 12:29 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] System.Data.SQLite and pooling problem Joe Mistachkin wrote: > > Alexander Spence wrote: > > > > What is the purpose of these locks when Sqlite itself has been compiled as ThreadSafe? > > > > I'm not sure what the original intent of that lock was; however, after reviewing the code, > that lock does not seem to serve a useful purpose. My plan now is to remove it for the > next release. > Further analysis and testing reveals a potential race condition when using the connection pool with that static lock removed (i.e. this may be causing the access violation you are seeing); however, the static lock itself is actually not a good solution to this issue. See my candidate fix for this issue here: https://system.data.sqlite.org/index.html/ci/0ed439a5e7?sbs=0 -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Please NOTE: This electronic message, including any attachments, may include privileged, confidential and/or inside information owned by Demand Media, Inc. Any distribution or use of this communication by anyone other than the intended recipient(s) is strictly prohibited and may be unlawful. If you are not the intended recipient, please notify the sender by replying to this message and then delete it from your system. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite and pooling problem
I noticed this while supporting the NFL Draft, not sure exactly how many threads were going through the score code that was causing this exception, but the servers were generally processing 200-300 requests per second at the time. Yes, connection pooling is turned on for the code in the stack trace reported earlier. This customer was running on an older version of our code, the newer version has our own secret sauce of in memory connections. I will take a look at your unit test and try to write something myself that reproduces the problem.. Also, I did modify the Sqlite Wrapper a bit, we removed the static locks from SqliteBase.cs as these were causing performance issues.. We do a lot with sqlite asynchronously and these locks were a single point of contention for everything. We have been running with these removed for over a year now and have had only a few problems.. One was caused by http://system.data.sqlite.org/index.html/tktview?name=fc994d007d and the other was fixed by this check in http://system.data.sqlite.org/index.html/info/10d400ebd0 (THANK YOU!). What is the purpose of these locks when Sqlite itself has been compiled as ThreadSafe? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Saturday, April 28, 2012 9:49 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] System.Data.SQLite and pooling problem Alexander Spence wrote: > > I am seeing a similar error since upgrading to 1.0.80.0. I am still working on > trying to reproduce it, but here is a stack trace: > Based on your stack traces, it seems you are using the connection pool as well? How many threads are involved? Is the exception happening consistently or on intermittently? -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Please NOTE: This electronic message, including any attachments, may include privileged, confidential and/or inside information owned by Demand Media, Inc. Any distribution or use of this communication by anyone other than the intended recipient(s) is strictly prohibited and may be unlawful. If you are not the intended recipient, please notify the sender by replying to this message and then delete it from your system. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System.Data.SQLite and pooling problem
I am seeing a similar error since upgrading to 1.0.80.0. I am still working on trying to reproduce it, but here is a stack trace: All of our Connections and Statements are wrapped in using statements and being disposed correctly. I will report with more if I am able to reproduce this with a unit test. 2012-04-28 13:47:54,100 [20] ERROR Pluck.SiteLife.Daapi.Cache.DaapiRequestCache.Get(C:\msys\home\sitelife\ApiSite\Daapi\Cache\DaapiRequestCache.cs:85) - Error attempting to get Response for ItemScoresRequest from Daapi Cache System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt. at System.Data.SQLite.UnsafeNativeMethods.sqlite3_busy_timeout(IntPtr db, Int32 ms) at System.Data.SQLite.SQLite3.SetTimeout(Int32 nTimeoutMS) in c:\Users\Alex\Downloads\sqlite-netFx-source-1.0.80.0\System.Data.SQLite\SQLite3.cs:line 259 at System.Data.SQLite.SQLite3.Open(String strFilename, SQLiteConnectionFlags connectionFlags, SQLiteOpenFlagsEnum openFlags, Int32 maxPoolSize, Boolean usePool) in c:\Users\Alex\Downloads\sqlite-netFx-source-1.0.80.0\System.Data.SQLite\SQLite3.cs:line 249 at System.Data.SQLite.SQLiteConnection.Open() in c:\Users\Alex\Downloads\sqlite-netFx-source-1.0.80.0\System.Data.SQLite\SQLiteConnection.cs:line 1021 at Pluck.SiteLife.Scores.Storage.ScoreCollectionStorage.GetDbConnection() in C:\msys\home\sitelife\ApiSite\Services\Scores\ScoreCollectionStorage.cs:line 426 at Pluck.SiteLife.Scores.Storage.ScoreCollectionStorage.CreateCommand() in C:\msys\home\sitelife\ApiSite\Services\Scores\ScoreCollectionStorage.cs:line 415 at Pluck.SiteLife.Scores.Storage.ScoreCollectionStorage.GetIndividualScores(String userKey, String targetKey) in C:\msys\home\sitelife\ApiSite\Services\Scores\ScoreCollectionStorage.cs:line 211 at Pluck.SiteLife.Services.ScoreService.GetUserScoreForItem(CommandContext ctx, String scoreKey, BaseKey parentKey, BaseKey targetKey, UserKey userKey) in C:\msys\home\sitelife\ApiSite\Services\Scores\ScoreService.cs:line 227 at Pluck.SiteLife.Daapi.Models.Reactions.ItemScore.Refresh(CommandContext ctx) in C:\msys\home\sitelife\ApiSite\Daapi\Models\Reactions\ItemScore.cs:line 150 at ItemScoresResponseCacheBuilder.Refresh(CommandContext ctx, ItemScoresResponse res) at ItemScoresResponseWrapper.Refresh(CommandContext , IResponse ) at Pluck.SiteLife.Daapi.Cache.DaapiRequestCache.Get(CommandContext ctx, IRequest req) in C:\msys\home\sitelife\ApiSite\Daapi\Cache\DaapiRequestCache.cs:line 85 2012-04-28 13:47:54,678 [2] ERROR Pluck.SiteLife.GlobalApplication.CurrentDomain_UnhandledException(C:\msys\home\sitelife\ApiSite\GlobalApplication.cs:648) - System.Runtime.InteropServices.SEHException: External component has thrown an exception. at System.Data.SQLite.UnsafeNativeMethods.sqlite3_next_stmt(IntPtr db, IntPtr stmt) at System.Data.SQLite.SQLiteBase.ResetConnection(SQLiteConnectionHandle db) in c:\Users\Alex\Downloads\sqlite-netFx-source-1.0.80.0\System.Data.SQLite\SQLiteBase.cs:line 383 at System.Data.SQLite.SQLite3.Close() in c:\Users\Alex\Downloads\sqlite-netFx-source-1.0.80.0\System.Data.SQLite\SQLite3.cs:line 120 at System.Data.SQLite.SQLiteConnection.Close() in c:\Users\Alex\Downloads\sqlite-netFx-source-1.0.80.0\System.Data.SQLite\SQLiteConnection.cs:line 643 at System.Data.SQLite.SQLiteConnection.Dispose(Boolean disposing) in c:\Users\Alex\Downloads\sqlite-netFx-source-1.0.80.0\System.Data.SQLite\SQLiteConnection.cs:line 483 at System.ComponentModel.Component.Finalize() -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Friday, April 27, 2012 11:21 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] System.Data.SQLite and pooling problem Greg Carter wrote: > > We recently upgraded to 1.0.80.0 from 1.0.77.0 and have found when we > turn connection pooling on we get seemingly random memory access > violations that > happen in calls to UnsafeNativeMethods.sqlite3_busy_timeout ,call > stack - Open/SetTimeout (line 259 of SQLite3.cs). > Are you seeing an ObjectDisposedException or an AccessViolationException? -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Please NOTE: This electronic message, including any attachments, may include privileged, confidential and/or inside information owned by Demand Media, Inc. Any distribution or use of this communication by anyone other than the intended recipient(s) is strictly prohibited and may be unlawful. If you are not the intended recipient, please notify the sender by replying to this message and then delete it from your system. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org
[sqlite] Sqlite strange stack trace
Hello, I am using SQLite to keep a copy of a database in memory. On load, this database is read off disk with the Backup API. Our project is .Net and we are using the System.Data.Sqlite driver 1.0.75. I have been testing performance under load and noticed some strange behavior. Access to a single connection is synchronized and I saw 30 threads waiting on a thread with the stack trace below. Why would prepare be calling into backup? I am very confused, if anyone could shed some light on this I would be greatly appreaciative. 0b1ec888 07fef8b6eab7 System_Data_SQLite+0x1270 0b1ec890 07fef8bdc0a8 System_Data_SQLite!sqlite3_backup_remaining+0x107 0b1ec8f0 07fef8be0329 System_Data_SQLite!sqlite3_backup_init_interop+0x84b8 0b1eca90 07fef8be3b67 System_Data_SQLite!sqlite3_backup_init_interop+0xc739 0b1ecb00 07fef8c05448 System_Data_SQLite!sqlite3_backup_finish+0xcf7 0b1ecb70 07fef8c077cb System_Data_SQLite!sqlite3_prepare16_interop+0x5188 0b1ecd80 07fef8c0799e System_Data_SQLite!sqlite3_prepare16_interop+0x750b 0b1ecdc0 07fef766ff82 System_Data_SQLite!sqlite3_step+0x8e 0b1ecdf0 07ff009ed042 mscorwks!DoNDirectCallWorker+0x62 0b1ece80 07ff009ebae9 System_Data_SQLite!System.Data.SQLite.SQLite3.Step(System.Data.SQLite.SQLiteStatement)+0xa2 0b1ecf90 07ff009eb935 System_Data_SQLite!System.Data.SQLite.SQLiteDataReader.NextResult()+0x159 0b1ecfd0 07ff009eb2c4 System_Data_SQLite!System.Data.SQLite.SQLiteDataReader..ctor(System.Data.SQLite.SQLiteCommand, System.Data.CommandBehavior)+0x75 0b1ed020 07ff013925da System_Data_SQLite!System.Data.SQLite.SQLiteCommand.ExecuteReader(System.Data.CommandBehavior)+0x34 Please NOTE: This electronic message, including any attachments, may include privileged, confidential and/or inside information owned by Demand Media, Inc. Any distribution or use of this communication by anyone other than the intended recipient(s) is strictly prohibited and may be unlawful. If you are not the intended recipient, please notify the sender by replying to this message and then delete it from your system. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_open16 and flags
How would I open a database connection using UTF-16 but still be able to specify flags. Seems like there would be a sqlite3_open16_v2 but there's not. Please NOTE: This electronic message, including any attachments, may include privileged, confidential and/or inside information owned by Demand Media, Inc. Any distribution or use of this communication by anyone other than the intended recipient(s) is strictly prohibited and may be unlawful. If you are not the intended recipient, please notify the sender by replying to this message and then delete it from your system. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Contributing to System.Data.Sqlite
Who would I talk to if I wanted to contribute to the System.Data.Sqlite project? We use this code in an enterprise environment and would like to contribute our changes to the community. Please NOTE: This electronic message, including any attachments, may include privileged, confidential and/or inside information owned by Demand Media, Inc. Any distribution or use of this communication by anyone other than the intended recipient(s) is strictly prohibited and may be unlawful. If you are not the intended recipient, please notify the sender by replying to this message and then delete it from your system. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite database sync
Do you really require a local copy of the entire database? Can you not just create a service that queries the database and gives you results on demand, caching the results? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of David Haymond Sent: Wednesday, October 06, 2010 7:30 AM To: sqlite-users@sqlite.org Subject: [sqlite] SQLite database sync Hi SQLite users, I am completely new to SQLite (and SQL in general), and I am currently working on an iPhone app that uses the embedded SQLite engine to cache offline data. What is the best way to implement synchronization capabilities (change tracking, state tracking, etc.) in a separate table (such as meta), so that the app can sync to the server? My database contains two tables: locations and trips. trips is the child of locations. Any help would be greatly appreciated. David Haymond ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Please NOTE: This electronic message, including any attachments, may include privileged, confidential and/or inside information owned by Demand Media, Inc. Any distribution or use of this communication by anyone other than the intended recipient(s) is strictly prohibited and may be unlawful. If you are not the intended recipient, please notify the sender by replying to this message and then delete it from your system. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Index Help
Hey guys,
I am writing a comment index database and need some help optimizing the
indices. Here is my table schema and a list of the possible queries. Please
note that they could be Ascending or Descending order by's. This will be used
in an enterprise level social media app that must be optimized for reads.
Should I create one index like this? [ParentThreadPath], [UserKey],
[NumberOfRecommends], [Timestamp] ? Or should I create multiple indices
targeting the specific queries? Also, does it matter if the index is created
with ASC and I order by DESC?
Thanks in advance for your help.
CREATE TABLE [CommentIndex] (
[CommentKey] TEXT UNIQUE,
[ThreadPath] TEXT PRIMARY KEY,
[ParentThreadPath] TEXT,
[ParentCommentKey] TEXT,
[NumberOfReplies] INT DEFAULT 0,
[NumberOfRecommends] INT DEFAULT 0,
[UserKey] TEXT,
[Timestamp] INT
);
SELECT CommentKey FROM CommentIndex WHERE ParentThreadPath = 'A1' AND UserKey
IN ('1','2','3') ORDER BY Timestamp
SELECT CommentKey FROM CommentIndex WHERE ParentThreadPath = 'A1' AND UserKey
IN ('1','2','3') ORDER BY NumberOfRecommends
SELECT CommentKey FROM CommentIndex WHERE UserKey IN ('1','2','3') ORDER BY
Timestamp
SELECT CommentKey FROM CommentIndex WHERE UserKey IN ('1','2','3') ORDER BY
NumberOfRecommends
SELECT CommentKey FROM CommentIndex WHERE ParentThreadPath = 'A1' ORDER BY
Timestamp
SELECT CommentKey FROM CommentIndex WHERE ParentThreadPath = 'A1' ORDER BY
NumberOfRecommends
Please NOTE: This electronic message, including any attachments, may include
privileged, confidential and/or inside information owned by Demand Media, Inc.
Any distribution or use of this communication by anyone other than the intended
recipient(s) is strictly prohibited and may be unlawful. If you are not the
intended recipient, please notify the sender by replying to this message and
then delete it from your system. Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
