表锁和防止死锁 [英] Table Locks and Preventing Deadlock
问题描述
我使用LotusScript将数据从许多Lotus Notes数据库导出到DB2数据库
。 LotusScript代理在EACH
update / insert之后提交。不过，我继续在
死锁或超时时获得事务回滚。当我获得锁的快照时，我看到一个
的导出代理正在获取表锁而不是行锁。
我猜这是什么导致超时。问题：我如何判断
是死锁还是超时？如果是因为
表锁，我该怎么做才能防止它？
LOCKLIST = 64000
DLCHKTIME = 15000
MAXLOCKS = 20
LOCKTIMEOUT = 45
谢谢。
兰迪
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
推荐答案
EoRaptor写道：
EoRaptor wrote:
我使用LotusScript将数据从许多Lotus Notes数据库导出到DB2数据库。每次更新/插入后，LotusScript代理都会提交。尽管如此，我仍然在死锁或超时时获得事务回滚。当我获得锁的快照时，我看到一个导出代理正在获取表锁而不是行锁。
我猜这是导致超时的原因。问题：我如何确定它是死锁还是超时？如果是因为表锁，我该怎么做才能防止它？
LOCKLIST = 64000
DLCHKTIME = 15000
MAXLOCKS = 20
LOCKTIMEOUT = 45
谢谢。
Randy
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
您需要查看错误中的原因代码：
SQL0911N，原因码2 =死锁
SQL0911N，原因码68 =锁定超时
您可能需要考虑许多参数：
LOCKTIMEOUT - 指定时间锁等待将持续到SQL0911N之前
LOCKLIST / MAXLOCKS - 控制应用程序可以锁定的数量
之前需要的数量导致锁定升级
祝你好运，
----- =通过Newsfeeds.Com发布，未经审查的Usenet新闻= -----
http://www.newsfeeds.com - 世界排名第一的新闻组服务！
----- ==超过100,000个新闻组 - 19个不同的服务器！ = -----
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>在消息中写道
news：fa ************************** @ posting.google.c om ...
"EoRaptor" <rc*******@patmedia.net> wrote in message
news:fa**************************@posting.google.c om...
我使用LotusScript将数据从许多Lotus Notes数据库导出到DB2数据库。每次更新/插入后，LotusScript代理都会提交。尽管如此，我仍然在死锁或超时时获得事务回滚。当我获得锁的快照时，我看到一个导出代理正在获取表锁而不是行锁。
我猜这是导致超时的原因。问题：我如何确定它是死锁还是超时？如果是因为表锁，我该怎么做才能防止它？
LOCKLIST = 64000
DLCHKTIME = 15000
MAXLOCKS = 20
LOCKTIMEOUT = 45
谢谢。
Randy
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
我不确定这会有所帮助，但尝试将maxlocks增加到60.这意味着
锁定升级只会在代理商使用超过60％的
锁定列表的情况下发生。
但是，锁定通常在每次提交时释放，所以我不知道你为什么要获得锁定升级，除非LotusScript代理专门要求
获取程序中的表锁。如果发生了这种情况，您应该能够在快照中跟踪锁定
升级。
每次更新/插入后执行提交效率不高，所以我想
考虑将Lotus中的数据导出为ASCII或del格式，并使用带有INSERT_UPDATE的DB2导入命令来使用
。选项（将
导入数据的行添加到目标表，或者使用匹配的主键更新目标
表的现有行）并将COMMITCOUNT设置为100.这应该
将经过的时间减少至少一半，但仍与其他应用程序提供合理的并发
并发性。
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>在留言新闻中写道：< qu ****************** @ news.uswest.net> ...
"Mark A" <ma@switchboard.net> wrote in message news:<qu******************@news.uswest.net>...
我是不确定这会有所帮助，但是尝试将maxlocks增加到60.这意味着只有当代理使用超过60％的锁定列表时才会发生锁定升级。
但是，锁定通常在每次提交时释放，因此我不知道为什么你会获得锁定升级，除非LotusScript代理特别要求程序中的表锁定。如果发生这种情况，您应该能够跟踪快照中的锁定
升级。
在每次更新/插入后执行提交效率不高，所以我会考虑导出从Lotus到ASCII或del格式的数据，并使用带有INSERT_UPDATE的DB2 import命令。选项（将导入数据的行添加到目标表，或使用匹配的主键更新目标表的现有行）并将COMMITCOUNT设置为100.这应该减少经过的时间至少一半，但仍然提供与其他应用程序的合理的并发。
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.
谢谢。我不愿增加MAXLOCKS，因为我有4个Lotus Notes
代理同时运行 - 每个代理在
时间导出1个LN数据库。不过，我会尝试一下 - 总能把它改回来。此外，它确实看起来像是一个锁定升级问题。当我看到
快照时，我看到其中一个代理已经获得了表IX锁定。
仍然，我不知道为什么不是''提交后发布。
至于提交频率和ascii导入，这是一个持续的过程。
每个LN DB每天更新一次DB2表。 Notes数据库
是我们的生产应用程序 - 移民工作流程
申请。所以，我正在使用代理来自动化这个过程。 FWIW，
在我开始进行这些回滚之前，代理商每秒处理大约150个Lotus Notes文档
- 完全可以接受。
再次，谢谢。
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.
这篇关于表锁和防止死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
