the lock issue, when streaming data write into a table, run the long-time sql

xiao321 commented 5 years ago

https://github.com/RedBeardLab/rediSQL/issues/66#issuecomment-507596085

Does this timeout?

I need to fix the timeout as well.

What I believe happens, is that the query takes more than 10sec, RediSQL timeout, and it replies to you with an empty response. However, eventually the query terminates, and it tried to reply, but the internal state is "corrupted" by the previous timeouted reply, so the thread crash. Only the thread crash so RediSQL keep running and you just see something that stop working.

Can you confirm this with the debug build? Just post the logs if you can reproduce it.

i test this on two scenarios. a table with 4000k data，execute the command "delete from a where id>2000000 and id<3000000", then my client get the error " read timeout". but the database can work, only "10s" timeout redis log:

[2019-07-02T07:35:44Z DEBUG redisql_lib::redis] Exec | Query = "delete from a where id>2000000 and id<3000000" [2019-07-02T07:35:44Z DEBUG redisql_lib::redis] do_execute | created statement [2019-07-02T07:35:44Z DEBUG redisql_lib::community_statement] Execute | Acquired db lock [2019-07-02T07:35:44Z DEBUG redisql_lib::community_statement] Execute | Read row modified before [2019-07-02T07:35:54Z DEBUG redisql_lib::sqlite] FromIterator => DONECursor [2019-07-02T07:35:54Z DEBUG redisql_lib::community_statement] Execute=> Executed trains of statements [2019-07-02T07:35:54Z DEBUG redisql_lib::community_statement] Execute=>DONECursor [2019-07-02T07:35:54Z DEBUG redisql_lib::redis] do_execute | statement executed [2019-07-02T07:35:54Z DEBUG redisql_lib::redis_type] Free thread safe context [2019-07-02T07:35:54Z DEBUG redisql_lib::redis] RedisContextSet | Drop [2019-07-02T07:35:54Z DEBUG redisql_lib::redis] with_contex_set | exit [2019-07-02T07:35:54Z DEBUG redisql_lib::redis] Exec | DONE, returning result [2019-07-02T07:35:54Z DEBUG redisql_lib::redis] Loop iteration

another : a table with 4000k data , when i execute the command "delete from b where id>1000000 and id<2000000", the streaming data always write into the table. my client get the error " timeout". but the database can not work.

the redis log: [2019-07-02T08:55:17Z DEBUG redisql_lib::redis] Exec | Query = "delete from b where id>=1000000 and id<2000000" [2019-07-02T08:55:17Z DEBUG redisql_lib::redis] do_execute | created statement [2019-07-02T08:55:17Z DEBUG redisql_lib::community_statement] Execute | Acquired db lock [2019-07-02T08:55:17Z DEBUG redisql_lib::community_statement] Execute | Read row modified before [2019-07-02T08:55:17Z DEBUG redis_sql::commands] Exec | GotDB [2019-07-02T08:55:17Z DEBUG redis_sql::commands] Exec | BlockedClient [2019-07-02T08:55:17Z DEBUG redis_sql::commands] Exec | Create Command [2019-07-02T08:55:28Z DEBUG redis_sql::commands] Exec | GotDB [2019-07-02T08:55:28Z DEBUG redis_sql::commands] Exec | BlockedClient [2019-07-02T08:55:28Z DEBUG redis_sql::commands] Exec | Create Command [2019-07-02T08:55:28Z DEBUG redisql_lib::sqlite] FromIterator => DONECursor [2019-07-02T08:55:28Z DEBUG redisql_lib::community_statement] Execute=> Executed trains of statements [2019-07-02T08:55:28Z DEBUG redisql_lib::community_statement] Execute=>DONECursor [2019-07-02T08:55:28Z DEBUG redisql_lib::redis] do_execute | statement executed [2019-07-02T08:55:28Z DEBUG redisql_lib::redis_type] Free thread safe context [2019-07-02T08:55:28Z DEBUG redisql_lib::redis] RedisContextSet | Drop [2019-07-02T08:55:28Z DEBUG redisql_lib::redis] with_contex_set | exit [2019-07-02T08:55:28Z DEBUG redisql_lib::redis] Exec | DONE, returning result [2019-07-02T08:55:28Z DEBUG redisql_lib::redis] Loop iteration

why? lock compete lead to?

siscia commented 5 years ago

Can you believe that I can't reproduce the issue?

I am able to send the database in timeout, but then, after the timeout, it keep working.

Can you please try to post reproducible examples?

Here one of my attempts:

127.0.0.1:6379> redisql.create_db DB
OK
127.0.0.1:6379> REDISQL.exec DB "create table t(id, data,b,c,d,e);"
1) DONE
2) (integer) 0
127.0.0.1:6379> REDISQL.exec DB "INSERT INTO t WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<14000000) SELECT  cnt.x, random() % 100000000, randomblob(21), randomblob(31), randomblob(26), random() % 1000 FROM c
nt;"
(nil)
(10.03s)
127.0.0.1:6379> REDISQL.exec DB "select 1;"
1) 1) (integer) 1
127.0.0.1:6379> REDISQL.exec DB "DELETE FROM t where e>200 and e<800"
1) DONE
2) (integer) 4194334
(3.21s)

It is possible that you are just not waiting long enough?

Do you think we should abort the query if it take more than the timeout?

xiao321 commented 5 years ago

when i execute the delete sql, the streaming data not stop to write. so i have two clients. one write , another query. i have set the maxWaitMillis in my code.

client a: GenericObjectPoolConfig genericObjectPoolConfig = new GenericObjectPoolConfig(); genericObjectPoolConfig.setMaxIdle(10); genericObjectPoolConfig.setMinIdle(10); genericObjectPoolConfig.setMaxWaitMillis(-1); //"-1" means no limit. JedisPool jedisPool = new JedisPool(genericObjectPoolConfig, ip, port); Jedis jedis = jedisPool.getResource(); int id = 1; while(true){ jedis.executeSQL("test", "insert into a(id,device_id,cert_id) values("+id+",0,0)"); id++; }

client b: GenericObjectPoolConfig genericObjectPoolConfig = new GenericObjectPoolConfig(); genericObjectPoolConfig.setMaxIdle(10); genericObjectPoolConfig.setMinIdle(10); genericObjectPoolConfig.setMaxWaitMillis(-1); JedisPool jedisPool = new JedisPool(genericObjectPoolConfig, ip, port); Jedis jedis = jedisPool.getResource(); List

RedBeardLab / rediSQL

the lock issue, when streaming data write into a table, run the long-time sql #69