copyright © 2005, sas institute inc. all rights reserved. jdbc, sas and multi-user update mitchel...
TRANSCRIPT
Copyright © 2005, SAS Institute Inc. All rights reserved.
JDBC, SAS and Multi-user UpdateMitchel SoltysManagerOpen Data Access
Copyright © 2005, SAS Institute Inc. All rights reserved.
Goal of Presentation Help you understand and accomplish multi-user
update of SAS data using JDBC.
Approach Overview of multi-user update.
Describe how to do multi-user update with JDBC.
Discuss some common issues in multi-user update.
Assumption Basic familiarity with java/JDBC and SAS.
Copyright © 2005, SAS Institute Inc. All rights reserved.
Questions People Ask1. Does the JDBC driver have any built in record
locking? (What do I have to do to maintain data integrity?)
2. I’m using SHARE and I’m getting a “lock held by another process error”. I thought SHARE was a multi-user server. Why am I getting this error?
3. Can you do multi-user update with the Workspace Server via JDBC?
4. Resubmitting an update seems inelegant, is there anything better I can do?
Copyright © 2005, SAS Institute Inc. All rights reserved.
Difficulty of Analyzing Multi-user Update Different usage patterns.
Different behavior requirements.
Different timings.
Different configurations.
Copyright © 2005, SAS Institute Inc. All rights reserved.
User 1 User 2
Multi-user Update : What could happen?
Table(contact)
Bill Sarah
Copyright © 2005, SAS Institute Inc. All rights reserved.
Sarah
Bi
Multi-user Update : What could happen?
Action Table
User 1
User 2
User 1
In a multi-user update situation, conflicts can arise.
There needs to be some kind of way to resolve update conflicts.
Sallh
Copyright © 2005, SAS Institute Inc. All rights reserved.
User 1 User 2Table
Update Update
Failure
Successful Multi-user Update in Action.
Update
Success
Success
What should be done? (2 users can’t update the same data at the same time.)
Lock data for one user and allow the other to fail (exception or warning).
User 1 User 2
Table
Copyright © 2005, SAS Institute Inc. All rights reserved.
User 1 User 2
Simultaneous UpdateData
User 2
Even though we talk about users updating data at the same time, all data update is actually sequential, because two users can’t be updating the exact same data at the same time.
Copyright © 2005, SAS Institute Inc. All rights reserved.
JDBC Under the Covers
Engine
SQL
JDBC
Dataset open.
Dataset update.
Dataset close.
Copyright © 2005, SAS Institute Inc. All rights reserved.
1. Does the JDBC driver have any built in record locking? (What do I have to do to maintain data integrity?)
Yes. SAS ensures the integrity of individual data updates.
Locking is implicit in JDBC.
As long as you’re checking for exceptions and warnings and resubmitting the executeUpdate conflicts are resolved like we’ve described.
Copyright © 2005, SAS Institute Inc. All rights reserved.
What’s in the Code Use statement.executeUpdate
UPDATE foo.table SET val = 10 WHERE …
Use a try catch block.
Check for warnings.
Copyright © 2005, SAS Institute Inc. All rights reserved.
Sample JDBC Codetry{
statement.clearWarnings();
numRowsAffected = statement.executeUpdate(…);
w = statement.getWarnings();
while(w != null){
w = w.getNextWarning();
}
} catch(Exception e){
return false;
}
Copyright © 2005, SAS Institute Inc. All rights reserved.
Resubmittingint count = 0;
while(!UpdateData(sqlString)){
count++;
if(count > 200){
//output message
return false;
}
}
Copyright © 2005, SAS Institute Inc. All rights reserved.
Errors and Warnings
Copyright © 2005, SAS Institute Inc. All rights reserved.
2. I’m using SHARE and I’m getting a “lock held by another process error”. I thought SHARE was a multi-user server. Why am I getting this error?
JDBC
Table
SHARE P2
Copyright © 2005, SAS Institute Inc. All rights reserved.
A Warning to Watch.numRowsAffected = statement.executeUpdate(…);
Imagine you manufacture computers and the price of certain video cards just went up.
You want to update the price of models using that video card.
Suppose 4 rows (models) actually match the where clause in your executeUpdate statement, but numRowsAffected is returned as 3.
Would you know there’s a mismatch?
What does that mean?
Warning: “ … ERROR lock held by …” (SHARE)
Copyright © 2005, SAS Institute Inc. All rights reserved.
Connection Error SQLException: “… driver could not open socket
…connection refused”.
Use a try catch block and try to connect again.
Copyright © 2005, SAS Institute Inc. All rights reserved.
User 1 User 4
Looking at an Example
Table(Balance)
+50 -85
User 3
+100
User 2
-25
Copyright © 2005, SAS Institute Inc. All rights reserved.
Execute Updatetry{
statement.executeUpdate(…);
} catch (Exception e){
return false;
}
SET balance = balance + myUpdateValue
Copyright © 2005, SAS Institute Inc. All rights reserved.
Execute Update
+100Balance-25
Success (25)
executeUpdate(B-25)
50
executeUpdate(B+100)
Success (125)
executeUpdate(B+100)
Failure
Balance is correct.
Updates are resolved properly.
Works no matter when updates are applied.
Copyright © 2005, SAS Institute Inc. All rights reserved.
What about Query then Update?result = statement.executeQuery(…);
balance = result.getDouble(…);
balance += myUpdateValue;
statement.executeUpdate(“…SET balance …”);
Approach common among interactive apps.
Query and Update may be close or widely separated in time.
This is fine in a single user context. Is it ok in a multi-user context?
No, the change is not atomic.
Copyright © 2005, SAS Institute Inc. All rights reserved.
What can happen? +100Balance
executeQuery
-25
executeQuery
50
executeUpdate(25)
50
Success
executeUpdate(150)
Success
50
Which would you rather have $25 or $125?
Copyright © 2005, SAS Institute Inc. All rights reserved.
1. Does the JDBC driver have any built in record locking? (What do I have to do to maintain data integrity?)
Yes. SAS ensures the integrity of individual data updates.
Locking is implicit in JDBC.
As long as you’re checking for exceptions and warnings and resubmitting the executeUpdate conflicts are resolved like we’ve described.
JDBC does not prevent others from updating between a query and update.
Copyright © 2005, SAS Institute Inc. All rights reserved.
Can Query be used?
Yes it can, but remember others may have updated the data since your query occurred. (Multi-user)
Updates not based on query are preferred.
Updating based on query? Use WHERE to check for changes.
Copyright © 2005, SAS Institute Inc. All rights reserved.
Understanding Access at a System Level We’ve seen how to do multi-user access in JDBC.
How does your system affect multi-user access.
Copyright © 2005, SAS Institute Inc. All rights reserved.
JDBC and Servers One of the active research project my group is
involved in at SAS is to provide transaction support for SAS data. That project will result in a new SAS server with JDBC support.
Currently, JDBC is able to access SAS through the SHARE Server and the Workspace Server.
The JDBC code we’ve discussed applies equally whether you’re accessing data through a SHARE Server or a Workspace Server.
Internal workings of the servers are a bit different.
Copyright © 2005, SAS Institute Inc. All rights reserved.
Member Level Locking Entire table is locked.
No other readers or updaters can access the table while it is locked.
You might think of this as “table locking”.
Record Level Locking Only the record being updated is locked.
Readers and updaters can access other records in the same table.
Copyright © 2005, SAS Institute Inc. All rights reserved.
Configuration
User 1
Table
SHARE
User 2
Without a single coordinating process, record locking looks like member locking for update.
The OS coordinates access in the multi-process scenario.
User 1
Table
WS
User 2
WS
Copyright © 2005, SAS Institute Inc. All rights reserved.
3. Can you do multi-user update with the Workspace Server via JDBC?
Yes! And response time is very good in most situations.
With JDBC the table will be locked during update, but only for a very short time.
User 2TableUser 1
Success
executeUpdate
executeUpdate
Success
Copyright © 2005, SAS Institute Inc. All rights reserved.
What about performance?
Collisions are what affect performance.
In many, cases JDBC to a Workspace Server and JDBC to a SHARE Server will be similar, because lock time is small and collisions are rare.
If updaters are numerous, collisions are more likely and access through SHARE may be able to help.
User 2DataUser 1
executeUpdate executeUpdate
Copyright © 2005, SAS Institute Inc. All rights reserved.
User 1 User 2
Record Locking and SHARE
SHARE coordinates record locking allowing multiple users to access the table at the same time.
User 1 User 2
SHARE
Copyright © 2005, SAS Institute Inc. All rights reserved.
Rec N
User 2User 1
Update
Record Locking and SHARE
Collisions only occur when accessing the same record.
Actual update is still sequential (one writer), but collision overhead is significantly reduced.
File open overhead is reduced (only one physical open).
Success
Rec M
Update
Success
SHARE
Copyright © 2005, SAS Institute Inc. All rights reserved.
Does Record Level Locking Guarantee Good Response Time?
No.
It can help if there are many collisions when updating different records.
System level behavior can have a big impact on performance. (ie watch out for other apps, esp. non-JDBC apps)
Copyright © 2005, SAS Institute Inc. All rights reserved.
FSEdit
FSEdit Record
Move to Record
Uses record level locking.
It’s intended to improve response time. People think of this as fast.
Table can appear locked from perspective of other processes.
Record/Table is locked for an arbitrarily long time (sec, min, hr).
This is not how JDBC does locking.
Move off Record
Copyright © 2005, SAS Institute Inc. All rights reserved.
1. Does the JDBC driver have any built in record locking? (What do I have to do to maintain data integrity?)
JDBC does not allow users to hold a lock open on a record the way FSEdit does.
Copyright © 2005, SAS Institute Inc. All rights reserved.
4. Resubmitting an update seems inelegant, is there anything better I can do?
WS FSEdit
Table
JDBC
Locks TableFail
Fail
SHARE
No choice but to wait for lock to be released and try again.
Copyright © 2005, SAS Institute Inc. All rights reserved.
FSEdit
Table
JDBC
Locks Record NFail on Record N
SHARE
No choice but to wait for lock to be released and try again.
4. Resubmitting an update seems inelegant, is there anything better I can do?
Copyright © 2005, SAS Institute Inc. All rights reserved.
Configuration, application behavior and usage pattern affect performance.
Neither record level locking nor member (table) level locking in themselves determine performance level.
Test system interaction early.
Try it and see.
Copyright © 2005, SAS Institute Inc. All rights reserved.
Execute Update Revisitedtry{
statement.clearWarnings();
numRowsAffected = statement.executeUpdate(…);
w = statement.getWarnings();
while(w != null){
w = w.getNextWarning();
}
} catch(Exception e){
return false;
}
Copyright © 2005, SAS Institute Inc. All rights reserved.
What we covered Overview of multi-user update; conflict resolution.
Showed how to do multi-user update in JDBC.
Described problem of update based on query.
Showed why update without query is preferable.
Talked about different locking styles in SAS and how they affect multi-user update.
Illustrated system complexity issues.
Pointed out the importance of testing system performance early.
Copyright © 2005, SAS Institute Inc. All rights reserved.
What to Remember executeUpdate is likely to give you good
performance with your current configuration.
Don’t guess, give it a try.
try{
statement.executeUpdate(…);
} catch (Exception e){
return false;
}
Copyright © 2005, SAS Institute Inc. All rights reserved.
Questions?