Long life cursors with SQL Server using the ODBC API

Carl Bruneau 136 Reputation points
2023-05-16T13:59:03.62+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,338 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 106.8K Reputation points
    2023-05-16T21:34:44.5833333+00:00

    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.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Olaf Helper 43,166 Reputation points
    2023-05-17T05:23:54.89+00:00

    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.

    0 comments No comments

  2. Carl Bruneau 136 Reputation points
    2023-05-17T12:36:01.6166667+00:00

    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.

    0 comments No comments