Isolating database transactions

Application's ability to show the consistent data is based on the isolation level of a transaction.
Based on the level chosen,  you can get that degree of isolation for a database transaction from other concurent transactions. 

Below are the isolation levels supported by resource local transactions. 

ISOLATION LEVELS (More generic to More specific)
----------------
READ UNCOMMITTED 
READ COMMITTED  - Protects against dirty reads.
REPEATABLE READ - Protects against dirty and non-repeatable reads.
SERIALIZABLE  - Protects against dirty, non-repeatable and phantom reads

Dirty Read - Reading uncommitted Data

Non-Repeatable Read - Data is not the same for 2 different reads.
Reason: some other user runs a transaction and committed it.

Phantom Read - Though Data is same for 2 different reads in the same transaction. Fresh rows area added  or deleted. Phantom read will have impact while using range conditions('>' or '<' or 'between') 
or aggregate functions(sum or group)

Cost of high isolation:
READ COMMITTED is the default isolation used by the databases. however, this value can be changed based on the  application requirements. But, as we increase the level of isolation, the level of concurrency (max no. of parallel transactions/sec) will reduce since, DB applies locks to isolate the data from other transactions

Comments

Post a Comment

Popular posts from this blog

Distributed database design using CAP theorem

Image
CAP is a theorem/standard which can be applied while designing distributed systems like distributed databases. A distributed database is a set of databases(nodes) which are spread across the network.  These set of databases(nodes) in the distributed database can be either designed as either a  master/slave  or a  sharded model . Consistency -  Database should return the latest write on the DB. Availability -  Database should return data in a decent amount of time .  But, the data might not be latest. Partition tolerant - I n spite of n/w failure, the database should always be available. A distributed database can be designed either as a CP, AP, CA but not with all the 3 capabilities.  AP - Available and Partition tolerant  -  Sharded model (Data is distributed, No Master)  -      Availability  - Because, the data is distributed, any of the nodes can return the data. So, always availabl...
Continue Reading »

SQL Analytical Functions - Partition by (to split resultset into groups)

Partition by:  Splits the resultset into groups.  Employees -Table EMPLOYEE_ID FIRST_NAME SALARY ----------- -------------------- ---------- 100 Steven 24000 101 Neena 17000 102 Lex 17000 103 Alexander 9000 104 Bruce 6000 105 David 4800 106 Valli 4800 107 Diana 4200 select e.employee_id, e.department_id, salary, row_number() over (partition by department_id order by salary desc) as rownumber from employees e; The above query splits the result into small subgroups for every department.  Syntax :  Function(arg1,..., argn) OVER ( [PARTITION BY <..>] [ORDER BY <....>]  [<window_clause>] ) partition by  - create  subgroups for e...
Continue Reading »

Easy approach to work with files in Java - Java NIO(New input output)

  NIO - New IO package. Introduced in 1.4. It is buffer based and non-blocking. Hence, faster than IO package. It supports symbolic links, posix file attributes(works only on OS's which are POSIX compliant), ACL (Access control list). Paths and Files are the main classes Creating a potential file reference: Path path = Paths.get("c:/demo/"); //possible file reference Creating a directory, file, write to the file, read from the file: //create all the non existing directories in the path. Ignores creating a directory, if that directory already exists Path directories = Files.createDirectories("c:/demo/example1/properties/"); Path newFilePath = Files.createFile("c:/demo/README.txt"); //Creating a file //Writting bytes to the file Files.write(newFilePath, "This is a dummy text".getBytes(StandardCharsets.UTF_8)); //Reading bytes from file String readFile = new String(Files.readAllBytes(newFilePath), "UTF-8"); Files.readAllBytes reads all...
Continue Reading »