SQL 3 Flashcards
Transactions
Units of work done on a database that may include many operations (Database Manipulation Language, DML)
ACID
Atomicity
Consistency
Isolation
Durability
Atomicity
Smallest logical unit that can be run.
All or nothing.
All operations execute successfully or rollback.
If we have bad atomicity, we can lose referential integrity.
Consistency
Database is in a valid state according to existing structure and constraints after a commit.
Isolation
Concurrent transactions do not affect each other.
One transaction must complete before another may execute, to sharing resources.
The system state during concurrent transactions is the same as if transactions were sequential.
Durability
If you shoot it, you have a backup.
Even in catastrophic failure of a database, the transaction will complete.
All commits are final and cannot be rolled back even in system failure.
Oracle 10g
Transaction Phenomenon
Issues that occur with concurrent transactions.
TYPES
Dirty Read
Non-Repeatable Read
Phantom Read
Dirty Read
returns something from a column/table that has not been committed.
Example
Session 1 - Begin transaction and modifies data
Session 2 - Begins with Session 1’s uncommitted data
If Session 1 rolls back, Session 2’s data is now invalid.
Non-Repeatable Read
Row is edited after being viewed · Session 1 begins TX, returns a row of data · Session 2 update same row and commit · Session 1 TX is still in progress, can’t same row · Example Session 1 Begin TX SELECT Name for VG WHERE Genre=RPG § Zelda and Dark Souls Session 2 Begin TX Update ID2 to Adventure Commit
Phantom Read
Query that does not return the same result when read again in a transaction. Example Session 1 Begins a TX Executes Query Session 2 Inserts data matching that Query Session 1 Re-runs that Query and finds something that wasn’t there before. Phantom Data has occurred.
Isolation Levels
Think of these as locks on our read-write permissions.
These are set up per database.
Need concurrent access to the database. Always assume thousands of people will be accessing your database concurrently.
Read-uncommitted
Read-committed (Oracle Default)
Repeatable Read
Serializable
Read Uncommitted
Terrible. No locks. ALLOWS FOR DIRTY READ Non-Repeatable Read Phantom Read
Read-committed (Oracle Default)
Oracle default.
It allows only committed data to be read, eliminating the dirty read problem.
Write Lock
ALLOWS FOR
Non-Repeatable Read
Phantom Read
DOES NOT ALLOW
Dirty read
Repeatable Read
Write and Read Lock
ALLOWS FOR
Phantom Read
DOES NOT ALLOW
Dirty read
Non-Repeatable Read
Serializable
Most secure, but very slow.
Banks often use these, since their information needs to remain highly secure.
Read, Write and Range Lock
DOES NOT ALLOW
Dirty read
Non-Repeatable Read
Phantom Read
Sequences
Stores and tracks a number that can be incremented/decremented.
Used for auto-incrementing primary keys
Can grab the current or next value (nextVal)
Good rule of thumb is to start with value 1001, so admins can use the smaller values for future purposes.
Sequences EXAMPLE
CREATE SEQUENCE[Seq name] MINVALUE[value] MAXVALUE[value] START WITH [value] INCREMENT BY [value] CACHE [value];
Cache
storing values in memory for faster access.
Views
Essentially a saved SELECT statement
Virtual table returned by Query
Considered a database object, although stored in memory only
If view definition does not have column names, names will be used from Query’s Result Set.v
If a table that view uses is dropped, the view becomes in accessible
View vs. Cursor
View restricts access/how much you can see. You can grant permission to a user to see parts of a table, for example. Cursor allows you to see everything.
Indices
Apply to column of a table to speed up searches by enforcing a physical in-memory order of rows
Searches are faster, but Insert/Delete statements are slowed down.
Triggers
Event listener for a DB Insert/Update/Delete on a specific table Get values of an operation and change them Before After
Functions
Block of code we can execute MUST return one and ONLY one value. You can have 0 or more parameters A function can call other functions Cannot call stored procedures No transactions - No data manipulation (though you can use SELECT statements/DQL)
Stored Procedures
Do not and cannot return a value. Support multiple IN, OUT AND IN/OUT PARAMETERS. Can call another Stored Procedure Transactions are okay DML is okay DDL is NOT okay
Cursors
View stored in a local variable
A pointer to a section of a table/result set.
It must be opened
It can be looped through
Must be closed
Every SQL statement generates a cursor, allowing PL/SQL to interact with SQL.
You cannot return a cursor from a function.
Stored Procedures can have as many OUT parameters as you want, but functions can only return one thing.
We use Cursors because they can be used as parameters. We can pass in a result set to a stored procedure. They’re a lot like iterators, but they aren’t actually iterators
TYPES
Implicit
Explicit
Explicit
CUR S = SELECT * FROM POKEMON;
SELECT ID FROM S;
Good way to format output.
Implicit
DQL Statements “SELECT”
SELECT * FROM POKEMON
System Reference cursors are already there for use, but we can make our own cursors, too.
There’s always a cursor implicitly created for result sets.
JDBC PreparedStatement
What we want to use instead of a regular Statement.
Pre-compiled (more efficient)
Safer against SQL injections
Safer, very efficient
SQL in Statement objects are compiled by Database and and SQL in PreparedStatement objects are compiled by Java.
JDBC CallableStatement
For executing Stored Procedures
String str = “{ call SP_FEED_BEAR(?, ?, ?)}”;
//Grab our CallableStatement from Connection
CallableStatement cs = conn.prepareCall(str);
cs. setInt(…); //Set your parameters
cs. setString(…);
cs. setInt(…);
cs. execute();
