how database handle request?

[albert sie]

Hi friend,

I want to write a program that can access to multiple database based on the user ID, but I dont know how the database handle user request. Please help me...

if user A execute program and redirect to database abc, and user B also accessing it. user A retrieve value from database but not yet update it, at the same time user B go get the value. How can I control it? can I code a program that control if A not yet finish, we cannot allowed user B to retrieve the value?

I cannot use Java synchronization. if I use it, it will synchronize for all the request although the request is redirect to different database(I am using many databases but sharing 1 program). There is no point to do synchronization if another user accessing other database that is no user at that moment but still need to wait user at different database finish their tasks first.

[Maximilian Xavier Stocker]

This is one part of what makes database transactions useful. The part you are interested in appears to be the transaction level. There are multiple levels of transactiosn supported by database. (You can get a quick overview of what's available by looking at the constants in java.sql.Connection).

What your database actually supports I do not know because you haven't identified what database you are using.

[Stan James]

Look up "isolation levels" in your database documentation. The db vendors have different names for the same concepts and someplace the doc will tell you which ones your db supports and how they map to the standard JDBC words.

Here are some notes on isolation & DB2. Your db may be similar.

Isolation Levels in DB2

Repeatable Read (RR) locks all the rows an application references within a unit of work.

Read Stability (RS) locks only those rows that an application retrieves within a unit of work

Cursor Stability (CS) locks any row accessed by a transaction of an application while the cursor is positioned on the row.

Uncommitted Read (UR) does not lock other applications out of the row it is reading, unless the other application attempts to drop or alter the table

Here is a great article from DB2 Universal Database Online Information: https://aurora.vcu.edu/db2help/db2d0/frame3.htm#integ

Levels in JDBC and how they relate to DB2 levels

JDBC           DB2
SERIALIZABLE   Repeatable read
REPEATABLE READ   Read stability
READ COMMITTED    Cursor stability
READ UNCOMMITTED  Uncommitted read

How do the DB2 levels affect data?

Isolation Level Access to uncommitted data Nonrepeatable reads Phantom read phenomenon
Repeatable Read (RR) Not possible Not possible Not possible
Read Stability (RS) Not possible Not possible Possible
Cursor Stability (CS) Not possible Possible PossibleIsolation Level         Access uncommitted data  Nonrepeatable reads  Phantom read
Repeatable Read (RR)    Not possible             Not possible         Not possible
Read Stability (RS)     Not possible             Not possible         Possible
Cursor Stability (CS)   Not possible             Possible             Possible
Uncommitted Read (UR)   Possible                 Possible             Possible
 
Uncommitted Read (UR) Possible Possible Possible

[ May 01, 2006: Message edited by: Stan James ]