DB2 Transactions and locking

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

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;

Select allOpen 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

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;

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.

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

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.

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

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.

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?

TOAD for DB2.  Just ordered DB2 for windows for dummies.

One note:

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

In SQL Server we call that a "dirty read".

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

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.