IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SKIP LOCKED DATA in UPDATE statement


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Murali Sethuram

New User


Joined: 23 Dec 2020
Posts: 4
Location: United States

PostPosted: Wed Dec 23, 2020 10:02 pm
Reply with quote

Using this clause with UPDATE statements sporadically skips Timestamp columns. Has anybody noticed this or have a solution to prevent this?
My query
Code:

UPDATE table
      SET col1_TS = CURRENT TIMESTAMP
             col2_ind = :value1
 WHERE  col3 = :value2
       and col4 = :value3
SKIP LOCKED DATA

Cod'ed for you
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Wed Dec 23, 2020 11:32 pm
Reply with quote

Row should be skipped if its locked by other processes . Isn't that happening ?
Back to top
View user's profile Send private message
Murali Sethuram

New User


Joined: 23 Dec 2020
Posts: 4
Location: United States

PostPosted: Wed Dec 23, 2020 11:48 pm
Reply with quote

Col1 remains '0001-01-01-00.00.00.000000'
Col2 gets updated.

Not sure if the recent change to SKIP LOCKED DAT clause or something else is the issue here.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Dec 24, 2020 12:56 am
Reply with quote

Did you not get syntax error , as ',' is missing between two columns in SET?
Back to top
View user's profile Send private message
Murali Sethuram

New User


Joined: 23 Dec 2020
Posts: 4
Location: United States

PostPosted: Thu Dec 24, 2020 1:01 am
Reply with quote

No syntax error.
Just that col2_ind gets updated but col1_TS does not get updated with CURRENT TIMESTAMP.
Remains inserted value '0001-01-01-00.00.00.000000'
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Dec 24, 2020 2:01 am
Reply with quote

Then add ',' and should work. This to me is a WAD by IBM. I have seen similar reaction for char field when i don't give single quotes.
Back to top
View user's profile Send private message
Murali Sethuram

New User


Joined: 23 Dec 2020
Posts: 4
Location: United States

PostPosted: Thu Dec 24, 2020 3:15 am
Reply with quote

The comma on SET - I missed it why I typed into the forum post- the actual code has it - that would definitely give a syntax error.

Just that one column is getting updated and the other is not.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Thu Dec 24, 2020 3:52 am
Reply with quote

Try updating without SKIP LOCKED DATA, if it works then please talk to DBA.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Thu Dec 24, 2020 4:30 am
Reply with quote

SKIP LOCKED DATA on an update statement is pretty inconsiderate

You will end up with inconsistent partial updates and You will probably know about it when it is too late

from the IBM manuals

Quote:
The SKIP LOCKED DATA clause specifies that rows are skipped when incompatible locks that would block the progress of the statement are held on the rows by other transactions. These rows can belong to any accessed table that is specified in the statement. SKIP LOCKED DATA can be used only with isolation CS or RS and applies only to row level or page level locks.


Quote:
SKIP LOCKED DATA is ignored if it is specified when the isolation level that is in effect is repeatable read (WITH RR) or uncommitted read (WITH UR). The default isolation level of the statement depends on the isolation level of the package or plan with which the statement is bound, and whether the result table is read-only.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Mon Dec 28, 2020 8:47 am
Reply with quote

SKIP LOCKED DATA
Quote:
About this task
The SKIP LOCKED DATA option allows a transaction to skip rows that are incompatibly locked by other transactions when those locks would hinder the progress of the transaction. Because the SKIP LOCKED DATA option skips these rows, the performance of some applications can be improved by eliminating lock wait time. However, you must use the SKIP LOCKED DATA option only for applications that can reasonably tolerate the absence of the skipped rows in the returned data. If your transaction uses the SKIP LOCKED DATA option, it does not read or modify data that is held by locks.

However, keep in mind that your application cannot rely on Db2 to skip all data for which locks are held. Db2 skips only locked data that would block the progress of the transaction that uses the SKIP LOCKED DATA option. If Db2 determines through lock avoidance that the locked data is already committed, the locked data is not skipped. Instead, the data is returned with no wait for the locks.

In you case it looks like col1_TS was locked by other process at exactly same time when this update was issued and made to update with low date time stamp value. IF you share this with DBA then they might look at the thread and give you more details about it.
If you find the casue then please share so that it will help others in future.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts SCOPE PENDING option -check data DB2 2
No new posts Check data with Exception Table DB2 0
No new posts JCL EXEC PARM data in C Java & MQSeries 2
This topic is locked: you cannot edit posts or make replies. Automation need help in sorting the data DFSORT/ICETOOL 38
Search our Forums:

Back to Top