Locks in db2?

Locks in db2?

Post by tchatter.. » Fri, 17 Nov 2000 12:47:39



I want to insert a few statements in a table and then do commit one of
the db2 tables (from an application). Unfortunately, the default
locking mechanism won't allow me to do it. How do I change this lock
mode at:

1. The table level
2. The database level

I want to have basic locking though (while the insert statement is
being processed)

Any help is appreciated

Tanuj Chatterjee

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Locks in db2?

Post by Mitc » Fri, 17 Nov 2000 14:58:58


I think you need to give a little more detail on EXACTLY what you're
trying to do.

--
Richard Mitch Mitchell
Senior Database Administrator
IBM Certified Solutions Expert - DB2 UDB v6.1 Database Administration
for UNIX, Windows and OS/2

Fracta Networks



> I want to insert a few statements in a table and then do commit one of
> the db2 tables (from an application). Unfortunately, the default
> locking mechanism won't allow me to do it. How do I change this lock
> mode at:

> 1. The table level
> 2. The database level

> I want to have basic locking though (while the insert statement is
> being processed)

> Any help is appreciated

> Tanuj Chatterjee

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Locks in db2?

Post by pm3i.. » Fri, 17 Nov 2000 04:00:00


Like mitch said, a little more detail would be helpful.

First comments;

locking levels are for row/table, never heard of database/node/instance
locks.

"you insert stuff in one table and want to commit one of the tableS."
What are these other tables that we don't know anything about.

commits have a unit-of-work scope.  I don't think there is a way to
commit some updates done on table1 and don't commit updates
on table 2 while in the same UOW.

you can influence locking by
CHANGE ISOLATION LEVEL ...
bind options/other options
adding FOR READ ONLY, FOR UPDATE, ... into your statements
using cursors
LOCK TABLE ...
etc, etc, etc, ...


> I want to insert a few statements in a table and then do commit one of
> the db2 tables (from an application). Unfortunately, the default
> locking mechanism won't allow me to do it. How do I change this lock
> mode at:

> 1. The table level
> 2. The database level

> I want to have basic locking though (while the insert statement is
> being processed)

> Any help is appreciated

> Tanuj Chatterjee

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

Locks in db2?

Post by ta.. » Fri, 17 Nov 2000 04:00:00


ex: I need to insert 10 rows in a table. If one of the insertions fail,
I would do a rollback (i.e. won't insert any row). That means, I will
do a commit only after inserting 10 rows.

It seems that by default, the locking mechanism in db2 is at the table
level which is preventing me from inserting the second row as I haven't
done a commit. Will row locking solve the problem? Also, if I restart
the database, will it still hold on to row locking (instead of table
locking?).

I looked at other postings and it said that I can reduce the
locktimeout but that worries me specifically if there are concurrent
requests going on.

I'm just 2 days into db2 so please bear with my naiveness...

thanks
Tanuj Chatterjee



> I think you need to give a little more detail on EXACTLY what you're
> trying to do.

> --
> Richard Mitch Mitchell
> Senior Database Administrator
> IBM Certified Solutions Expert - DB2 UDB v6.1 Database Administration
> for UNIX, Windows and OS/2

> Fracta Networks



> > I want to insert a few statements in a table and then do commit one
of
> > the db2 tables (from an application). Unfortunately, the default
> > locking mechanism won't allow me to do it. How do I change this lock
> > mode at:

> > 1. The table level
> > 2. The database level

> > I want to have basic locking though (while the insert statement is
> > being processed)

> > Any help is appreciated

> > Tanuj Chatterjee

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

Locks in db2?

Post by Blair Kenneth Adamach » Fri, 17 Nov 2000 04:00:00


Default locking is at the row-level.  Look into isolation levels in the
Administration Guide - this may explain why your application is acquiring a
table level lock.

> ex: I need to insert 10 rows in a table. If one of the insertions fail,
> I would do a rollback (i.e. won't insert any row). That means, I will
> do a commit only after inserting 10 rows.

> It seems that by default, the locking mechanism in db2 is at the table
> level which is preventing me from inserting the second row as I haven't
> done a commit. Will row locking solve the problem? Also, if I restart
> the database, will it still hold on to row locking (instead of table
> locking?).

> I looked at other postings and it said that I can reduce the
> locktimeout but that worries me specifically if there are concurrent
> requests going on.

> I'm just 2 days into db2 so please bear with my naiveness...

> thanks
> Tanuj Chatterjee



> > I think you need to give a little more detail on EXACTLY what you're
> > trying to do.

> > --
> > Richard Mitch Mitchell
> > Senior Database Administrator
> > IBM Certified Solutions Expert - DB2 UDB v6.1 Database Administration
> > for UNIX, Windows and OS/2

> > Fracta Networks



> > > I want to insert a few statements in a table and then do commit one
> of
> > > the db2 tables (from an application). Unfortunately, the default
> > > locking mechanism won't allow me to do it. How do I change this lock
> > > mode at:

> > > 1. The table level
> > > 2. The database level

> > > I want to have basic locking though (while the insert statement is
> > > being processed)

> > > Any help is appreciated

> > > Tanuj Chatterjee

> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

1. Q: INFORMIX Locking vs DB2 Locking

Hello,

I heard, DB2 is able to hold record locks after commit. Is there a
possibility for INFORMIX, too?

Thanks,
Volker

--
Volker Fraenkle                         CS Controlling
Technical Consultant                    Software Systeme GmbH


Phone:  +49 7144 8144-0                 Riedbachstrasse 5
Fax:    +49 7144 8144-10                D-74385 Pleidelsheim

2. Filling out the Prediction Tree List of Data Mining Model Browser

3. Record Lock : VB / DB2 / MSRDC / ODBC

4. Session id

5. pgsql/src backend/catalog/aclchk.c backend/cat ...

6. View locks on DB2

7. getting table names through "get descriptor"?

8. How to lock a DB2 record from VB ?

9. Distributed DB2 - Oracle/Oracle - Oracle/DB2 - DB2/DB2