|
View previous topic :: View next topic
|
| Author |
Message |
Murali Sethuram
New User
Joined: 23 Dec 2020
Posts: 4
Location: United States
|
|
|
|
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 |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| Row should be skipped if its locked by other processes . Isn't that happening ? |
|
| Back to top |
|
|
Murali Sethuram
New User
Joined: 23 Dec 2020
Posts: 4
Location: United States
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| Did you not get syntax error , as ',' is missing between two columns in SET? |
|
| Back to top |
|
|
Murali Sethuram
New User
Joined: 23 Dec 2020
Posts: 4
Location: United States
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| 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 |
|
|
Murali Sethuram
New User
Joined: 23 Dec 2020
Posts: 4
Location: United States
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| Try updating without SKIP LOCKED DATA, if it works then please talk to DBA. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10886
Location: italy
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
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 |
|
|
|
|
