Avatar of Jim Youmans
Jim Youmans
Flag for United States of America asked on

DB2 Transactions and locking

DB2 10.5 on Windows Server

If I have something like this...

START TRANSACTION
     CALL WEB SERVICE; (can take up to 8 seconds)
     UPDATE TABLE1;
     UPDATE TABLE2;
     DELETE FROM TABLE3;
COMMIT TRANSACTION;


How would DB2 handle the tables during the life of the transaction?  Would it only lock a table when the change is made or would if lock the tables at the start of the transaction?  I am not familiar with DB2 transactions and while the IBM info pages describe how they work, none of it answers this question.

I am making the assumption that they are similar to transactions in SQL Server although I do see some references to SAVEPOINTS which I am not sure I understand.  I think that might not apply to DB2 on windows.

Thank you so much for any help in advance.

Jim
DB2Windows OSSQL

Avatar of undefined
Last Comment
Kent Olsen

8/22/2022 - Mon
Kent Olsen

Hi Jim,

Locking works similarly on most database engines these days, though the vendors use different terminology (just like they use different looking SQL)....

By default, DB2 will NOT lock a table to perform a change (update/insert/delete, etc.)  Instead, DB2 will lock the row being changed.  With just that single row locked, every other row in the table is available to every other task.  

DB2 also utilizes something called "lock escalation".  To reduce the number of locks being managed, DB2 will merge similar locks into a higher level lock, "escalating" the lock to the next larger group.  If DB2 detects that a task has multiple row locks in the same block it can escalate the locks to a single page  (block) lock, reserving every row in the page for the current task.  

In your example, the UPDATE(s) to Table 1 all occur during the statement execution.  When it completes, all of the row locks and page locks required to update Table 1 are held while the next statements in the transaction are executed.  Only when the updates to all of the tables are complete does DB2 commit the changes and release the locks.

That's a little different from most clients (Developer Studio, TOAD, etc.) which usually commit the changes automatically after every statement.


Kent
Jim Youmans

ASKER
OK, that makes sense I think.  So I am trying to test this and not sure what I am doing wrong.  I am following the examples from IBM but ...

Here is what I have

BEGIN 
UPDATE TESTTABLE2 SET MYVALUE = 'WTH' WHERE ID = 2
COMMIT;

Open in new window

Here is the error I get
ERROR [42601] [IBM][DB2/NT64] SQL0104N  An unexpected token "2" was found following "E = 'WTH' WHERE ID =".  Expected tokens may include:  "IN <in_predicate_value>".

Any thoughts?  Other than "god this guy is dense.

Jim
Scott Pletcher

A table would only be locked after it has actually been updated.  DB2 would not "pre-lock" the table just because of a transaction.

Since you explicitly started a transaction, "BEGIN TRANSACTION", every rdbms I know of would treat all the UPDATEs and other mods to recoverable resources within that transaction as "all or none".  That is, ultimately either all the updates/mods in the tran will be applied or none of them will be ("atomicity").  


As to the issue with last code, you left out the keyword "TRANSACTION", which might account for the error you're seeing.

BEGIN TRANSACTION
UPDATE TESTTABLE2 SET MYVALUE = 'WTH' WHERE ID = 2
COMMIT;
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Gary Patterson, CISSP

Just to append to Kent's comments:  

If you are using a cursor, the Isolation Level you declare for transactions also has an impact on locking behavior:

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.admin.perf.doc/doc/c0004121.html

You might want to actually supply a code sample, so we can see exactly what it is you're doing.
Jim Youmans

ASKER
I am trying to test the locking behavior to make sure it works as I think it will, as Kent described.  My code example is

BEGIN TRANSACTION
UPDATE TESTTABLE2 SET MYVALUE = 'WTH' WHERE ID = 2
COMMIT


And I get an error message when I run it.  I have tried many different versions of the syntax and none seem to work.

The only thing I can think of is that I am using TOAD and maybe that is the problem?

Jim
Gary Patterson, CISSP

Isn't BEGIN TRANSACTION SQL Server syntax?  I'm an IBM i DB2 guy primarily, but I don't think I've ever seen this statement in DB2.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gary Patterson, CISSP

Configure your connection with an isolation level that isn't *NONE/NC, then try just the UPDATE and COMMIT.
Scott Pletcher

Quite right on the BEGIN TRAN; I thought that was ANSI-standard syntax but wasn't sure.

It seems that DB2 uses:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

to start a trans.  Not as clear or as clean, but it seems that's the way it is.
Kent Olsen

Any thoughts?  Other than "god this guy is dense.

That's a long way to go to get a free beer!   :)

What client are you using to run the statements?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Jim Youmans

ASKER
TOAD for DB2.  Just ordered DB2 for windows for dummies.
Gary Patterson, CISSP

One note:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED doesn't actually start a transaction.  It changes the isolation level used in transactions going forward.
Jim Youmans

ASKER
In SQL Server we call that a "dirty read".
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Kent Olsen

Toad for DB2.  :)

Go to the options page and make sure that auto-commit is turned off.  Then you won't have to initiate a transaction as all of the statements will be executed in sequence without an intervening COMMIT and the behavior will be as if you're running a transaction.

Note that when you're done testing you probably want to turn auto-commit back on.  Most people run this way so that changes are committed as soon as the statement ends.  (You wouldn't want to run an update and head to lunch thinking it was done!)


Kent
Scott Pletcher

Meant to use READ COMMITTED not uncomm.

If you want all or none of the updates to be applied, you do not want to use auto-commit per statement. Bizarre that db2 doesn't easily allow you to explicitly start a trans, other major rdbms's do.
ASKER CERTIFIED SOLUTION
Kent Olsen

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question