This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hello, I just upgraded from LightSpeed 4 to 5, and am now referencing all the MindScape binaries as well as the SQLite provider included with version 5. A behavior difference between 4 and 5 that I am seeing is the locking of the physical SQLite database file. In version 4, the file isn't locked but in version 5 it is. The problem for me is that when my program exits it copies the database file out as a backup. That backup now fails. This is .NET 4.0, a Windows Forms application. To recreate the problem, I do this. I've trimmed out the LightSpeed context initialization and other stuff because "it didn't seem to matter" to the problem.
In version 4, at Application.Run(...), the database file is not locked and could be even deleted out from under the application if I wanted to. In version 5, the database file is locked. I'm not sure if this is a LightSpeed thing or a SQLite thing, or if something is even broken but it does seem to be a behavior change. Any guidance on what is supposed to happen here with respect to file locking? Is there a way anyone can think of to NOT lock the file? Suggestions welcome. Thank you for your time. Lionel |
|
|
Hi Lionel, We have included a newer version of System.Data.SQLite with LightSpeed 5 so I would say that will be where the behaviour has changed. From a quick test it would appear that while a connection is active the file remains locked. If you need to use an earlier version of System.Data.SQLite you can use a binding redirect to handle this - e.g.
|
|
|
Hi Jeremy, thank you for answering. You wrote: "while a connection is active the file remains locked" In my sample code, the file lock remains after the IUnitOfWork has been closed and disposed of. Wouldn't the Dispose also close the underlying connection and so unlock the file? Edit the next day: Interestingly, if I don't actually fetch an entity, the problem goes away. The problem happens only if I fetch an entity. Lionel |
|
|
Hi Lionel, Interesting, I am only seeing the lock persist until I dispose the UnitOfWork (where the underlying connection is closed). I am not sure why this would be different for you. If you wait say a minute or two does it unlock then (e.g. maybe something else is being subsequently GC'ed which was continuing to hold the lock?) The behaviour you will be seeing about the file not locking until you select an entity will be because no connection will be opened until this occurs - the UnitOfWork does not establish a connection on creation, rather when it is first required (e.g. you fetch an entity).
|
|
|
Hi Jeremy,
I waited a few hours (It was dinner!) and the file remained locked. The only way to free the file is exit the application, as far as I can see. Maybe it's freed up when the Lightspeed context is unloaded but I've not tested that. My entire test case is the code above (with the model of course) and nothing in the background. With the code above, I am seeing these results:
Everything I am seeing here suggests that something inside Lightspeed 5 is now keeping the file locked. I can't imagine what, but I don't have visibility to any of that. I guess I don't know what to do here, Jeremy. To me, this is an undesirable behavior change on the version 5 upgrade. My two rather undesirable choices seem to be revert to LS 4 or recode that piece of my app that backs up by copying the file out. We both know I'm not going to revert to LS 4. Please advise on what, if anything Mindscape will do or if I need to deal with it in my app. I just don't want to mess with it anymore :) Thank you again for your time. Lionel |
|
|
I also encounter similar problem with LS5. I made an attempt to exchange LS4 with LS5 in our software and all the unit tests failed. The tests which failed basically create SQLite DBs, perform some operations and finally close DBs and delete files. All the failures are on File.Delete() method, so probably connections remain open after uow.Dispose(). They for sure didn't in LS4. |
|
|
Thanks InstalSoft, I was beginning to think I was imagining things :) I appreciate knowing I'm not the only one. |
|
|
You're welcome, Lionel :-) Jeremy, you may take tests from my other forum thread: http://www.mindscapehq.com/forums/thread/651848. They run fine under LS4 (except the problem with NAN), but under LS5 they fail. The funny thing is: all of these tests create and delete DBs, but not all of them fail (I claimed incorrectly in my previous post that they all fail). But the tests which fail are always the same (I ran them multiple times)- so there's probably something specific in the data which prevents connections from being closed. |
|
|
Thanks for the updates, I will look to see if I can reproduce this behaviour here. The only direct change with SQLite between 4 and 5 is with the provider version so its very strange that there appears to be some other difference. Its certainly possible of course since there have been may other changes in the framework. I will see if I can track this down, so hopefully we can repro this :) Ill update you once I have had a look at the other issue.
|
|
|
Hi, I repeated the test above against System.Data.Sqlite.dll version 1.0.84.0 and the problem appears identical to that from the version 1.0.82.0 that ships with Mindscape. Unfortunately, the file remains locked there as well. Lionel |
|
|
I believe Ive isolated the cause of the lock discrepancy that we were seeing which is due to when a disposal occurs on the command objects which get used. Ive added an update which will more swiftly dispose of the command objects after use so they will definitely be disposed prior to the connection close after which the lock will be released by SQLite. This update will be available in the next nightly build for 5.0. Keep in mind the file will of course still be locked while a connection is active to the file or if you utilize any manual command objects or surface a manual reader (e.g. via UnitOfWork.Project) and dont dispose of these as the requirements of System.Data.Sqlite is for all reader/command and connection objects to be disposed before it releases its lock on the file.
|
|
|
Unfortunately there's no improvement :-( I've downloaded the nightly build for 5.0 of 20130402. In my test suite (you have it, as mentioned before) the following tests fail: Case2Test(true) and RelationsmustbeaccessibleafterDBopen(). I cannot see anything incorrect about command objects inside these tests. |
|
|
I confirm InstalSoft's results with the 20130403 nightly. The file remains locked. Thank you. -Lionel |
|
|
Hi guys, Ive added one additional forced disposal which might impact this although all of the tests (outside of the one regarding NaN doubles) pass for me so I am guessing there is some subtle timing difference there. If you could check again with the next nightly build and see if that covers it as it was on the execution path for the Case2Test where as the previous change I made was not. Lionel, just to confirm you are seeing the file as being locked after the UOW scope is completed (and any manually surfaces command objects and readers have been explicitely disposed)? If so (and after the next nightly you still see a problem) are you able to look at sending through a repro for this I could check against in case this highlights the issue better than the existing repro project InstalSoft sent through? I would certainly like to get this one covered off! :)
|
|
|
It is much better now :-), but still not perfect. Now all small tests pass (the tests I attached), but our bigger test suite fails (5 out of 255 tests fail). The error is still "The process cannot access the file 'xxx' because it is being used by another process.", so probably still some connections are not closed. I've noticed that all the tests that fail open existing database files (opposite to the small tests which create DB from scratch), migrate them up, perform some operation and close DB. Maybe there's something in migrations what keeps DB open? |
|
|
I updated to the 20130404 nightly. As InstalSoft wrote, it is much better. I am not seeing any failures in my teensy test back in post 1 (after 30 iterations) but I still see some failures occasionally when inside my broader application. One out of five application executions fail when I try to copy the file out. I don't use the migration toolkit but do seem to be seeing the same pattern as InstalSoft - something is not freeing up somewhere under some path. Thanks for the great improvement so far! |
|
|
Thanks for the updates. I will have a check into the migrator code as that will likely need an update also - it has its own connection handling logic. If you could please send through a repro regarding any issue around normal UnitOfWork based use though as I believe we should have everything covered off here but if you are still seeing any issues then there must be something else floating around but I will need some way of debugging this to track it down at this stage.
|
|
|
Hi Jeremy, I'd like to ask if there's any progress in this subject. I've downloaded the latest nightly of 20130522 and I still have errors in my unit tests :-( Thanks, Darek Wąsacz |
|
|
Hi Darek, Is this still just with the migrations or with normal UnitOfWork related activity?
|
|
|
I suspect migrations, but I'm not 100% sure. As I wrote in my previous posts: "all the tests that fail open existing database files (opposite to the small tests which create DB from scratch), migrate them up, perform some operation and close DB". |
|
|
Ive added some additional updates to the Sqlite provider in the migration framework which will be included in tonights nightly build so have a recheck on those tests when you are checking with the updated Sqlite provider.
|
|
|
Hi Jeremy, Sorry for late answer, but I was very busy with other tasks last days. I've downloaded latest nightly (20130606) but unfortunately it didn't help at all. I'm a little desperate. I have prepared a compiled subset of our test DLLs. You'll find it at http://programisci.instalsoft.com/Mindscape/MindscapeDBLockingProblem.zip. Please let me know when you downloaded it - I don't want to keep it there too long. It is not possible to send you all the sources - they are too big and require too many prerequisites to compile. All DLLs are compiled with "Specific version" = "false" so it should be possible to replace Lightspeed dlls and still run the tests. Please go to "bin" folder and run NUnit with InstalSoft.DataModel.TestPart.Tests.dll. Please ignore failing tests: "shouldreadcommandmodule" and "Getloadedmodulescount" - there are a few data files missing - these tests are not relevant in this subject. The rest of failing tests (at least 3, sometimes 4) concern the subject. They fail while trying to delete temporary files - they remain open by Lightspeed, while I'm sure I have closed all the connections. With Lightspeed 4 they all pass. In Source\InstalSoft\DataModel\TestPart\Tests you'll find .cs source files of the failing tests as well as DMPFacade.cs - maybe they could help as well. Please let me know if I could do something more to help catching the problem. Best regards, Darek Wąsacz, InstalSoft |
|
|
Hi Darek, I have downloaded the file now so you can remove this. I will update this once I have had a chance to look at this further.
|
|
|
Hi Jeremy, I managed to isolate the problem in System.Data.SQLite. I posted a ticket to SQLite guys: http://system.data.sqlite.org/index.html/tktview/0c26384ec44d9f8b8bdde4ccec8ebc9af5221a54. I'll let you know it there's some news about it. Darek Wąsacz, InstalSoft |
|
|
Hi Jeremy, Please see mistachkin's response to my SQLite ticket: http://system.data.sqlite.org/index.html/tktview/0c26384ec44d9f8b8bdde4ccec8ebc9af5221a54. After applying his advice in my ADO.NET test, the problem disappeared (in this test). So I suspect that Lightspeed doesn't dispose some SQLiteCommand or SQLiteDataReader objects and, as a consequence, connections remain open. Darek Wąsacz, InstalSoft |
|
|
Yes, I would agree thats the most likely reason for this as the previous fixes in the runtime came from these types of changes to force disposal with Sqlite. The changes I most recently made added some extra forced disposal code around some DataTable use we have in the migration engine. Is it possible to get a more complete solution for your failing tests? I will need to be able to work through the call paths into the migration code taken by your failing tests with a debugger to progress this sooner rather than later so if its possible to arrange something for this let me know.
|
|
|
Hi Jeremy, I have found a bug in our migration extension (which provides SQLite column deletion and default values for added columns). After fixing this extension all the tests pass. Case is solved! Thank you for your cooperation and I'm sorry that I didn't find it earlier. Darek Wąsacz, InstalSoft |
|
|
Awesome! Great to hear you finally managed to track this down.
|
|