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
Jim YoumansSr Database AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
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
0
Jim YoumansSr Database AdministratorAuthor Commented:
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
0
Scott PletcherSenior DBACommented:
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;
0
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Gary PattersonVP Technology / Senior Consultant Commented:
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.
0
Jim YoumansSr Database AdministratorAuthor Commented:
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
0
Gary PattersonVP Technology / Senior Consultant Commented:
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.
0
Gary PattersonVP Technology / Senior Consultant Commented:
Configure your connection with an isolation level that isn't *NONE/NC, then try just the UPDATE and COMMIT.
0
Scott PletcherSenior DBACommented:
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.
0
Kent OlsenDBACommented:
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?
0
Jim YoumansSr Database AdministratorAuthor Commented:
TOAD for DB2.  Just ordered DB2 for windows for dummies.
0
Gary PattersonVP Technology / Senior Consultant Commented:
One note:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED doesn't actually start a transaction.  It changes the isolation level used in transactions going forward.
0
Jim YoumansSr Database AdministratorAuthor Commented:
In SQL Server we call that a "dirty read".
0
Kent OlsenDBACommented:
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
0
Scott PletcherSenior DBACommented:
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.
0
Kent OlsenDBACommented:
Hi Scott,

That's a behavior of the client.  By turning off auto-commit, statements are executed as if they are in a transaction.  

That has a long history with DB2.  The "batch" tool (db2 command line processor) is almost as old as is DB2.  It's default behavior is to issue a commit after every update.  It also has a command line switch that turns off auto-commit.

So the DB2 engine (server) is blind to the execution of transactions, deferring the controls of the transaction process to the process that is connected to the server.

I suspect that most DBMS work similarly, except that most explicitly support overriding the auto-commit behavior by issuing a START TRANSACTION / BEGIN WORK or other statement.

Kent
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.