Database Transaction Concurrency

DDL(Data Definition Language)

  • CREATE – is used to create the database or its objects (like table, index, function, views, store procedure and triggers).
  • DROP – is used to delete objects from the database.
  • ALTER-is used to alter the structure of the database.
  • TRUNCATE–is used to remove all records from a table, including all spaces allocated for the records are removed.
  • COMMENT –is used to add comments to the data dictionary.
  • RENAME –is used to rename an object existing in the database.

DML(Data Manipulation Language)

  • SELECT – is used to retrieve data from the a database.
  • INSERT – is used to insert data into a table.
  • UPDATE – is used to update existing data within a table.
  • DELETE – is used to delete records from a database table.

 

Transaction concurrency Isolation & Lock

  • One transaction run DML, then the other transactions won’t be able to see the changes until it’s committed.
  • One transaction run DML, it would put a lock on the data set. So the other transactions won’t be able to apply changes until the first transaction commits the changes which would release the lock.

 

SELECT username, osuser, SID, serial#, lockwait, status, MODULE, machine, program FROM v$session;

This query would return all the transactions. Lockwait would has a number if there is a lock applied.

 

Advertisements

SQL Cursor

Cursor was created for processing one record on a time.

Before you use a CURSOR, you’ll need to declare it.  The basic form looks like:

DECLARE cursorName CURSOR for
 SELECT field1, field2 FROM myTable
 WHERE whereClause

Once we have the cursor declared, we need to OPEN it to cause it to execute:

OPEN cursorName

The rest is going to look very similar to what we did back when we were using ODBC.  To move to the first record, we have to FETCH the row:

FETCH NEXT FROM cursorName
 INTO @field1, @field2

There is a special variable in SQL called @@FETCH_STATUS that will be zero (0) as long as the fetch was able to retrieve a row, so you’ll want to set up a while loop after your first fetch that checks the status and then processes the data.  Right before the end of the while loop you will issue another FETCH.

(SQL Server) FETCH NEXT FROM cursorName
 INTO @field1, @field2
 While @@FETCH_STATUS = 0
 Begin
  /* do something with the data here */
  FETCH NEXT FROM cursorName
   INTO @field1, @field2
 End 

Syntax DB2:

DECLARE cursor-name INSENSITIVE (DYNAMIC)SCROLL CURSOR WITH HOLD FOR Select Statement FOR UPDATE OF FOR READ ONLY 
Insensitive - Once a cursor is opened it does not have any sensitivity to inserts, updates or adds performed by this or any other activation group.
Scroll - The cursor is scrollable, you can get rows in reverse order and reposition to where your next read will occur.
Dynamic Scroll - The cursor is update-able.
With Hold - Prevents the cursor from being closed as a consequence of a commit operation. 
 (when join multiple tables, it will be read only by default.)

FETCH Next FROM cursor-name (single fetch) INTO Host Variable/Structure
      Prior (multiple row fetch) FOR Host Variable or Integer ROWS INTO Host Structure Array 
 First 
 Last
 Before
 After
 Current
 Relative - host variable or integer