4

In my mind I always thought of a cursor in databases as a pointer used to access a result set. But I heard that there are isolation levels for cursors.

So perhaps a cursor is not just a pointer?
What exactly is then an SQL cursor that seems to have stability levels?

General Grievance
  • 4,555
  • 31
  • 31
  • 45
Jim
  • 18,826
  • 34
  • 135
  • 254
  • it's kinda `pointer` to a `select` result [example](http://arashmd.blogspot.com/2013/07/sql-server-t-sql-tips-tricks.html#cursor) –  Oct 14 '13 at 19:41

2 Answers2

1

SQL Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis

You may check this MYSQL Transaction

enter image description here

On a side note:-

Cursors have performance issues so try to avoid them as much as possible!

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • But we only use it when we access the db via application code e.g. Java? Because I don't think I have ever seen an example of a "raw" SQL statement using a CURSOR – Jim Oct 14 '13 at 19:41
  • There may be many more circumstances where you will find that cursors are used.! – Rahul Tripathi Oct 14 '13 at 19:45
  • It seems from your diagram that a cursor is some kind of auxiliary object for transactions.So how is it related with isolation levels, stability etc? – Jim Oct 14 '13 at 19:46
  • Athough its from IBM but I dont think that there may any different reasons then this:- Like levels RR and RS, level Cursor Stability (CS) ensures that any row that was changed (or a row that is currently locked with an UPDATE row lock) by another activation group using a different commitment definition cannot be read until it is committed. Unlike RR and RS, level CS only ensures that the current row of every updatable cursor is not changed by other activation groups using different commitment definitions. ......continued – Rahul Tripathi Oct 14 '13 at 19:56
  • Thus, the rows that were read during a unit of work can be changed by other activation groups that use a different commitment definition. In addition to any exclusive locks, an activation group running at level CS may acquire a share lock for the current row of every cursor. Source: http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=%2Fdb2%2Frbafzmstisol.htm – Rahul Tripathi Oct 14 '13 at 19:57
  • @Jim:- Hope that help? – Rahul Tripathi Oct 14 '13 at 19:57
  • To be honest I am not sure I understand what this article is talking about (especially about updatable cursor and activation group) – Jim Oct 14 '13 at 20:00
  • 1
    As a side note Cursors are slow and should be avoided when at all possible – Brandon Johnson Oct 14 '13 at 20:05
  • @BrandonJohnson:- Thanks I was about to write this and enter the key and found your comment! ;) – Rahul Tripathi Oct 14 '13 at 20:05
0

Cursor is a pointer in database engine space(object or number). So it is identificator which can be used to got your query(already done, but not fetched) from sql engine.

For example for simple query like select * from somebigtable cursor will be point to list structure of rows headers, and rows will be fetched from disks only when you ask it.

arheops
  • 15,544
  • 1
  • 21
  • 27