|
Post by Rod on Mar 10, 2019 4:51:18 GMT -5
Ok, having now had time to look Task Manager was indeed showing two window resources that happened to be named xx.db
Going back to basics. Using "simple databas.bas" example.
1) Files #accessor,records.db is using DefaultDir$ 2) Sqliteconnect #records, "records.db" is NOT using DefaultDir$ 3) First time in there is no .db so a .db is created but in StartupDir$ lb5-347 4) Second time in Files looks for the .db in DefaultDir$ lb5-347\bas_files and it is never found. 5) This causes the program to attempt to write duplicate named table to the .db stored in StartupDir$
So that lead me to see if acting on a different .db or connecting to one in a different location and acting on another could be the source of Dan's problems. But I cant replicate the lock I just get different file access errors.
Great that Metro has managed to get Dan's code working. Just wish we understood where the lock came from.
|
|
|
Post by metro on Mar 10, 2019 5:37:18 GMT -5
www.sqlite.org/faq.html#q5point 5 may be relevant
found after a search to back up my theory a google search produces multiple posts regarding file locks some methods to circumvent the problem are beyond me.
|
|
|
Post by meerkat on Mar 10, 2019 6:46:56 GMT -5
Great that Metro has managed to get Dan's code working. Just wish we understood where the lock came from. From what I've found in the past the lock may be coming from the previous update. Did you try to do more than one update? I wonder if it locks the 2nd time? Also I've found that using disconnect and connect again before updates does not always work. It's unpredictable. I'll try to find a example where that fails. This all started because I wanted to write something simple program to test the grid. I wanted single threaded with a single open. I knew the system locks the DB before the write and then releases it. Being single threaded it should be ok because It cannot get back to the reading of the table before it completes the update. There are a couple of changes that need to be made to the program. It needs to test for Unique Index so it doesn't try to use the 'rowid' as a test in the "INSERT OR REPLACE" that throws a conflict error. I can post the changes if you like. But if you use tables without a unique key, it does not matter. I've always wondered on indexed tables, since it has to update both the B-Tree and the table, maybe that has some kind of effect?
|
|
|
Post by meerkat on Mar 10, 2019 7:38:35 GMT -5
Here is a very small program that simulates what the feasibility program does. It has a single sql connect. It goes through 10 passes Each pass reads 10 records. After reading the 10 records it uses a INSERT OR REPLACE to replace the last record read in the pass. It does not do a disconnect/connect before the update -- It works, and does not lock. Maybe different changes can be done to test different stuff. I already tried 5 sql connects and it works. I changed the INSERT OR REPLACE so it does a REPLACE and not a INSERT.. And that works. Maybe I need to ask what record to update then do the update.. I'll see if that works.
' ---------------------------------- ' LB5 lock test monster ' ----------------------------------
db$ = "test.db" sqliteconnect #sql, db$
' No table then create it with a unique key ' sql$ = "SELECT * FROM sqlite_master WHERE tbl_name = 'xx'" #sql execute(sql$) rows = #sql ROWCOUNT()
if rows = 0 then sql$ = " CREATE TABLE xx ( xxNum integer(5), xxDescr VARCHAR(10), xxCount integer(5))" #sql execute(sql$) #sql execute("CREATE UNIQUE INDEX xx_num ON xx(xxNum)") ' give it a unique index print "Table A Created" end if
' re populate the table with new stuff #sql execute("DELETE FROM xx") for i = 1 to 100 #sql execute("INSERT INTO xx VALUES (";i;",'xx_";right$("000"+str$(i),3);"',0)") next i
' read the table 10 rows 10 time ' then do INSERT OR UPDATE to the last row read ' for pass = 1 to 10 #sql execute("SELECT * FROM xx WHERE rowid > ";pass;" ORDER BY xxNum LIMIT 10") rows = #sql ROWCOUNT() 'Get the number of rows print "Number of rows in table xx:";rows for i = 1 to rows #row = #sql #nextrow() xxNum = #row xxNum() xxDescr$ = #row xxDescr$() xxCount = #row xxCount() print xxNum;chr$(9);xxDescr$;chr$(9);xxCount next i print "** end of select ** pass: ";pass ' INSERT OR REPLACE the last record read ' sql$ = "INSERT OR REPLACE INTO xx VALUES(";xxNum;",'";xxDescr$;"',";pass;")" print sql$ #sql execute(sql$) next pass #sql disconnect() end
|
|
|
Post by Rod on Mar 10, 2019 8:00:13 GMT -5
Well simple testing proves not. This saves eight records between connect and disconnect and a further record within a following connect select disconnect. So we need to tease out what is tripping it up. Can you start more simple testing? You have much more knowledge than I about SQL. Stretch this example using some of the SQL code you have been using till you manage to trip it up.
Edit, we cross posted I see your on the case.
sqliteconnect #records, "c:\atemp\Records.db" #records execute("insert into Payables (company, invoice, due, amt) values (""IBM"", ""1029A"", 38945, ""19.95"")") #records execute("insert into Payables (company, invoice, due, amt) values (""Commodore"", ""VIC-20"", 84545, ""299.95"")") #records execute("insert into Payables (company, invoice, due, amt) values (""IBM"", ""1029A"", 38945, ""19.95"")") #records execute("insert into Payables (company, invoice, due, amt) values (""Commodore"", ""VIC-20"", 84545, ""299.95"")") #records execute("insert into Payables (company, invoice, due, amt) values (""IBM"", ""1029A"", 38945, ""19.95"")") #records execute("insert into Payables (company, invoice, due, amt) values (""Commodore"", ""VIC-20"", 84545, ""299.95"")") #records execute("insert into Payables (company, invoice, due, amt) values (""IBM"", ""1029A"", 38945, ""19.95"")") #records execute("insert into Payables (company, invoice, due, amt) values (""Commodore"", ""VIC-20"", 84545, ""299.95"")") #records disconnect() print "Eight rows added." 'input "Press Enter to continue."; a$
sqliteconnect #records, "c:\atemp\Records.db" query$ = "select * from Payables" #records execute(query$) if #records hasanswer() then for x = 1 to #records rowcount() result$ = #records nextrow$("|") print result$ next x end if #records execute("insert into Payables (company, invoice, due, amt) values (""Commodore"", ""VIC-20"", 84545, ""299.95"")") #records disconnect() print "Rows retrieved." print "Done." end
|
|
|
Post by meerkat on Mar 10, 2019 8:39:46 GMT -5
I was able to get the "lock test monster" program above to lock. But I expected it. I did another connect using #sql1 As it reads the records it does an update. So for each record it reads it does a update. Basically reading and updating at the same time - and the update works: sql1$ = "UPDATE xx SET xxCount = xxCount + 5 WHERE xxNum = ";xxNum #sql1 execute(#sql1)
But after the 10 reads when it does the INSERT OR REPLACE but throws a record locked error. If I do a disconnect, execute, and reconnect it works..
If I thought the disconnect and connect were the problem, I could live with it and move on. But I've done this in the past and it hasn't helped. Maybe we accept it and wait for a program to lock using that method. Who knows, maybe with that routine the feasibility program will fail. If the past is true, it will lock doing this for some programs.
What gets bad is when you have some SQL commands that are over a page long with lots of outer joins, subselects and selects within selects, sub groups, and logic within the select. Especially when the SQL runs for a few hours and then you get the error.
I wouldn't even try SQLite for complex apps, but in Run Basic there is no option so I moved to another language for web apps. And BTW using the other language with SQLite, I've never got a lock!!..
I'll see what I can do to get something to lock with a simple read and update.
|
|
|
Post by meerkat on Mar 10, 2019 9:10:44 GMT -5
After thinking about it. The above test of "lock test monster" when it updates while reading should NOT have locked. It should have locked and unlocked after the update while reading. Therefore it should not be locked when the UPDATE OR REPLACE takes place.
So I think we found a problem!!!! What do you guys think??
|
|
|
Post by Rod on Mar 10, 2019 9:33:27 GMT -5
Ok running lock test monster works for me every time. I have run it fifty times consecutively without error or lock.
I can get it to error and I can get it to lock. If I click RUN twice in the same IDE I get error messages but not a lock.
If I open a second IDE and RUN two instances of LB5 in parallel it locks up every time. Closing the locked program allows the earlier program to finish without error.
Can you explain a bit more, was this two instances? Or did you change something in the program to get the lock?
|
|
|
Post by meerkat on Mar 10, 2019 10:04:14 GMT -5
OK.. These changes between the '****** were added to the lock test monster. It adds another connection so it has #sql and #sql1
The one that makes it lock is the PRAGMA statement. If you comment that out it will not lock. It has no reason to lock cause far as I know it's simply a list of table info. Here is the code.. I think it should not lock.. You be the judge.
' ---------------------------------- ' LB5 lock test monster ' ----------------------------------
db$ = "test.db" sqliteconnect #sql, db$ ' *************** sqliteconnect #sql1, db$ ' ****************
' No table then create it with a unique key ' sql$ = "SELECT * FROM sqlite_master WHERE tbl_name = 'xx'" #sql execute(sql$) rows = #sql ROWCOUNT()
if rows = 0 then sql$ = " CREATE TABLE xx ( xxNum integer(5), xxDescr VARCHAR(10), xxCount integer(5))" #sql execute(sql$) #sql execute("CREATE UNIQUE INDEX xx_num ON xx(xxNum)") ' give it a unique index print "Table A Created" end if
' re populate the table with new stuff #sql execute("DELETE FROM xx") for i = 1 to 100 #sql execute("INSERT INTO xx VALUES (";i;",'xx_";right$("000"+str$(i),3);"',0)") next i
' ************ this causes it to lock ************* sql$ = "PRAGMA table_info(xx)" ' returns cid|name|type|notnull|dflt_value|pk print sql$ #sql execute(sql$) ' *********************** ' read the table 10 rows 10 time ' then do INSERT OR UPDATE to the last row read ' for pass = 1 to 10 #sql execute("SELECT * FROM xx WHERE rowid > ";pass;" ORDER BY xxNum LIMIT 10") rows = #sql ROWCOUNT() 'Get the number of rows print "Number of rows in table xx:";rows for i = 1 to rows #row = #sql #nextrow() xxNum = #row xxNum() xxDescr$ = #row xxDescr$() xxCount = #row xxCount() ' ******************* sql1$ = "UPDATE xx SET xxCount = xxCount + 5 WHERE xxNum = ";xxNum #sql1 execute(sql1$) ' *********************** print xxNum;chr$(9);xxDescr$;chr$(9);xxCount next i print "** end of select ** pass: ";pass ' INSERT OR REPLACE the last record read ' sql$ = "INSERT OR REPLACE INTO xx VALUES(";xxNum;",'";xxDescr$;"',";pass;")" print sql$ #sql execute(sql$) '#sql execute(sql$)
next pass
#sql disconnect() end
|
|
|
Post by meerkat on Mar 10, 2019 10:14:19 GMT -5
BTW.. I looked at the feasibility program and it has a PRAGMA sql. Even if it did lock that command was done was at the front and should not be locked by the time it gets to the meat. Same as this test. It's only done once up front. So if there were locks they surely should have been gone before the read and updates take place...
|
|
|
Post by Rod on Mar 10, 2019 11:58:31 GMT -5
Ok, the changes are hard to follow, we should keep the changes to a minimum.
The last code you posted locks for me. But I have a couple of issues with the code, bear in mind I am a complete novice at SQL.
First, the code balks at the #sql1 line not the PRAGMA line.
Second, changing
sql$ = "PRAGMA table_info(xx)" ' returns cid|name|type|notnull|dflt_value|pk to sql$ = "PRAGMA table_xinfo(xx)" ' returns cid|name|type|notnull|dflt_value|pk
appears to solve the problem.
So as a novice I need to understand why we need two connections to one database in a single threaded application/program. We know that if you start two instances of Liberty accessing the same .db you will instantly get locks. We know that single threaded apps process sequentially so what is the point of having two connections? They will not speed access and they may cause internal conflicts that we are unaware off. But it does work, but why do it? I need educated.
The SQLite site that lists PRAGMA commands is littered with dire warnings about their use, case in hand table_info crashes tablex_info runs?
Please don't take this as criticism I am focused on the code , databases in general and getting it all to work.
I have a fundamental issue with SQLite as well. It is offered as "concurrent" and a throw away "but only single updates allowed" without offering any real way (it appears to me) to manage that conflict. So while folks assume multiuser functionality as we are seeing it is anything but. For the vast majority of Liberty coders single user or multiuser read will be fine. But to get beyond that we need a method to manage locks.
Edit to correct table_xinfo not tablex_info
|
|
|
Post by meerkat on Mar 10, 2019 13:03:17 GMT -5
Not sure what the PRAGMA tablex_info is. I'll have to look it up and see what it's all about.
I know about table_info and table_xinfo, but far as I know they basically do the same thing; Here are the docs from sqlite: -------------------------------------------- PRAGMA schema.table_xinfo(table-name); This pragma returns one row for each column in the named table, including hidden columns in virtual tables. The output is the same as for PRAGMA table_info except that hidden columns are shown rather than being omitted. --------------------------------------------
Since PRAGMA table_info causes the problem, that's the one I wanna use. In any event the PRAGMA table_info does not itself cause a problem or even complain about it. What is happening is that it causes the file to be in EXCLUSIVE LOCK. So the next time it does a #sql1 update command it notices it is still locked and throws a database is locked error.. Why.. First the PRAGMA is a read so it shouldn't be putting it in Exclusive lock anyway. Even if it does SQLite should be going through it's normal lock and unlock and be done with it. Why is LB or whoever telling the system it's still locked when the PRAGMA is complete. This lock never ever leaves and there is no way to get rid of it. So your program will throw the lock error at any future place in your code you try to update. You must close the program, or sometimes the disconnect/connect works as metro found out. I have a feeling if the DB is disconnected and connected after the PRAGMA it will get rid of the lock.
In RB, it's the backend server that holds the lock, and the only way out is to kill the server and restart it.
|
|
|
Post by Rod on Mar 10, 2019 13:46:13 GMT -5
Ok, I had a typo but it remains the fact that table_xinfo runs and table_info does not. Why I do not know, perhaps the SQL community would be better placed to help with that question. Remember Liberty is just firing of standard SQL calls in the background.
While you say it is a read does it not have to form a new table holding the results?
What about dual connects? Good practice / bad practice?
|
|
|
Post by meerkat on Mar 10, 2019 15:28:58 GMT -5
I doubt that it creates a table. If it did, I think it would use a :memory: table. But who knows? I don't think this is the only cause of a lock. I just happen to run across this one.
Today I don't have time to do anything. But I'd like to do the exact same thing in LB4 with DLL calls. If that works then I'll want to find out why LB5 locks and not LB4 with DLL calls. Personally I like single connects. Good or Bad - sometimes you don't have a choice and need multiple connects. And we just proved that two connects work with two sql commands right next to each other. One is reading and the other updating the same record it just read, with INSERT and REPLACE at the end of each of the ten passes. So we know the SQLite lock cycle worked there.
Actually it won't matter to me, because I'll be using Postgres. I'll wait for the update in Run BASIC to use ODBC before going back to that. The managed DB's are a lot faster also. I've had SQL commands that run hours in SQLite run in minutes in managed DB's.
For most apps that don't have complex SQL, SQLite is great.
In the meantime I'm going to assume you have to do a disconnect and connect before each update and see how that works.
|
|
|
Post by metro on Mar 10, 2019 17:30:25 GMT -5
|
|