Transaction Timed Out

[myrights99]

Hello:

I am getting the following error, as shown below.

ClientError: {code: Neo.ClientError.Transaction.TransactionTimedOut} {message: The transaction has been terminated. Retry your operation in a new transaction, and you should see a successful result. The transaction has not completed within the specified timeout (dbms.transaction.timeout). You may want to retry with a longer timeout. }

From the below code, I am creating a new relationship to an existing node (alg:Allergy), and I am creating a new node (g). Any help is greatly appreciated.

CODE:-

p1subjectid = female['SUBJECT_ID']
#p1birthyear = female['BIRTH_YEAR']
p1age = female['AGE']
p1shellfish = female['SHELLFISH_ALG']
p1fish = female['FISH_ALG']
p1milk = female['MILK_ALG']
p1soy = female['SOY_ALG']
p1egg = female['EGG_ALG']
p1wheat = female['WHEAT_ALG']
p1peanut = female['PEANUT_ALG']
p1almond = female['ALMOND_ALG']
p1asth = female['NUM_ASTHMARX']
p1gen = female['Gender']
p1race = female['Race']
p1eth = female['Ethnicity']

# $p1birthyear[index] as p1birthyear, \

def create_multi_node(tx,p1subjectid,p1age,p1shellfish,p1fish,p1milk,p1soy,p1egg,p1wheat,p1peanut,p1almond,p1asth,p1gen,p1race,p1eth):
return tx.run(
"unwind range(0,size($p1subjectid)-1) as index \
with \
$p1subjectid[index] as p1subjectid, \
$p1age[index] as p1age, \
$p1shellfish[index] as p1shellfish, \
$p1fish[index] as p1fish, \
$p1milk[index] as p1milk, \
$p1soy[index] as p1soy, \
$p1egg[index] as p1egg, \
$p1wheat[index] as p1wheat, \
$p1peanut[index] as p1peanut, \
$p1almond[index] as p1almond, \
$p1asth[index] as p1asth, \
$p1gen[index] as p1gen, \
$p1race[index] as p1race, \
$p1eth[index] as p1eth \
merge(g:p1gen:Female{race:p1race, age:p1age, ethnicity:p1eth, asthma_RX:p1asth}) \
merge(alg:Allergy)<-[:HAS_ALLERGY{shellfish:p1shellfish, fish:p1fish,milk:p1milk,soy:p1soy,egg:p1egg,wheat:p1wheat,peanut:p1peanut, \
almond:p1almond}]-(g)",
p1subjectid=p1subjectid,p1age = p1age,p1shellfish = p1shellfish, \
p1fish = p1fish,p1milk = p1milk,p1soy = p1soy,p1egg = p1egg,p1wheat = p1wheat,p1peanut = p1peanut, \
p1almond = p1almond,p1asth = p1asth,p1gen = p1gen,p1race = p1race,p1eth = p1eth
)

session = driver.session(database="allergy")

# Execute the `create_mnc_node` "unit of work" within a write transaction
session.execute_write(create_multi_node, p1subjectid=p1subjectid,p1age = p1age,p1shellfish = p1shellfish, \
p1fish = p1fish,p1milk = p1milk,p1soy = p1soy,p1egg = p1egg,p1wheat = p1wheat,p1peanut = p1peanut, \
p1almond = p1almond,p1asth = p1asth,p1gen = p1gen,p1race = p1race,p1eth = p1eth)

[rouven_bauer]

Hi, try increasing the transaction timeout on the server side.

https://neo4j.com/docs/operations-manual/current/monitoring/transaction-management/#transaction-mana...

Alternatively or on top try batching your insertions, i.e., multiple transactions that each only push a (distinct) subset of the data to the server.

[myrights99]

Hello Rouven:

Thanks for your reply. Increasing time didn't help. I have to understand how I can do batching in Cypher.

[glilienfield]

In addition to what @rouven_bauer suggested, you may want to look at the query. You are merging a node with four parameters. This will require a full label scan to find a match. Do you expect to find a similar node in your data with that combination of four properties?  If not, or your intention is to create a new node for this data, then using ‘create’ instead of ‘merge’ should execute faster. Maybe that would avoid a timeout. How many rows are in your data frame? 

I have a similar comment for the second merge. You are merging while specifying seven relationship properties. Again, it will look for an existing relationship. Assuming the query planner starts at your node ‘g’ and expands from it, there will not be any relationships if ‘g’ is new. If your node ‘g’ is always created or the there is no chance there is going to be an existing relationship with that combination of seven parameters, you may also consider switching this merge to a ‘create’. 

[myrights99]

Hello Ninja:

Thanks for your reply. It worked on AuraDB but in sandbox the query is failing after changing from merge to create. getting this error message.

Transaction failed and will be retried in 1.0256386347801698s (There is not enough memory to perform the current task. Please try increasing 'dbms.memory.heap.max_size' in the neo4j configuration (normally in 'conf/neo4j.conf' or, if you are using Neo4j Desktop, found through the user interface) or if you are running an embedded installation increase the heap by using '-Xmx' command line flag, and then restart the database.)

I tried to change the heap size and got this error.

Failed to invoke procedure `dbms.setConfigValue`: Caused by: java.lang.IllegalArgumentException: Setting 'dbms.memory.heap.max_size' is not dynamic and can not be changed at runtime.

As Rouven mentioned about batching, I haven't tried. will do some digging how to batch.

Thank you again for your help.

[glilienfield]

You can use apoc.periodic.iterate or call subquery in transactions. 

https://neo4j.com/labs/apoc/4.1/overview/apoc.periodic/apoc.periodic.iterate/

https://neo4j.com/docs/cypher-manual/current/clauses/call-subquery/#_batching

with the apoc procedure you pass two queries, one get the data, and the second to execute your update statement in batches of rows from the first query. In your case, the first query would unwind your data and return it in rows.  The second query would process one tie. 

it’s very similar using call subquery. You would unwind your data into rows, then call the subquery to process one row. The call subquery needs to import each row with a ‘with’ clause. 

I can help further is you need help writing the query. 

[myrights99]

Hello:
What better way to put my above-shown code in "CALL apoc.periodic.iterate".

CALL apoc.periodic.iterate(
"MATCH (o:Order) WHERE o.date > '2016-10-13' RETURN o",
"MATCH (o)-[:HAS_ITEM]->(i) WITH o, sum(i.value) as value SET o.value = value",
{batchSize:100, parallel:true})

[glilienfield]

You nailed it.  I heard on a talk with Michael hunger that 10,000 batch size is good. I have read an article were performance degraded when the batch sized was too small. 

[myrights99]

Hello Ninja:

Thanks for your help. I am getting errors, also shown below. Variable is defined, then why is it still throwing the same error? Does this have to do with formatting?

ERROR:-

ClientError: {code: Neo.ClientError.Procedure.ProcedureCallFailed} {message: Failed to invoke procedure `apoc.periodic.iterate`: Caused by: org.neo4j.exceptions.SyntaxException: Variable `p1subjectid` not defined (line 1, column 38 (offset: 37))

"EXPLAIN          unwind range(0,size(p1subjectid)-1) as index           return           p1subjectid[index] as p1subjectid,          p1age[index] as  p1age,          p1shellfish[index] as p1shellfish,          p1fish[index] as p1fish,          p1milk[index] as p1milk,          p1soy[index] as p1soy,          p1egg[index] as p1egg,          p1wheat[index] as p1wheat,          p1peanut[index] as p1peanut,          p1almond[index] as p1almond,          p1asth[index] as p1asth,          p1gen[index] as p1gen,          p1race[index] as p1race,          p1eth[index] as p1eth"

                                      ^}

CODE:-

p1subjectid = female['SUBJECT_ID']

#p1birthyear = female['BIRTH_YEAR']

p1age = female['AGE']

p1shellfish = female['SHELLFISH_ALG']

p1fish = female['FISH_ALG']

p1milk = female['MILK_ALG']

p1soy = female['SOY_ALG']

p1egg = female['EGG_ALG']

p1wheat = female['WHEAT_ALG']

p1peanut = female['PEANUT_ALG']

p1almond = female['ALMOND_ALG']

p1asth = female['NUM_ASTHMARX']

p1gen = female['Gender']

p1race = female['Race']

p1eth = female['Ethnicity']

#        $p1birthyear[index] as p1birthyear, \

def create_multi_node(tx,p1subjectid,p1age,p1shellfish,p1fish,p1milk,p1soy,p1egg,p1wheat,p1peanut,p1almond,p1asth,p1gen,p1race,p1eth):

    return tx.run(

        "call apoc.periodic.iterate( ' \

        unwind range(0,size(p1subjectid)-1) as index   \

        return   \

        p1subjectid[index] as p1subjectid,  \

        p1age[index] as  p1age,  \

        p1shellfish[index] as p1shellfish,  \

        p1fish[index] as p1fish,  \

        p1milk[index] as p1milk,  \

        p1soy[index] as p1soy,  \

        p1egg[index] as p1egg,  \

        p1wheat[index] as p1wheat,  \

        p1peanut[index] as p1peanut,  \

        p1almond[index] as p1almond,  \

        p1asth[index] as p1asth,  \

        p1gen[index] as p1gen,  \

        p1race[index] as p1race,  \

        p1eth[index] as p1eth \

        ', \

        ' \

        create(g:p1gen:Female{race:p1race, age:p1age, ethnicity:p1eth, asthma_RX:p1asth})  \

        create(alg:Allergy)<-[:HAS_ALLERGY{shellfish:p1shellfish, fish:p1fish,milk:p1milk,soy:p1soy,egg:p1egg,wheat:p1wheat,peanut:p1peanut,  \

        almond:p1almond}]-(g) \

        ', \

        {batchSize:10000, parallel: true, params: {p1subjectid:$p1subjectid,p1fish:$p1fish,p1milk:$p1milk,p1soy:$p1soy,p1egg:$p1egg, \

        p1wheat:$p1wheat,p1peanut:$p1peanut,p1almond:$p1almond,p1asth:$p1asth,p1gen:$p1gen,p1race:$p1race,p1eth:$p1eth}} \

)",

        p1subjectid=p1subjectid,p1age = p1age,p1shellfish = p1shellfish, \

        p1fish = p1fish,p1milk = p1milk,p1soy = p1soy,p1egg = p1egg,p1wheat = p1wheat,p1peanut = p1peanut, \

        p1almond = p1almond,p1asth = p1asth,p1gen = p1gen,p1race = p1race,p1eth = p1eth

)

session = driver.session(database="allergy")

#session = driver.session(database="neo4j")

# Execute the `create_mnc_node` "unit of work" within a write transaction

session.execute_write(create_multi_node, p1subjectid=p1subjectid,p1age = p1age,p1shellfish = p1shellfish, \

                                p1fish = p1fish,p1milk = p1milk,p1soy = p1soy,p1egg = p1egg,p1wheat = p1wheat,p1peanut = p1peanut, \

                                p1almond = p1almond,p1asth = p1asth,p1gen = p1gen,p1race = p1race,p1eth = p1eth)

[glilienfield · Accepted solution]

Please try this first.  We can go from here if it doesn't work.

p1subjectidList = female['SUBJECT_ID']
#p1birthyearList = female['BIRTH_YEAR']
p1ageList = female['AGE']
p1shellfishList = female['SHELLFISH_ALG']
p1fishList = female['FISH_ALG']
p1milkList = female['MILK_ALG']
p1soyList = female['SOY_ALG']
p1eggList = female['EGG_ALG']
p1wheatList = female['WHEAT_ALG']
p1peanutList = female['PEANUT_ALG']
p1almondList = female['ALMOND_ALG']
p1asthList= female['NUM_ASTHMARX']
p1genList = female['Gender']
p1raceList = female['Race']
p1ethList = female['Ethnicity']
   
def create_multi_node(tx, p1subjectidLists, p1ageList, p1shellfishList, p1fishList, p1milkList, p1soyList, p1eggList, p1wheatList, p1peanutList, p1almondList, p1asthList, p1genList, p1raceList, p1ethList):
    return tx.run(
        "call apoc.periodic.iterate( \
         ' \
        unwind range(0,size(p1subjectidList)-1) as index   \
        return   \
        p1subjectidList[index] as p1subjectid,  \
        p1ageList[index] as  p1age,  \
        p1shellfishList[[index] as p1shellfish,  \
        p1fishList[index] as p1fish,  \
        p1milkList[index] as p1milk,  \
        p1soyList[index] as p1soy,  \
        p1eggList[index] as p1egg,  \
        p1wheatList[index] as p1wheat,  \
        p1peanutList[index] as p1peanut,  \
        p1almondList[index] as p1almond,  \
        p1asthList[index] as p1asth,  \
        p1genList[index] as p1gen,  \
        p1raceList[index] as p1race,  \
        p1ethList[index] as p1eth \
        ', \
        ' \
        create(g:p1gen:Female{race:p1race, age:p1age, ethnicity:p1eth, asthma_RX:p1asth})  \
        create(alg:Allergy)<-[:HAS_ALLERGY{shellfish:p1shellfish, fish:p1fish,milk:p1milk,soy:p1soy,egg:p1egg,wheat:p1wheat,peanut:p1peanut,  \
        almond:p1almond}]-(g) \
        ', \
        {batchSize:10000, parallel: true, params: {p1subjectidList:$p1subjectidList, p1fishList:$p1fishList, p1milkList:$p1milkList, p1soyList:$p1soyList, p1eggList:$p1eggList, \
        p1wheatList:$p1wheatList, p1peanutList:$p1peanutList, p1almondList:$p1almondList, p1asthList:$p1asthList, p1genList:$p1genList, p1raceList:$p1raceList, p1ethList:$p1ethList}} \
)",
        p1subjectidList = p1subjectidList, p1ageList = p1ageList, p1shellfishList = p1shellfishList, \
        p1fishList = p1fishList, p1milkList = p1milkList,p1soyList = p1soyList, p1eggList = p1eggList, p1wheatList = p1wheatList, p1peanutList = p1peanutList, \
        p1almondList = p1almondList, p1asthList = p1asthList, p1genList = p1genList, p1raceList = p1raceList, p1ethList = p1ethList 
)
 
session = driver.session(database="allergy")
#session = driver.session(database="neo4j")
 
# Execute the `create_mnc_node` "unit of work" within a write transaction
session.execute_write(create_multi_node, p1subjectidList = p1subjectidList, p1ageList = p1ageList, p1shellfishList = p1shellfishList, \
                                p1fishList = p1fishList, p1milkList = p1milkList, p1soyList = p1soyList, p1eggList = p1eggList, p1wheatList = p1wheatList, p1peanutList = p1peanutList, \
                                p1almondList = p1almondList, p1asthList = p1asthList, p1genvList = p1genList, p1raceList = p1raceList, p1ethList = p1ethList)

[myrights99]

Hello Ninja:

Thanks for your reply. I got the following error after running the code you gave.TypeError: create_multi_node() got an unexpected keyword argument 'p1subjectidList'

[myrights99]

I did tweak around formatting as shown below and got same error. based on error it seems the code is working.

ERROR:-

ClientError: {code: Neo.ClientError.Transaction.TransactionTimedOut} {message: The transaction has been terminated. Retry your operation in a new transaction, and you should see a successful result. The transaction has not completed within the specified timeout (dbms.transaction.timeout). You may want to retry with a longer timeout. }

CODE:-

p1subjectid = female['SUBJECT_ID']
#p1birthyear = female['BIRTH_YEAR']
p1age = female['AGE']
p1shellfish = female['SHELLFISH_ALG']
p1fish = female['FISH_ALG']
p1milk = female['MILK_ALG']
p1soy = female['SOY_ALG']
p1egg = female['EGG_ALG']
p1wheat = female['WHEAT_ALG']
p1peanut = female['PEANUT_ALG']
p1almond = female['ALMOND_ALG']
p1asth = female['NUM_ASTHMARX']
p1gen = female['Gender']
p1race = female['Race']
p1eth = female['Ethnicity']

# $p1birthyear[index] as p1birthyear, \

def create_multi_node(tx,p1subjectid,p1age,p1shellfish,p1fish,p1milk,p1soy,p1egg,p1wheat,p1peanut,p1almond,p1asth,p1gen,p1race,p1eth):
return tx.run(
"call apoc.periodic.iterate('unwind range(0,size($p1subjectid)-1) as index \
return \
$p1subjectid[index] as p1subjectid, \
$p1age[index] as p1age, \
$p1shellfish[index] as p1shellfish, \
$p1fish[index] as p1fish, \
$p1milk[index] as p1milk, \
$p1soy[index] as p1soy, \
$p1egg[index] as p1egg, \
$p1wheat[index] as p1wheat, \
$p1peanut[index] as p1peanut, \
$p1almond[index] as p1almond, \
$p1asth[index] as p1asth, \
$p1gen[index] as p1gen, \
$p1race[index] as p1race, \
$p1eth[index] as p1eth', \
'create(g:p1gen:Female{ID:p1subjectid, race:p1race, age:p1age, ethnicity:p1eth, asthma_RX:p1asth}) \
create(alg:Allergy)<-[:HAS_ALLERGY{shellfish:p1shellfish, fish:p1fish,milk:p1milk,soy:p1soy,egg:p1egg,wheat:p1wheat,peanut:p1peanut, \
almond:p1almond}]-(g)', \
{batchSize:10000, parallel: true, params: {p1subjectid:$p1subjectid,p1age:$p1age,p1shellfish:$p1shellfish,p1fish:$p1fish,p1milk:$p1milk,p1soy:$p1soy,p1egg:$p1egg, \
p1wheat:$p1wheat,p1peanut:$p1peanut,p1almond:$p1almond,p1asth:$p1asth,p1gen:$p1gen,p1race:$p1race,p1eth:$p1eth}})", \
p1subjectid=p1subjectid,p1age = p1age,p1shellfish = p1shellfish, \
p1fish = p1fish,p1milk = p1milk,p1soy = p1soy,p1egg = p1egg,p1wheat = p1wheat,p1peanut = p1peanut, \
p1almond = p1almond,p1asth = p1asth,p1gen = p1gen,p1race = p1race,p1eth = p1eth
)

session = driver.session(database="allergy")
#session = driver.session(database="neo4j")

# Execute the `create_mnc_node` "unit of work" within a write transaction
session.execute_write(create_multi_node, p1subjectid=p1subjectid,p1age = p1age,p1shellfish = p1shellfish, \
p1fish = p1fish,p1milk = p1milk,p1soy = p1soy,p1egg = p1egg,p1wheat = p1wheat,p1peanut = p1peanut, \
p1almond = p1almond,p1asth = p1asth,p1gen = p1gen,p1race = p1race,p1eth = p1eth)

[glilienfield]

Arg. What is your timeout set to?  

try tuning with half the batch size to see if a smaller batch size completes on time. 

[myrights99]

Hello Ninja:

Finally, it worked after trial-error.

The initial timeout was 1m, I changed it to 5m and it worked.

CALL dbms.setConfigValue("dbms.transaction.timeout", "5m")

Thank you for your all help and support.

Thank you again.

[glilienfield]

That seems like a crazy low timeout value.  Where did you learn about this? I don't see that config item in my configuration file.

Glad you finally got it to work.

[myrights99]

Hello Ninja:

I am doing testing on Neo4j Sandbox environment.

Below statement gives the current config and one below that helps you to set the prefer setting.

CALL dbms.listConfig()

YIELD name, value

WHERE name = 'dbms.transaction.timeout'

RETURN name, value

ORDER BY name

LIMIT 3;

CALL dbms.setConfigValue("dbms.transaction.timeout", "300s") -- "5m"

Thanks for your help again.

[glilienfield]

Interesting to know.  My local installation is set to '0', which probably means no timeout.  

thanks for the info...

[glilienfield]

Sorry, I named the parameter in the method incorrectly. Notice the 1st parameter’s name ends with Lists’. Remove the ‘s’ 

[myrights99]

With the help from Ninja & Rouven, below is the working code; I hope this style & formatting of code helps the community, using APOC function within Python function.

CODE:-

p1subjectid = female['SUBJECT_ID']
#p1birthyear = female['BIRTH_YEAR']
p1age = female['AGE']
p1shellfish = female['SHELLFISH_ALG']
p1fish = female['FISH_ALG']
p1milk = female['MILK_ALG']
p1soy = female['SOY_ALG']
p1egg = female['EGG_ALG']
p1wheat = female['WHEAT_ALG']
p1peanut = female['PEANUT_ALG']
p1almond = female['ALMOND_ALG']
p1asth = female['NUM_ASTHMARX']
p1gen = female['Gender']
p1race = female['Race']
p1eth = female['Ethnicity']

# $p1birthyear[index] as p1birthyear, \

def create_multi_node(tx,p1subjectid,p1age,p1shellfish,p1fish,p1milk,p1soy,p1egg,p1wheat,p1peanut,p1almond,p1asth,p1gen,p1race,p1eth):
return tx.run(
"call apoc.periodic.iterate('unwind range(0,size($p1subjectid)-1) as index \
return \
$p1subjectid[index] as p1subjectid, \
$p1age[index] as p1age, \
$p1shellfish[index] as p1shellfish, \
$p1fish[index] as p1fish, \
$p1milk[index] as p1milk, \
$p1soy[index] as p1soy, \
$p1egg[index] as p1egg, \
$p1wheat[index] as p1wheat, \
$p1peanut[index] as p1peanut, \
$p1almond[index] as p1almond, \
$p1asth[index] as p1asth, \
$p1gen[index] as p1gen, \
$p1race[index] as p1race, \
$p1eth[index] as p1eth', \
'create(g:p1gen:Female{ID:p1subjectid, race:p1race, age:p1age, ethnicity:p1eth, asthma_RX:p1asth}) \
create(alg:Allergy)<-[:HAS_ALLERGY{shellfish:p1shellfish, fish:p1fish,milk:p1milk,soy:p1soy,egg:p1egg,wheat:p1wheat,peanut:p1peanut, \
almond:p1almond}]-(g)', \
{batchSize:10000, parallel: true, params: {p1subjectid:$p1subjectid,p1age:$p1age,p1shellfish:$p1shellfish,p1fish:$p1fish,p1milk:$p1milk,p1soy:$p1soy,p1egg:$p1egg, \
p1wheat:$p1wheat,p1peanut:$p1peanut,p1almond:$p1almond,p1asth:$p1asth,p1gen:$p1gen,p1race:$p1race,p1eth:$p1eth}})", \
p1subjectid=p1subjectid,p1age = p1age,p1shellfish = p1shellfish, \
p1fish = p1fish,p1milk = p1milk,p1soy = p1soy,p1egg = p1egg,p1wheat = p1wheat,p1peanut = p1peanut, \
p1almond = p1almond,p1asth = p1asth,p1gen = p1gen,p1race = p1race,p1eth = p1eth
)

session = driver.session(database="allergy")
#session = driver.session(database="neo4j")

# Execute the `create_mnc_node` "unit of work" within a write transaction
session.execute_write(create_multi_node, p1subjectid=p1subjectid,p1age = p1age,p1shellfish = p1shellfish, \
p1fish = p1fish,p1milk = p1milk,p1soy = p1soy,p1egg = p1egg,p1wheat = p1wheat,p1peanut = p1peanut, \
p1almond = p1almond,p1asth = p1asth,p1gen = p1gen,p1race = p1race,p1eth = p1eth)