473,321 Members | 1,877 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Table Locks and Preventing Deadlock

I`m exporting data from many Lotus Notes databases to a DB2 database
using LotusScript. The LotusScript agents commit after EACH
update/insert. Nevertheless, I keep getting transaction rollbacks on
deadlock or timeout. When I get a snapshot for locks, I see that one
of the export agents is getting a table lock rather than a row lock.
I`m guessing this is what causes the time out. Questions: how do I
determine whether it`s a deadlock or a timeout? If it`s because of the
table locks, what can I do to prevent it?
LOCKLIST = 64000
DLCHKTIME = 15000
MAXLOCKS = 20
LOCKTIMEOUT = 45
Thanks.
Randy
Nov 12 '05 #1
11 14012
Ian
EoRaptor wrote:
I`m exporting data from many Lotus Notes databases to a DB2 database
using LotusScript. The LotusScript agents commit after EACH
update/insert. Nevertheless, I keep getting transaction rollbacks on
deadlock or timeout. When I get a snapshot for locks, I see that one
of the export agents is getting a table lock rather than a row lock.
I`m guessing this is what causes the time out. Questions: how do I
determine whether it`s a deadlock or a timeout? If it`s because of the
table locks, what can I do to prevent it?
LOCKLIST = 64000
DLCHKTIME = 15000
MAXLOCKS = 20
LOCKTIMEOUT = 45
Thanks.
Randy


You need to look at the reason code from the error:

SQL0911N, reason code 2 = deadlock
SQL0911N, reason code 68 = lock timeout
There are a number of parameters that you may need to consider:

LOCKTIMEOUT -- specifies the time lock waits will last before SQL0911N
LOCKLIST / MAXLOCKS -- controls the number of locks an application can
take before causing lock escalation
Good luck,
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #2
"EoRaptor" <rc*******@patmedia.net> wrote in message
news:fa**************************@posting.google.c om...
I`m exporting data from many Lotus Notes databases to a DB2 database
using LotusScript. The LotusScript agents commit after EACH
update/insert. Nevertheless, I keep getting transaction rollbacks on
deadlock or timeout. When I get a snapshot for locks, I see that one
of the export agents is getting a table lock rather than a row lock.
I`m guessing this is what causes the time out. Questions: how do I
determine whether it`s a deadlock or a timeout? If it`s because of the
table locks, what can I do to prevent it?
LOCKLIST = 64000
DLCHKTIME = 15000
MAXLOCKS = 20
LOCKTIMEOUT = 45
Thanks.
Randy


I am not sure this will help, but try increasing maxlocks to 60. This means
that lock escalation will only occur if the agent is using more than 60% of
the locklist.

However, locks are usually released at each commit, so I don't know why you
are getting lock escalation unless the LotusScript agent specifically asked
for a table lock in the program. You should be able to track lock
escalations in the snapshot if that is occurring.

Doing a commit after each update/insert is not very efficient, so I would
consider exporting the data from Lotus into ASCII or del format and using
the DB2 import command with the "INSERT_UPDATE" option (Adds rows of
imported data to the target table, or updates existing rows of the target
table with matching primary keys) and set COMMITCOUNT to 100. This should
cut the elapsed time at least in half but still provide reasonable
concurrency with other applications.
Nov 12 '05 #3
"Mark A" <ma@switchboard.net> wrote in message news:<qu******************@news.uswest.net>...
I am not sure this will help, but try increasing maxlocks to 60. This means
that lock escalation will only occur if the agent is using more than 60% of
the locklist.

However, locks are usually released at each commit, so I don't know why you
are getting lock escalation unless the LotusScript agent specifically asked
for a table lock in the program. You should be able to track lock
escalations in the snapshot if that is occurring.

Doing a commit after each update/insert is not very efficient, so I would
consider exporting the data from Lotus into ASCII or del format and using
the DB2 import command with the "INSERT_UPDATE" option (Adds rows of
imported data to the target table, or updates existing rows of the target
table with matching primary keys) and set COMMITCOUNT to 100. This should
cut the elapsed time at least in half but still provide reasonable
concurrency with other applications.


Thanks. I hesitate to increase MAXLOCKS because I have 4 Lotus Notes
agents running at the same time -- each agent exporting 1 LN DB at a
time. Still, I'll give it a try -- can always change it back. Also, it
does look like it's a lock escalation issue. When I look at the
snapshot, I see that one of the agents has gotten a table IX lock.
Still, I don't know why that isn't released after the commit.
As for commit frequency and ascii import, this is an on-going process.
Each LN DB updates the DB2 tables once per day. The Notes databases
are our production application -- workflow for immigration
applications. So, I'm using agents to automate the process. FWIW,
before I started having these rollbacks, the agents were processing
about 150 Lotus Notes documents per second -- perfectly acceptable.
Again, thanks.
Nov 12 '05 #4
Ian <ia*****@mobileaudio.com> wrote in message news:<40**********@corp.newsgroups.com>...
You need to look at the reason code from the error:

SQL0911N, reason code 2 = deadlock
SQL0911N, reason code 68 = lock timeout
There are a number of parameters that you may need to consider:

LOCKTIMEOUT -- specifies the time lock waits will last before SQL0911N
LOCKLIST / MAXLOCKS -- controls the number of locks an application can
take before causing lock escalation


Ah ha! So they are all deadlocks. Using snapshot for locks, I found
that one agent is getting its locks escalated -- an IX table lock. My
LOCKLIST is 96000 and MAXLOCKS is 20. I'll try some fiddling to see
what happens.
Thanks.
Randy
Nov 12 '05 #5
"EoRaptor" <rc*******@patmedia.net> wrote in message
news:fa**************************@posting.google.c om...
"Mark A" <ma@switchboard.net> wrote in message news:<qu******************@news.uswest.net>...
I am not sure this will help, but try increasing maxlocks to 60. This means that lock escalation will only occur if the agent is using more than 60% of the locklist.

However, locks are usually released at each commit, so I don't know why you are getting lock escalation unless the LotusScript agent specifically asked for a table lock in the program. You should be able to track lock
escalations in the snapshot if that is occurring.

Doing a commit after each update/insert is not very efficient, so I would consider exporting the data from Lotus into ASCII or del format and using the DB2 import command with the "INSERT_UPDATE" option (Adds rows of
imported data to the target table, or updates existing rows of the target table with matching primary keys) and set COMMITCOUNT to 100. This should cut the elapsed time at least in half but still provide reasonable
concurrency with other applications.


Thanks. I hesitate to increase MAXLOCKS because I have 4 Lotus Notes
agents running at the same time -- each agent exporting 1 LN DB at a
time. Still, I'll give it a try -- can always change it back. Also, it
does look like it's a lock escalation issue. When I look at the
snapshot, I see that one of the agents has gotten a table IX lock.
Still, I don't know why that isn't released after the commit.
As for commit frequency and ascii import, this is an on-going process.
Each LN DB updates the DB2 tables once per day. The Notes databases
are our production application -- workflow for immigration
applications. So, I'm using agents to automate the process. FWIW,
before I started having these rollbacks, the agents were processing
about 150 Lotus Notes documents per second -- perfectly acceptable.
Again, thanks.


I don't understand why you would be hesitant to increase MAXLOCKS. The worst
that will happen is that you will prevent escalation to table locks. If you
are running 4 agents at a time against the same table, you don't want
escalation. The only time you might want escalation is when you have single
job running against the table, and you need it to run faster by not having
to obtain (and maybe release) row locks for each row.

150 rows per second is pitiful performance, but if your volume is low, then
it doesn't matter.
Nov 12 '05 #6
"Mark A" <ma@switchboard.net> wrote in message news:<Hf****************@news.uswest.net>...
I don't understand why you would be hesitant to increase MAXLOCKS. The worst
that will happen is that you will prevent escalation to table locks. If you
are running 4 agents at a time against the same table, you don't want
escalation. The only time you might want escalation is when you have single
job running against the table, and you need it to run faster by not having
to obtain (and maybe release) row locks for each row.

150 rows per second is pitiful performance, but if your volume is low, then
it doesn't matter.


Is there a way to just turn off the bloody lock escalation?? Right
now, LOCKLIST is at 128000 and MAXLOCKS is at 60. Nevertheless, if I
run two or more agents(four are supposed to run), one of them ends up
getting a table lock -- at least that's what seems to be happening.
All agents are exactly the same and nowhere do I ask for a table lock.
Each agent has a maximum of 6 connections at any one time but it is a
certainty that NO agent will require any record written to, or held
by, any other agent (the combined primary key consists of the Lotus
Notes document ID and the Notes database replica ID and each agent
works wholly within one database and processes one document at a
time).
As far as performance, the bottleneck is on the Lotus Notes side...
exporting data to a text file isn't really any faster than the 150
records/sec direct export to DB2.
Thanks for your thoughts.
Randy
Nov 12 '05 #7
rc*******@patmedia.net (EoRaptor) wrote:
The LotusScript agents commit after EACH update/insert.


As Mark was saying, the lock escalation seems to make little sense -
are your insert/update statements doing row-at-a-time processing, or
set-based (e.g. INSERT SELECT)? If the latter, how many rows is your
largest insert or update statement processing?
Jeremy Rickard
Nov 12 '05 #8
rc*******@patmedia.net (EoRaptor) wrote in message news:<fa**************************@posting.google. com>...
I`m exporting data from many Lotus Notes databases to a DB2 database
using LotusScript. The LotusScript agents commit after EACH
update/insert. Nevertheless, I keep getting transaction rollbacks on
deadlock or timeout. When I get a snapshot for locks, I see that one
of the export agents is getting a table lock rather than a row lock.
I`m guessing this is what causes the time out. Questions: how do I
determine whether it`s a deadlock or a timeout? If it`s because of the
table locks, what can I do to prevent it?


Your later posts show that you are getting deadlocks.

Why not attack the root cause of the deadlocks?

How are you doing the updates?
With Cursor ?
With searched-update? (check your indexes).
What is your db2level and operating-system? If v7.x consider DB2_RR_TO_RS=YES

Are your inserts just plain INSERT ...VALUES?
Or are they INSERT INTO...SELECT ...FROM...WHERE ?

Are you committing after each insert, *and* after each update ?
Nov 12 '05 #9
Ian
EoRaptor wrote:
"Mark A" <ma@switchboard.net> wrote in message news:<Hf****************@news.uswest.net>...

I don't understand why you would be hesitant to increase MAXLOCKS. The worst
that will happen is that you will prevent escalation to table locks. If you
are running 4 agents at a time against the same table, you don't want
escalation. The only time you might want escalation is when you have single
job running against the table, and you need it to run faster by not having
to obtain (and maybe release) row locks for each row.

150 rows per second is pitiful performance, but if your volume is low, then
it doesn't matter.

Is there a way to just turn off the bloody lock escalation?? Right
now, LOCKLIST is at 128000 and MAXLOCKS is at 60. Nevertheless, if I
run two or more agents(four are supposed to run), one of them ends up
getting a table lock -- at least that's what seems to be happening.
All agents are exactly the same and nowhere do I ask for a table lock.
Each agent has a maximum of 6 connections at any one time but it is a
certainty that NO agent will require any record written to, or held
by, any other agent (the combined primary key consists of the Lotus
Notes document ID and the Notes database replica ID and each agent
works wholly within one database and processes one document at a
time).


Unfortunately you can't disable lock escalation entirely, without
resorting to locking the entire table for each transaction (this
would negatively affect concurrency :-)

If you have increased locklist (from 64000 to 128000) and maxlocks
(from 20 to 60), and are still having a problem, are you sure that
the changes have gone into effect? Keep in mind that the database
may need to be deactivated/reactivated for any changes to take effect
(I don't think you mentioned if you're on V7.x or V8.1).

Are you _sure_ your app is actually committing after each insert
or update? What is the scope of the update statements -- if each
update is updating a HUGE (with a 500 Mb locklist, and 60% available
per application you could lock well over 8,000,000 rows), maybe
something in your app is not right.

You should check the db2diag.log for more information. It will contain
information about lock escalations (like how many row locks were held
when escalation was triggered) as well as deadlocks. This may give you
a clue about an error in the app doing the migration. Trapping the SQL
will also help. As will running the app single threaded and monitoring
closely to see what's going on.

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #10
Ian <ia*****@mobileaudio.com> wrote in message news:<40**********@corp.newsgroups.com>...
Unfortunately you can't disable lock escalation entirely, without
resorting to locking the entire table for each transaction (this
would negatively affect concurrency :-)

If you have increased locklist (from 64000 to 128000) and maxlocks
(from 20 to 60), and are still having a problem, are you sure that
the changes have gone into effect? Keep in mind that the database
may need to be deactivated/reactivated for any changes to take effect
(I don't think you mentioned if you're on V7.x or V8.1).

Are you _sure_ your app is actually committing after each insert
or update? What is the scope of the update statements -- if each
update is updating a HUGE (with a 500 Mb locklist, and 60% available
per application you could lock well over 8,000,000 rows), maybe
something in your app is not right.

You should check the db2diag.log for more information. It will contain
information about lock escalations (like how many row locks were held
when escalation was triggered) as well as deadlocks. This may give you
a clue about an error in the app doing the migration. Trapping the SQL
will also help. As will running the app single threaded and monitoring
closely to see what's going on.

Good questions. Wish I was certain of the answers. I'm using
LotusScript LSC:LC classes and connectors. Of course since DB2 and
Lotus come from the same shop, you'd think they'd play well
together...
I am processing single rows. LotusScript agent code gets a Lotus Notes
document, and processes it to insert/update one row into one table.
The code, however, uses the LSX:LC fieldlist and connection classes so
I don't know what they are REALLY doing under the hood. I do have a
primary key and the agent explicitly identifies those two columns as
keys. As for actually commiting, again, I'm not issuing a direct SQL
statement. I use the connection class's "Action" method and specify
commit and assume it does so. I guess that I should be able to monitor
this someway in DB2 but I've only figured out (so far) how to get
snapshots and I've yet to catch anyting "in the act." Is there a way
to watch what's happening in real-time? I do have to say however, that
I took a look at snapshot for database and found 0 lock escalation
events. What I can't figure out is whether that info is cumulative or
if it's also instantaneous, i.e. 0 because I didn't catch one then or
cumulative and I really haven't had any. If the latter then I'm really
at a loss. Within any one agent, tables are populated serially so that
I don't get foreign key conflicts. Inserting one row at a time into my
contact table, shouldn't create deadlocks. However, I only get the
problem when I run multiple agents. I dont' see how this can create
deadlocks however, because each agent works wholly within its own
database and the primary key includes the Notes database replica ID.
So, no agent should be waiting for a commitment from another agent
because no agent is processing the same Notes database as any other
agent. I'm beginning to think that I just wrote bad code :-<
Thanks.
Randy
Nov 12 '05 #11
"EoRaptor" <rc*******@patmedia.net> wrote in message
news:fa**************************@posting.google.c om...
Ian <ia*****@mobileaudio.com> wrote in message news:<40**********@corp.newsgroups.com>...
Good questions. Wish I was certain of the answers. I'm using
LotusScript LSC:LC classes and connectors. Of course since DB2 and
Lotus come from the same shop, you'd think they'd play well
together...
Same shop? Same corporation, yes. Same development lab, no. Lotus remains
pretty much intact as a separate subsidiary located in the Boston area.
.. I am processing single rows. LotusScript agent code gets a Lotus Notes
document, and processes it to insert/update one row into one table.
The code, however, uses the LSX:LC fieldlist and connection classes so
I don't know what they are REALLY doing under the hood. I do have a
primary key and the agent explicitly identifies those two columns as
keys. As for actually commiting, again, I'm not issuing a direct SQL
statement. I use the connection class's "Action" method and specify
commit and assume it does so. I guess that I should be able to monitor
this someway in DB2 but I've only figured out (so far) how to get
snapshots and I've yet to catch anyting "in the act." Is there a way
to watch what's happening in real-time? I do have to say however, that
I took a look at snapshot for database and found 0 lock escalation
events. What I can't figure out is whether that info is cumulative or
if it's also instantaneous, i.e. 0 because I didn't catch one then or
cumulative and I really haven't had any. If the latter then I'm really
at a loss. Within any one agent, tables are populated serially so that
I don't get foreign key conflicts. Inserting one row at a time into my
contact table, shouldn't create deadlocks. However, I only get the
problem when I run multiple agents. I dont' see how this can create
deadlocks however, because each agent works wholly within its own
database and the primary key includes the Notes database replica ID.
So, no agent should be waiting for a commitment from another agent
because no agent is processing the same Notes database as any other
agent. I'm beginning to think that I just wrote bad code :-<
Thanks.
Randy


I am not certain what is happening, but if the LotusScript is explicitly
locking the entire table (SQL lock table statement) then a deadlock could
occur.

I would ask Lotus support about your problem.
Nov 12 '05 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Vincento Harris | last post by:
Hi There is an application that runs on sql server. The application selects/updates some few tables frequently Once there is even a select on this table .It blocks other users sometimes for...
1
by: cloverme | last post by:
The database is configured for single publisher, many subscribers, merge replication. The maintenance plan started to fail a couple of months ago and the database would not get backed up. After...
1
by: dawatson833 | last post by:
I want to set an alert for a specific table whenever an event has caused a deadlock to occur on the table. I understand how to set up an alert. But I don't know which error number to use for...
3
by: Uwe C. Schroeder | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi. I have the following little stored proc: CREATE OR REPLACE FUNCTION public.ib_nextval(varchar) RETURNS varchar AS 'DECLARE
8
by: Noni Ledford | last post by:
Hi - I have a database on a network, and each user logins using a shortcut from his PC to the shared database. There is a table created by a make-table query that locks up whenever more than one...
1
by: leon | last post by:
Hi all, I have an .mdb file (access database) which has been set to table level locking (i.e. not just records in question, but the whole table). The database is also in sharing mode (ie. not...
2
by: Jürgen Devlieghere | last post by:
Hi, We are creating event-driven multi-threaded applications on a daily basis. To help us solving deadlocks, we implemented a CriticalSection class that does dead-lock detection: an attempt to...
0
by: chenchutnv | last post by:
Hi, Please Give Some Examples For Table Locks And Releasing The Table. Let Me Know Clearly Abot The 'tablockx' Command.and How To Release The Table And Syntex For That......my...
1
by: TimHop12 | last post by:
Hello, I ran into a problem, where I am not sure how sql server is reacting - There is a table dbo.T1 with columns (OWNER, DATA)- now I have 3 stored procs - dbo.Begin, dbo.Copy, dbo.END. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.