Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
How to lock a particular row in Db2 ?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
itzbalajee

New User


Joined: 11 Aug 2006
Posts: 5
Location: Chennai

PostPosted: Tue Feb 20, 2007 11:58 am    Post subject: How to lock a particular row in Db2 ?
Reply with quote

Assume that one user is updating a row in a table through an application.
Now if another user is tries to update that same row(at the same time) he should not be able to update the row. I am planning to throw a message to the second user saying "This row is being updated by some other user".

Now the first user should lock the row before updating.
So please let me how to lock that particular row.

Thanks in advance,
balaji
Back to top
View user's profile Send private message

sinu_203

New User


Joined: 17 Feb 2007
Posts: 3
Location: kerala

PostPosted: Tue Feb 20, 2007 12:05 pm    Post subject: DB2 locks
Reply with quote

Please go through the details below. I think it will be helpful to u

> IN (Intent None) Table spaces, tables The lock
> owner can read any data in
> the table, including uncommitted data, but cannot
> update any of it. No row
> locks are acquired by the lock owner. Other
> concurrent applications can read
> or update the table.
>
> IS (Intent Share) Table spaces, tables The lock
> owner can read data in the
> locked table, but not update this data. When an
> application holds the IS
> table lock, the application acquires an S or NS lock
> on each row read. In
> either case, other applications can read or update
> the table.
>
> NS (Next Key Share) Rows The lock owner and all
> concurrent applications
> can read, but not update, the locked row. This lock
> is acquired on rows of a
> table, instead of an S lock, where the isolation
> level is either RS or CS on
> data that is read.
>
> S (Share) Rows, tables The lock owner and all
> concurrent applications can
> read, but not update, the locked data. Individual
> rows of a table can be S
> locked. If a table is S locked, no row locks are
> necessary.
>
> IX (Intent Exclusive) Table spaces, tables The
> lock owner and concurrent
> applications can read and update data in the table.
> When the lock owner
> reads data, an S, NS, X, or U lock is acquired on
> each row read. An X lock
> is also acquired on each row that the lock owner
> updates. Other concurrent
> applications can both read and update the table.
>
> SIX (Share with Intent Exclusive) Tables The lock
> owner can read and
> update data in the table. The lock owner acquires X
> locks on the rows it
> updates, but acquires no locks on rows that it
> reads. Other concurrent
> applications can read the table.
>
> U (Update) Rows, tables The lock owner can update
> data in the locked row
> or table. The lock owner acquires X locks on the
> rows before it updates the
> rows. Other units of work can read the data in the
> locked row or table; but
> cannot attempt to update it.
>
> NX (Next Key Exclusive) Rows The lock owner can
> read but not update the
> locked row. This mode is similar to an X lock except
> that it is compatible
> with the NS lock.
>
> NW (Next Key Weak Exclusive) Rows This lock is
> acquired on the next row
> when a row is inserted into the index of a
> non-catalog table. The lock owner
> can read but not update the locked row. This mode is
> similar to X and NX
> locks except that it is compatible with the W and NS
> locks.
>
> X (Exclusive) Rows, tables The lock owner can both
> read and update data in
> the locked row or table. Tables can be Exclusive
> locked, meaning that no row
> locks are acquired on rows in those tables. Only
> uncommitted read
> applications can access the locked table.
>
> W (Weak Exclusive) Rows This lock is acquired on
> the row when a row is
> inserted into a non-catalog table. The lock owner
> can change the locked row.
> This lock is similar to an X lock except that it is
> compatible with the NW
> lock. Only uncommitted read applications can access
> the locked row.
>
> Z (Superxclusive) Table spaces, tables This lock
> is acquired on a table in
> certain conditions, such as when the table is
> altered or dropped, an index
> on the table is created or dropped, or a table is
> reorganized. No other
> concurrent application can read or update the table.
Back to top
View user's profile Send private message
itzbalajee

New User


Joined: 11 Aug 2006
Posts: 5
Location: Chennai

PostPosted: Tue Feb 20, 2007 12:16 pm    Post subject: Db2 locks
Reply with quote

I have tried this before.

I had a cursor with "for update of column-name".
But even then that row was not locked. We tried this like two users updating the same row. And both of them were able to change the row.

Thanks for ur reply.
balaji
Back to top
View user's profile Send private message
itzbalajee

New User


Joined: 11 Aug 2006
Posts: 5
Location: Chennai

PostPosted: Tue Feb 20, 2007 12:29 pm    Post subject: This is the problem i am facing
Reply with quote

Assume that there are two users u1 & u2.

And there is an table Emp_tab. In that emp_id is the primary key.

Emp_tab
---------------------------------------------------------------------
Emp_id Emp_name Emp_dob Emp_stat_ind
---------------------------------------------------------------------
1000 aaa 12/12/1983 A
2000 bbb 12/12/1983 B
3000 ccc 12/12/1983 A
4000 ddd 12/12/1983 A

Now assume that both the users U1 & U2 have loaded the employee 2000 in their frontend application. And they are trying to change the EMP_STAT_IND to C and D respectively.

Now in my program i have the logic like the old row will be expired and the new row will be inserted. Now in this case both(C & D) EMP_STAT_IND are added to this employee.

So i want to avoid this by locking the row.
Back to top
View user's profile Send private message
sinu_203

New User


Joined: 17 Feb 2007
Posts: 3
Location: kerala

PostPosted: Tue Feb 20, 2007 12:54 pm    Post subject: better to use isolation levels
Reply with quote

Better to use isolation levels based on ur requirement

CS (cursor stability), RR ............so on
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How to put exclusive lock on a file i... sudarshan.srivathsav JCL & VSAM 4 Fri Jun 05, 2015 10:36 pm
No new posts dead lock while updating the DB2 table abdulrafi DB2 7 Tue Oct 22, 2013 7:02 pm
No new posts Dead lock in DB2 - CICS programs Pandora-Box CICS 8 Fri Sep 13, 2013 3:47 pm
No new posts IRLM Lock Compatibility Amit.Agarwal IMS DB/DC 0 Sat Mar 30, 2013 5:15 pm
No new posts How to lock and release keyboard in CICS baratchandar CICS 1 Wed Dec 05, 2012 11:21 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us