insert - always X locks?

D A

insert - always X locks?
Esteemed colleagues,
I would like to verify the facts or what I think the fact is.
1.) When inserting a row, does DB2 on OS/390 always locks whole table with
X lock?
2.) Is it different with DB2 on Win/Linux/Unix?
In MS SQL Server online help I found a chapter about Key-Range Locking
which is apparently a mechanism used to enable concurrent inserts and other
dml. Never tested it, will give it a try just to see.
I am somewhat surprised to see more advanced concurrency mechanism in MS
product. I thought DB2 would be superior database in terms of concurrency.
Any opinions?
Thanks,
Daniel

PS. This is not a flame bate, but If you feel like it then just go ahead :)



D A

Re: insert - always X locks?
(in response to Joel Goldstein)
RTFM is the answer for my questions - DB2 Administration Guide, chpt. 30,
under "DB2’s choice of lock types".
In usual scenario (LOCKSIZE PAGE) it takes IX locks on both the tablespace
and table. An X lock is only taken on the data page.
It seems that I was wrong - DB2 does not take an X lock on whole table when
inserting a row. Now I wander just where I got the idea from...
Bye,
Daniel



Joel Goldstein

Re: insert - always X locks?
(in response to D A)
It depends on your objects, and thespecified level of locking,
it can be at tablespace, table, page or row level.
I suggest you read the DB2 Administration Guide for a claer understanding
of your options.
Options in SQl Server are not superior to DB2.

Regards,
Joel


Message text written by DB2 Data Base Discussion List
>Esteemed colleagues,
I would like to verify the facts or what I think the fact is.
1.) When inserting a row, does DB2 on OS/390 always locks whole table with
X lock?
2.) Is it different with DB2 on Win/Linux/Unix?
In MS SQL Server online help I found a chapter about Key-Range Locking
which is apparently a mechanism used to enable concurrent inserts and other
dml. Never tested it, will give it a try just to see.
I am somewhat surprised to see more advanced concurrency mechanism in MS
product. I thought DB2 would be superior database in terms of concurrency.
Any opinions?
Thanks,
Daniel

PS. This is not a flame bate, but If you feel like it then just go ahead
:)<



Mike Turner

Re: insert - always X locks?
(in response to D A)
Daniel

Everything I say about locking is a simplification, but ...

The X-lock taken by DB2 for OS/390 for an insert depends on the LOCKSIZE
specified on the tablespace. It will lock the table(space) only if LOCKSIZE
TABLESPACE was specified. It will lock the page on which the row is
inserted for LOCKSIZE PAGE or ANY. It will lock the new row for LOCKSIZE
ROW.

DB2 on Unix/Linux/Windows always locks the row.

Regards
Mike Turner
Email: [login to unmask email]
Web: www.michael-turner.ltd.uk

Message text written by DB2 Data Base Discussion List
>Esteemed colleagues,
I would like to verify the facts or what I think the fact is.
1.) When inserting a row, does DB2 on OS/390 always locks whole table with
X lock?
2.) Is it different with DB2 on Win/Linux/Unix?
In MS SQL Server online help I found a chapter about Key-Range Locking
which is apparently a mechanism used to enable concurrent inserts and other
dml. Never tested it, will give it a try just to see.
I am somewhat surprised to see more advanced concurrency mechanism in MS
product. I thought DB2 would be superior database in terms of concurrency.
Any opinions?
Thanks,
Daniel

PS. This is not a flame bate, but If you feel like it then just go ahead :)
<