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 11 14012
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! =-----
"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.
"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.
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
"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.
"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 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 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 ?
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! =-----
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
"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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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.
...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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...
| |