On Tue, Nov 10, 2009 at 06:04:20PM -0500, Frank Chang scratched on the wall: > > We have an application which uses Microsoft SQL Server 2005 > Extended stored procedures in conjunction with Sqlite databases. > We have a C++ DLL which uses the following code to insert rows > into a SQLite database: > > sprintf(Command,"INSERT INTO [Keys] ([Key], [Cluster], " > "[DupeGroup]) VALUES (\"%s\", \"%*.*s\", %d)", > MCKey,BlockSize,BlockSize,MCKey,DupeGroup);
Aside: using string functions to build query strings is a Bad Idea, and string constants in SQL use single-quotes, not double. > When we run this UPDATE statement from two SQL Server 2005 Management > Studio clients concurrently, one of the client processes returns with > the error code Database is locked and the other client process is > suspended. Has anyone seen this problem? If you keep getting SQLITE_BUSY return codes sooner or later you're obligated too cancel the current statement and rollback any open transactions. See: http://sqlite.org/c3ref/busy_handler.html In specific: The presence of a busy handler does not guarantee that it will be invoked when there is lock contention. If SQLite determines that invoking the busy handler could result in a deadlock, it will go ahead and return SQLITE_BUSY or SQLITE_IOERR_BLOCKED instead of invoking the busy handler. Consider a scenario where one process is holding a read lock that it is trying to promote to a reserved lock and a second process is holding a reserved lock that it is trying to promote to an exclusive lock. The first process cannot proceed because it is blocked by the second and the second process cannot proceed because it is blocked by the first. If both processes invoke the busy handlers, neither will make any progress. Therefore, SQLite returns SQLITE_BUSY for the first process, hoping that this will induce the first process to release its read lock and allow the second process to proceed. You're basically implementing a manual busy handler with your loop. Sooner or later it needs to give up and start over or things can remain locked. If all you're doing is waiting, you might want to look at sqlite3_busy_timeout(): http://sqlite.org/c3ref/busy_timeout.html This will keep trying if things still look safe, but will return SQLITE_BUSY right away if SQLite detects a possible deadlock. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
