Lock Timeout when creating index in ArangoDB

[zachk...@gmail.com]

I am able to connect to the coordinator of my ArangoDB cluster using the following:

sudo arangosh --server.endpoint tcp://10.32.0.15:1027

Everything works (CRUD, querying, etc.), except when I try to create an index I get a "Lock Timeout" error:

10.32.0.15:1027@_system> db.imdb_vertices.ensureIndex({ type: "skiplist", fields: ["name"] })
JavaScript exception in file '/usr/share/arangodb3/js/client/modules/@arangodb/arangosh.js' at 100,7: ArangoError 18: : lock timeout
!      throw error;
!      ^
stacktrace: ArangoError: : lock timeout
    at Object.exports.checkRequestResult (/usr/share/arangodb3/js/client/modules/@arangodb/arangosh.js:98:21)
    at ArangoCollection.ensureIndex (/usr/share/arangodb3/js/client/modules/@arangodb/arango-collection.js:738:12)
    at <shell command>:1:18

10.32.0.15:1027@_system>

I get the same error when I try to create the index over HTTP:

$ curl -X POST --data-binary @- --dump - http://10.32.0.15:1027/_api/index?collection=imdb_vertices << EOF
{
    "type" : "skiplist",
    "fields" : [
        "name",
        "genre"
    ]
}
EOF

HTTP/1.1 400 Bad Request
Content-Type: application/json; charset=utf-8
Server: ArangoDB
Connection: Keep-Alive
Content-Length: 71

{"error":true,"code":400,"errorNum":18,"errorMessage":": lock timeout"}

From the docs, I see "Will be raised when there's a timeout waiting for a lock." is listed as the cause of the error. However, I am unable to find a solution. Any hints?

Thanks!

(Cross posted from stack overflow here).

[m...@arangodb.com]

Are there any operations running on the database at the time when you try this? In particular long running queries or so might be relevant.

And: How long does it run before this error is returned?

[zachk...@gmail.com]

I don't believe so, but is there any way of checking is some process/query has been stalled on the database?

And it runs for 10 seconds before the lock timeout error.

[Frank Celler]

You execute 

127.0.0.1:8529@_system> require("@arangodb/aql/queries").current();

on the coordinator to check for running queries.

[zachk...@gmail.com]

Thanks! Looks like there are operations running. Are these easy to cancel somehow? Sorry for all the questions - I've been perusing the docs but can't find how to deal with this. Thanks for any help!

10.32.0.15:1027@_system> require("@arangodb/aql/queries").current();

[ 

  { 

    "id" : "12919", 

    "query" : "FOR s IN imdb_vertices FOR t IN imdb_vertices FILTER s._id < t._id LET p = SUM((...", 

    "started" : "2016-07-15T18:44:24Z", 

    "runTime" : 213152.8452320099, 

    "state" : "executing" 

  }, 

  { 

    "id" : "12912", 

    "query" : "FOR s IN imdb_vertices FOR t IN imdb_vertices FILTER s._id < t._id LET p = SUM((...", 

    "started" : "2016-07-15T18:43:27Z", 

    "runTime" : 213209.77132701874, 

    "state" : "executing" 

  } 

]

[zachk...@gmail.com]

Figured it out! require("@arangodb/aql/queries").kill(id)

[m...@arangodb.com]

Yes, this is the right method. It should get rid of the long running queries. It should also be possible to use the web UI for this.

Could you please post the complete query text? I would like to understand why it runs for such a long time, and whether this can somehow be optimized.

The beginning of the query suggests that the query might have a runtime proportional to the square of the number of vertices. How large is your graph?

[zachk...@gmail.com]

Since it's been a few days since I gave that query, I have a feeling that I Ctrl-C'ed or something and suspended the query, rather than it been running this whole time. But here it is in full:

db._query("FOR x in imdb_edges FILTER x.$label == \"DIRECTED\" COLLECT director = DOCUMENT(x._from), movie = DOCUMENT(x._to) FILTER director.name LIKE 'A%' AND movie.genre == 'Drama' RETURN director").toArray()

The attempt was to find all people whose name starts with A and have directed a drama.

On a related note, is there a better way to query a graph in this way? (i.e. find all vertices that meet certain conditions, rather than a traversal or shortest path). I may have been looking in the wrong places, but I couldn't find any examples of querying a graph directly - I believe that here I am not querying the graph, but rather the edge collection that is a part of the graph.

[m...@arangodb.com]

The query above started differently: 

    FOR s IN imdb_vertices FOR t IN imdb_vertices FILTER s._id < t._id LET p = SUM((...

Judging from this start the query might have complexity N^2 if N is the number of vertices.

As to your other query: This will have to build up all edges with label "DIRECTED" with their directors and movies and sort by director and movie. It will probably only then use the filter conditions. If a director has directed two dramas, I would expect it to return the director twice.

I think this can be done better: I think you should first run through the directors starting with an A and then use the traversal functionality. If your people are stored in the people collection and your movies in the movies collection, you could do:

    FOR person IN people 

      FILTER person.name LIKE 'A%' 

      FOR movie, edge IN 1..1 OUTBOUND person imdb_edges 

        FILTER edge.$label == 'DIRECTED' 

        FILTER movie.genre == 'Drama' 

        COLLECT p = person

          RETURN p

This can do a person whose name starts with an at a time and then use the graph functionality to find out whether this person has directed a drama. The collect now only has to deal with multiple dramas of a single person. I would imagine that this will run faster.

[zachk...@gmail.com]

Ah I see. I'm still learning how to formulate these queries, but that makes a lot of sense. Thanks!

[zachk...@gmail.com]

Just realized the query I posted in full is not the same one that had been running on the database for so long :/

Unfortunately, I really can't remember what that original query was - but yeah looking at it, definitely seems that it would be at least n^2.

[zachk...@gmail.com]

Just out of curiosity, is there a reason in your query you did

FILTER edge.$label == 'DIRECTED'
FILTER movie.genre == 'Drama'

rather than 

FILTER edge.$label == 'DIRECTED' AND movie.genre == 'Drama'

Or is that just a style thing?

[Simran Brucherseifer]

Multiple FILTER operations like that are AND combined, so really just code style:

https://docs.arangodb.com/3.0/AQL/Operations/Filter.html