RE: SQL Question On Record Locks

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

Ron,

As with RPG, the answer is it depends.

With RPG, it depends on if commitment control is used and if so, what LCKLCL is 
used on the STRCMTCTL command.

Since SQL always uses commitment control, it simply depends on the lock level 
used.

Take a look at the SQL reference manual (page 22 v5r2) to see a description of 
how the various "Isolation Levels" work.


Also note that the same isolation levels apply when you do a UPDATE TABLE SET 
name='PAT' where name='RON' instead of using a updateable cursor.


HTH,

Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of RPower@xxxxxxxxxx
> Sent: Tuesday, May 17, 2005 2:32 PM
> To: RPG programming on the AS400 / iSeries
> Subject: SQL Question On Record Locks
> 
> 
> Ok guys.  This debate started here since we are taking an SQL 
> training 
> class.
> It concerns how SQL handles record locking.  If we use SELECT 
> * FROM TABLE 
> FOR UPDATE and the result set returns 100 records all of 
> name="RON" and I 
> want them updated to name="PAT".  If a user goes to look at 
> one of those 
> records, at what point are the records updated, locked, etc?  
> Will the 
> user see RON or PAT during the processing?  Does SQL lock one 
> record at a 
> time, or the whole 100?  Thanks,
> 
> Ron Power
> Programmer
> Information Services
> City Of St. John's, NL
> P.O. Box 908
> St. John's, NL
> A1C 5M2
> Tel: 709-576-8132
> Email: rpower@xxxxxxxxxx
> Website: http://www.stjohns.ca/
> ______________________________________________________________
> _____________
> Success is going from failure to failure without a loss of 
> enthusiasm. - 
> Sir Winston Churchill
> -- 
> This is the RPG programming on the AS400 / iSeries (RPG400-L) 
> mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
> 
>