Long life cursors with SQL Server using the ODBC API

Question

Hi,

In our application we think of using what we can call long life cursors. These are application-side cursors (not server-side cursors) that will be openned by/for the end user and read according to the end user needs. If the end user don't scroll to the end of it, this cursor will not be read entirely and will not be closed. All non-closed cursors will be closed when the application will be closed. But if the application is not closed, this will lead to cursors openned for a long time.

Can/Will this design lead us into problems? What kind of problems?

Best regards.

Carl

Answer 1

I certainly get nervous when I read this. There is a risk that the user will hold locks on the rows that then could block writes unless the database is in read_committed_shapshot.

Also, I can't say that I find this a very modern design. I would rather read all rows into to memory once. If the dataset is large, let's say thousands of rows, I would consider paging.

Answer 2

Can/Will this design lead us into problems? What kind of problems?

To be true, that sounds more then a bad idea for an application design/architecture.

Do you have/plan high availability for your SQL Server in form of a cluster? Then your app will fail on a cluster fail-over.

Answer 3

Thanks to both of you for your valuable answers.

What I also expect with this poor design, since we are using read_committed_shapshot, is to see version ghost records accumulation since these "long life cursors" will open long read-only transactions.