Row scan high memory usage and does not complete the process

Getting Help Code Review
1

Hi,
I have done my full codes here Go Playground - The Go Programming Language. What I do here is a run a timer and run a processData on every interval but now I limit to 5 loops. The problem it first run a sql query then in that I scan and I run another query. But what I notice is that is takes high memory and cpu usage. Thereafter it gets killed. Surprisingly its just 2 select statement but what could be wrong or need to be tweak which I think is on the row.scan cause when I just run the query it runs perfectly.

2

Hi there, “Newbie”

I suspect the problem is your usage of defer rows.Close() in nested queries. The defer statement essentially appends a cleanup function that’s executed when the function returns. If you’re iterating over a lot of rows, that list can get extremely large and hold references to unclosed result sets and/or other resources from the inner loop.

Izca from StackOverflow answered a related question with some good examples here: go - Proper way to release resources with defer in a loop? - Stack Overflow.

The simplest one that I would suggest trying is to wrap your code inside of your for rows1.Next() loop into an anonymous function closure:

    for rows1.Next() {
        func() {
            // ...
            defer rows2.Close()
            //
        }()
    }

This way the defer is after every inner loop.

3

Hi Sean,
Yes I am trying over a huge rows you are right. In fact before going further I wanted to check with you on this

rows1, errStmt1 := db.Query(" Select test1.aTID as aTID,test1.vtestID as gdvID,test1.latitude as glat,test1.longitude as glong,test1.eOn as acc,test1.gDateTime as mysqlDateTime,test1.speed as intSpeed,test1.direction as intDirection,test1.flLevel"+
                           " From test1 "+                                       
                           " Order By test1.gDateTime Asc")
        if errStmt1 != nil {
                fmt.Println("Error errStmt1 1", errStmt1)
        }
        defer rows1.Close()       

        for rows1.Next() {
                    var test11 test1
                var errScan1 error
                errScan1 = rows1.Scan(&test11.aTID,&test11.gdvID,&test11.glat,&test11.glong,&test11.acc,&test11.mysqlDateTime,&test11.intSpeed,&test11.intDirection,&test11.flLevel)
                if errScan1 !=nil {
                        fmt.Println("Error errScan1 1", errScan1)
                }
        } 
        if errRows1 := rows1.Err(); errRows1 != nil {
                fmt.Println("Error errRows1 1", errRows1)
        }
        rows1.Close()

I wanted to ask if at this first level without the nested is that right or is there possible for memory leak from this level ? The issue is that even with out the inner level query just this outer query I tend to get this error.

Error errStmt1 1 sql: database is closed
panic: runtime error: invalid memory address or nil pointer dereference
        panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x0 pc=0x58e2ae]
goroutine 20 [running]:
database/sql.(*Rows).close(0x0, {0x0, 0x0})
        /usr/lib/golang/src/database/sql/sql.go:3308 +0x8e
database/sql.(*Rows).Close(0xc000485be8?)
        /usr/lib/golang/src/database/sql/sql.go:3304 +0x1d
panic({0x60a5e0, 0x7c6950})
        /usr/lib/golang/src/runtime/panic.go:838 +0x207
database/sql.(*Rows).Next(0x0)
        /usr/lib/golang/src/database/sql/sql.go:2985 +0x27
main.processData()
4

I’m not sure that this is the problem, but you’re calling rows1.Close twice: Once in the defer statement and another after your for rows1.Next() loop.

5

Hi Sean,
I tried to close first rows1.Close() at the last line there I got this error


Error errStmt1 1 sql: database is closed

Then I commented this defer rows1.Close() and open this line rows1.Close() . Then I got error

Error errStmt1 1 sql: database is closed
panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x0 pc=0x58d767]

goroutine 22 [running]:
database/sql.(*Rows).Next(0x0)
        /usr/lib/golang/src/database/sql/sql.go:2985 +0x27
main.processData()

Neither way it works what could be the issue ? After all it just a select statement right?