紧急求救：Transaction Locking- Track table要是一直update delete会怎样？

雨吟

请问，multiuser application 难免会遇到的database locking & blocking 要怎么办？

情形大约如下：
Transaction begin

Update Table A [Status]
Select Table A [Status] Where A.BID = aaa
[size=-1]根据以上的select到的结果：
Update Table B [Status] where B.BID = aaa
Select Table B [Status] where B.CID = bbb
[size=-1]根据以上的select到的结果：
Update Table C [Status] where C.CID = bbb
Update Table C [StatusOrg] where C.CID = bbb

Transaction Commit

我的系统是用ODBC connection, table 没有primarykey

如果有两个人同时在做这个operation,database 就会lock着其中一方。。结果，有时候其中一个人会成功，有时候两个人都失败。。。

我真的不知道要怎么样handle...请有经验的大大们教我吧~

感激不尽，谢谢~

[ 本帖最后由 雨吟 于 20-12-2006 11:32 AM 编辑 ]

悠哉亭

你用什么资料库平台?

通常 developer 问及此事, 问题大多数出在于application以下范围:

Transaction begin
-- Point A
Update Table A [Status]
Select Table A [Status] Where A.BID = aaa
根据以上的select到的结果：
Update Table B [Status] where B.BID = aaa
Select Table B [Status] where B.CID = bbb
根据以上的select到的结果：
Update Table C [Status] where C.CID = bbb
Update Table C [StatusOrg] where C.CID = bbb
-- Point B
Transaction Commit

1. point B后没有立即commit;
2. point A 到 point B, 不小心的用到multiple transaction or connection
3. point A 到 point B 用的时间太久, 其他waiting的user timeout.

雨吟

原帖由 悠哉亭 于 14-12-2006 11:24 AM 发表
你用什么资料库平台?

通常 developer 问及此事, 问题大多数出在于application以下范围:

Transaction begin
-- Point A
Update Table A
Select Table A  Where A.BID = aaa
根据以上的select到的结果： ...

我用的是SQL server 2000
基本上，connection 和 Transaction 都开对了，而且也有commit到。。。因为如果这边就错了，老早应该就不可以用了。。现在问题只是出现在２个或以上的user同时使用...

比较可能的是：
3. point A 到 point B 用的时间太久, 其他waiting的user timeout.

然而，point A - Point B要做的东西已经是无可避免一定要做的了。。。有什么办法可以handle接踵而来的block & lock 呢？

goatstudio

1. Is the commit is necessary? If not, release it.
2. Is the update is back to same row record? If yes, I would suggest you put a flag to lock it up, so that user will know someone is locking it.
3. If the update is not back to same row, something unusual is happen, you gotta check your code.

Bank update usually is critical but they update back to different row, if update back to same row, the locking still there.

p/s: I need to use english here again...

悠哉亭

原帖由 雨吟 于 14-12-2006 12:14 PM 发表


我用的是SQL server 2000

SQL server 2000 是用 page locking 不是 row locking.

原帖由 雨吟 于 14-12-2006 12:14 PM 发表
基本上，connection 和 Transaction 都开对了，而且也有commit到。。。因为如果这边就错了，老早应该就不可以用了。。现在问题只是出现在２个或以上的user同时使用...

connection 和 Transaction 都开对 与 开到multiple session  是不同的东西. 你可以用SQL Enterprise Manager 来check sessions.

雨吟

原帖由 goatstudio 于 14-12-2006 12:38 PM 发表
1. Is the commit is necessary? If not, release it.
2. Is the update is back to same row record? If yes, I would suggest you put a flag to lock it up, so that user will know someone is locking it.  ...

commit 是一定要有．．因为这个process一定要从头到尾完成．

Is the update is back to same row record．．这个无法避免．．因为它的process类似这样子的：

1．　user A Complete 小明的资料, user B complete 小华的资料．
2.   去找小明和小华的爸爸－大明，大明所有的儿子的资料都完成了吗？如果是，complete 大明的资料

３．去找大明的爸爸　－老明，老明所有的儿子（大明）和孙子（小明小华）的资料完成了吗？如果是，complete 老明的资料．

在　１，　不同的row is update
在　２，3　same row is update.

我想，这也是导致lock吧？怎么样handle这样子的情形？
希望你们明白我讲什么

雨吟

原帖由 悠哉亭 于 14-12-2006 12:52 PM 发表


SQL server 2000 是用 page locking 不是 row locking.



connection 和 Transaction 都开对 与 开到multiple session  是不同的东西. 你可以用SQL Enterprise Manager 来check sessions.

不好意思，我的technical knowledge不是很好,可以详细点告诉我是什么吗？

goatstudio

原帖由 雨吟 于 14-12-2006 02:45 PM 发表


commit 是一定要有．．因为这个process一定要从头到尾完成．

Is the update is back to same row record．．这个无法避免．．因为它的process类似这样子的：

1．　user A Complete 小明的资料, user B  ...

1. What you actually intend to do when the row update is crashed with another update? In your situation, your update must done from start to end, so some other people need to wait? (That is why you have "check in" and "check out" in some of the system.)

2. Try to give your table an index, or key, so that during row locking, the response will be faster. Without index, your sql server actually examine the entire table to check for locking. With index, your database will response you with an error message faster. However, you still need to think how you gonna handle row update crash.

3. I would recommend check in and check out.

4. If you really have no choice, update your data with keyword NOLOCK. But you will get inaccurate data. Look for T-SQL manual in you quesry analzyer on how to use NOLOCK.

[ 本帖最后由 goatstudio 于 14-12-2006 03:16 PM 编辑 ]

雨吟

原帖由 goatstudio 于 14-12-2006 03:04 PM 发表


1. What you actually intend to do when the row update is crashed with another update? In your situation, your update must done from start to end, so some other people need to wait? (That is why ...

嗯～谢谢你提醒了我的情况是Update同一个row的问题．．．

这几天一直在想的是transaction deadlock的问题．．．．

想clarify一下，一个transaction在走着的时候，他的lock是什么形式的locking?row level? table level? 还是什么其它的？

请问还有什么要注意的？

我们组里有经验的走了．．．．希望指教一下我们，以免忽略了其他要注意的事情．．．

雨吟

有没有办法让transaction 排队？让一个走完了才到第二个？那就不会有update同一个row的事情了？

goatstudio

原帖由 雨吟 于 14-12-2006 04:22 PM 发表

想clarify一下，一个transaction在走着的时候，他的lock是什么形式的locking?row level? table level? 还是什么其它的？

SQL Server will automatic get the best locking mechanism for you, unless you put a hint like ROWLOCK, NOLOCK...

Now you need to decide what you suppose to do during row update crash, then you will able to know next step.

Take a look here: http://www.developerfusion.co.uk/show/1688/4/

I would suggest you use NOLOCK first, NOLOCK will let you SELECT those uncommitted data.

悠哉亭

goatstudio 大大建议的URL, 你可以看到关于
SQL server 2000 的 page lock 和 row lock.

明显的是process引起deadlocks.

重复你所说的process:

1．　user A Complete 小明的资料, user B complete 小华的资料
Current Situation:
小明的资料 (locked by user A), 小华的资料 (locked by user B)

2.   去找小明和小华的爸爸－大明，大明所有的儿子的资料都完成了吗？如果是，complete 大明的资料

(Update 大明所有的儿子的资料?小明和小华?)

Current Situation:
1. 小明的资料 (locked by user A), 小华的资料 (locked by user B)
2. User A attempt to lock 小华的资料, User B attempt to lock 小明的资料
3. User A waiting B to release lock, User B waiting A to release lock
deadlock.....


你可采取goatstudio大大建议的方法防止deadlock.
记得index 哦.

我的建议:如果可以的话,重组process的sequence.

雨吟

嗯～～～应该是这样：

User A
update 小明 = A table, 小明的row.
然后，update 小明的爸爸　＝　B table, 大明的row.**
然后，update 大明的爸爸　＝　C table, 老明的row.**

User B
update 小华 = A table, 小华的row.
然后，update 小华的爸爸　＝　B table, 大明的row.**
然后，update 大明的爸爸　＝　C table, 老明的row.**

两个同时跑的结果，就是有两个user 同时在update
-B table, 大明的row.
-C table, 老明的row.

同样table同样row 发生了．．．．

感谢你们．．我会去看看那个文章怎么办．．．可是现在我们其实已经在用着nolock...

刚刚发现，两个同时insert 同一个table的transaction也是会挂掉，这个又该怎么办？ transaction是不是会把整个table lock 住？

[ 本帖最后由 雨吟 于 14-12-2006 06:04 PM 编辑 ]

悠哉亭

不对,如果你所说的process是正确的话, 不应该有deadlock.

你可以用2个Query Analyzer simulate User A 和 User B.

1.User A 开始 Begin Trans (Query Analyzer 1), User B 开始 Begin Trans (Query Analyzer 2)
User A update 小明 = A table(小明的row.)
User B update 小华 = A table(小华的row)

Current Situation:
小明的资料 (locked by user A), 小华的资料 (locked by user B)


2.然后 concurrently,

User A update 小明的爸爸 ＝　B table(大明的row.**)
User B update 小华的爸爸 ＝　B table(大明的row.**)

Current Situation:
小明的资料 (locked by user A), 小华的资料 (locked by user B)
大明的资料 (locked by user A), B waiting for locking 大明的资料

3.然后,
User A update 大明的爸爸　＝　C table(老明的row.**)
User B waiting......
小明的资料 (locked by user A), 小华的资料 (locked by user B)
大明的资料 (locked by user A), B waiting for locking 大明的资料
老明的资料 (locked by user A), ....(B still at step 2)


4.然后,
User A Commit
User B 将获得继续 step 2, Step 3 and commit.


猜想是你的transaction control有问题

雨吟

原帖由 悠哉亭 于 14-12-2006 06:58 PM 发表
4.然后,
User A Commit
User B 将获得继续 step 2, Step 3 and commit.

结果是

User A Commit
User B block, lock 一大堆的然后timeout...

现在，还发现一同insert也是挂～～～

transaction isolation level = read commited...
select query 全部都放了with nolock...

还有什么地方能够注意吗？

[ 本帖最后由 雨吟 于 14-12-2006 07:07 PM 编辑 ]

悠哉亭

请问你需要几久来做全部 process?

雨吟

原帖由 悠哉亭 于 14-12-2006 07:19 PM 发表

请问你需要几久来做全部 process?

怎么样看多久？

再多一点infomation 是：

-全部都是 ASP.NET call SQL server 的dynamic SQL query
-database server 和 web server 在同一部电脑

[ 本帖最后由 雨吟 于 14-12-2006 09:28 PM 编辑 ]

悠哉亭

哦..sorry, 我是问多久的时间来执行所有的SQL.

你是不是有执行一些 transaction setting
在 Begin Transaction 前面?

悠哉亭

原帖由 雨吟 于 14-12-2006 07:04 PM 发表
...
还有什么地方能够注意吗？

goatstudio大大建议你加 index. 加了吗?

雨吟

原帖由 悠哉亭 于 14-12-2006 11:12 PM 发表



goatstudio大大建议你加 index. 加了吗?

嗯~这个明天要和大家商量。。。因为有一个senior说，index用得不好，performance反而还要慢~所以总是要问他怎么样加。。。。