|
|
Post by meerkat on Mar 14, 2019 9:11:28 GMT -5
Great work Carl!
I did a test with a program that used to lock in previous test. Since LB5 is single threaded there should be no locks. All SQL commands are serial so it goes through each command and completes it before the next command. No so on multi threaded, but in those cases most interfaces look for locks and reissue the SQL command on your behalf.
Only problem I had was when trying to change a field that was indexed. In that case SQLite not only has to update the record, but also the B-Tree.
Not sure this is a issue. The system reads 10 records 3 times. Each pass it updates the next 3 records it will be reading. It does not see the changes. Other managed DB's they do. I think SQLite already read the records and does not know they are being change. I can live with that if I know that's the rules.
This test does some nasty stuff. It does 3 passes and reads 10 records. As each record is read, 3 updates take place with 3 different connections. The last statement does a SUBSELECT to find the highest numbers updated.
You have the option to create a index on the aCount field or the record number (aNum). If you create the index on the aCount, the system goes away. The aCount field is the field that gets updated, and when you create a b-tree on it, It has problems..
Thanks for all the work on this Carl.
' ------------------------------------
' LB5 SQLite lock test with 4 cursors
' ------------------------------------
db$ = "test.db"
sqliteconnect #sql1, db$ ' Connect to the DB
sqliteconnect #sql2, db$ ' Connect to the DB
sqliteconnect #sql3, db$ ' Connect to the DB
sqliteconnect #sql4, db$ ' Connect to the DB
' ----------------------------------------------------------
' if the DB don't exist - create it and populate it
' ----------------------------------------------------------
confirm "Start with a fresh copy of DB";g$
if g$ = "yes" then
#sql1 execute("DROP TABLE tblA")
end if
sql1$ = "SELECT * FROM sqlite_master WHERE tbl_name = 'tblA'"
#sql1 execute(sql1$)
rows = #sql1 ROWCOUNT() 'Get the number of rows
print "sqlite_master Table has ";rows;" records"
if rows = 0 then
sql1$ = "
CREATE TABLE tblA (
aNum integer(5),
aDescr VARCHAR(10),
aCount integer(5))"
#sql1 execute(sql1$)
for i = 1 to 100
#sql1 execute("INSERT INTO tblA VALUES (";i;",'tblA_";right$("000"+str$(i),3);"',0)")
next i
confirm "Create index on aCount";g$
if g$ = "yes" then
#sql1 execute("CREATE UNIQUE INDEX tblAx ON tblA(aCount)")
print "Table A Created With aCount index - btree needs to update"
else
#sql1 execute("CREATE UNIQUE INDEX tblAx ON tblA(aNum)")
print "Table A Created With aNum index - btree not updated"
end if
end if
confirm "Continue";g$
print "-----------------------------------------------"
print "Initial look at 10 rows of table before updates"
print "i","Num","Descr","Count"
sql1$ = "SELECT * FROM tblA LIMIT 10"
#sql1 execute(sql1$)
rows1 = #sql1 ROWCOUNT() 'Get the number of rows
for i = 1 to rows1
#row1 = #sql1 #nextrow()
aNum = #row1 aNum()
aDescr$ = #row1 aDescr$()
aCount = #row1 aCount()
print i,aNum,aDescr$,aCount
next i
print "-------------------------------------------------------"
for pass = 1 to 3
print "---------------- Pass:";pass;" -----------------"
confirm "Continue:";g$
if g$ = "no" then end
print "i","Num","Descr","Count"
sql1$ = "SELECT * FROM tblA LIMIT 10"
#sql1 execute(sql1$)
rows1 = #sql1 ROWCOUNT() 'Get the number of rows
for i = 1 to rows1
#row1 = #sql1 #nextrow()
aNum = #row1 aNum()
aDescr$ = #row1 aDescr$()
aCount = #row1 aCount()
print i,aNum,aDescr$,aCount
sql2$ = "UPDATE tblA SET aCount = ";pass;" WHERE aNum = ";i
#sql2 execute(sql2$)
sql3$ = "UPDATE tblA SET aCount = ";pass;" WHERE aNum = ";i+1
#sql3 execute(sql3$)
sql4$ = "UPDATE tblA SET aCount = ";pass;" WHERE aNum = ";i+2
#sql4 execute(sql4$)
' ------------------------------------------------------------------
' Find the largest count that was updated in the prev sql Update commands
' ------------------------------------------------------------------
sql4$ = "SELECT * FROM tblA WHERE tblA.aCount = " _
+ "(SELECT max(ta.aCount) FROM tblA as ta)"
#sql4 execute(sql4$)
rows4 = #sql4 ROWCOUNT() 'Get the number of rows
#row4 = #sql4 #nextrow()
aNum = #row4 aNum()
aDescr$ = #row4 aDescr$()
aCount = #row4 aCount()
print "-----------SUBSELECT Count:"rows4
print i,aNum,aDescr$,aCount
print "-----------------END SubSelect -----------------"
next i
next pass
print "** End of Test **"
#sql1 disconnect()
#sql2 disconnect()
#sql3 disconnect()
#sql4 disconnect()
confirm "END:";g$
if g$ = "yes" then end
wait
end
|
|
|
|
Post by Carl Gundel on Mar 14, 2019 10:00:39 GMT -5
Great work Carl! I did a test with a program that used to lock in previous test. Since LB5 is single threaded there should be no locks. All SQL commands are serial so it goes through each command and completes it before the next command. No so on multi threaded, but in those cases most interfaces look for locks and reissue the SQL command on your behalf Thanks, but I'm not really sure if you explaining that something is working better, or if you're reporting a new bug. I didn't really change any SQLite related code aside from making it connect on Linux, and improving the connection closing code for Kill BASIC Programs. |
|
|
|
Post by Rod on Mar 14, 2019 10:42:39 GMT -5
Reading the SQLite docs it states .db are locked when an update takes place. Multiple reads fine but any update will lock the .db. So the .db is being locked, so you mean we should never see any locks. Sorry to be pedantic.
As we found if the program stops while the .db is being updated we get a lock that persist when the .db is abandoned by Liberty. Carl has helped us with that by somehow forcing a closure of the .db using Kill BASIC Programs.
When you talk about single threaded applications creating multiple connections to one .db surely we need to think about protection from locks. If one connection locks and holds the .db the second connection will find the file locked will it not? Now perhaps we will never see that conflict if we open and close the connection briefly for each update / query.
Opening and closing should never conflict because of the sequential processing. But I am pretty sure that if you hold open one connection and branch to the second connection we will find the .db locked. But perhaps SQLite has some tricks I don't know about. Perhaps it queues the updates itself to make them sequential. Perhaps this is why you don't see the updates?
What I would urge is to simplify the code testing the interface. Hold open one connection and try an update with the second as simply as you can. I have no doubt we can read as many connections as we can make but it is the multiple updates that will catch us out.
Simple understandable steps is where we are right now.
|
|
|
|
Post by meerkat on Mar 14, 2019 11:00:23 GMT -5
You mentioned you only changed it so we didn't have to open LB5 again.
Not sure what happened, but the above code locked before - now it does not lock.
There is a bug.. Not sure it's new since I don't think we tested this b4. If you place a index on a field that's being updating, it doesn't like it.
This program ask where you want to create the index.
If you index the aCount field it doesn't like it. That's the field that is being updated.
If you ask to index on the aNum field, it works fine.
Basically the program changes the aCount field. If it's not indexed it's ok. Index it and it has a problem.
Only difference when a field is indexed is that it builds a b-tree (balanced tree) that holds the index in sequence. So if you update a field that is indexed it needs to delete old entries in the b-tree and place them in the right sequence. Plus change the record.
Not sure the second issue is a problem. Example if you select 10 records in sequence from 1 to 10. When you read record 1, and immediately update record 2 and change it to 11, the next record listed is 2 even though it was changed to 11. In mySQL it immediately knows 2 was changed to 11 and the next record listed is 3 and 11 is at the end.
I think SQLite reads all the records in the buffer first and doesn't know 2 was changed. So may not be a problem but something we need to be aware of.
This program also updates the next 2 records in sequence, but the outer select loop don't know it.
So much was going on before I'm not sure what was working where. It's possible that maybe the code we tested before had a index on a field we were updating and that caused a problem. But I don't think so..
Hard to explain, and it's about as clear as mud..
|
|
|
|
Post by Carl Gundel on Mar 14, 2019 11:47:03 GMT -5
Yeah, you like to torture this stuff Dan. Mind you I'm not complaining. It's good to have torture testers because I'm not a database guy, and so every bit of SQL I've written has been simple enough that SQLite is happy to oblige me without a hiccup.  |
|
|
|
Post by meerkat on Mar 14, 2019 14:21:37 GMT -5
Believe it or not, these are some real needs I currently have.
I have some applications that need to read the file in certain sequences, and update the same records it's reading. One is a routing system that selects the best possible route by minimizing or maximizing based on user requirements. Kinda complex, but it works. This only has a few thousand records and may be able to run with SQLite.
Other systems are just big. Hundreds of Millions of records. Some SQL commands are over a page long and take hours to run.. I don't even want to try this with SLQite. It does updates while on-line so needs to be multi-threaded. Also this system must run 24/7/365 and downtime is not allowed. So we need replication..
If Run Basic had odbc, I'd give it a try.
|
|
|
|
Post by Carl Gundel on Mar 14, 2019 14:36:07 GMT -5
LB5 will have ODBC.
|
|
|
|
Post by meerkat on Mar 14, 2019 15:53:46 GMT -5
Will there be some kind of interface to get it on-line to the web.
The routing system is run in the warehouse, and only maybe 20 people are computing routes. Routes are constantly being added and completed. Another few hundred are looking at the results for schedules, costing, and what is going where.
If I can get it multi-tasked on the web, I'll go for it..
|
|
|
|
Post by Carl Gundel on Mar 14, 2019 16:00:28 GMT -5
Will there be some kind of interface to get it on-line to the web. The routing system is run in the warehouse, and only maybe 20 people are computing routes. Routes are constantly being added and completed. Another few hundred are looking at the results for schedules, costing, and what is going where. If I can get it multi-tasked on the web, I'll go for it.. There might be something clever that can be done, I'm not sure. It kills me that RB2 isn't ready because, well you know why because. It would be dumb for me to commit to any work on it until LB5 is actually done. |
|
|
|
Post by meerkat on Mar 14, 2019 17:00:15 GMT -5
I agree. However I prefer the clever with LB5 over getting ODBC on RB. But I'll take what I can get. Would be nice to have one language to do both web and real time.
I built a airplane a couple years ago and wrote some avionics. That's why I'm poor. It needs both web and real time. The web gets google maps/gps , airport, and other information, and real time gets the instruments, and drives some steppers.
Just tooo much fun.
|
|
|
|
Post by pierre on Apr 1, 2020 8:55:08 GMT -5
LB5 lock test with 4 cursors:
I found this old problem. Apparently no solution was ever posted. It is relatively simple, though. Nothing to do with differents threads. You have only to remove the word UNIQUE from the first Create Index line.
As we are entering the same values in the field 'aCount ' of the first 10 rows (first pass: 1, second pass 2, third pass 3) we cannot create a unique index for this column. For the field 'aNum' it works because the values of each row are different.
The program falls on the create index statement with a 'Unique constraint violation' (we just populated 100 rows for the aCount field with zeros - wich are the same values --> the program cannot create the unique index). Even when you fill in 100 times 'null' for example, the index will be created but the program falls on the second INSERT statement.
So, a simple CREATE INDEX works. That's all.
|
|
