Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tribune
Posting Yak Master
105 Posts |
 Posted - 2002-03-05 : 18:27:12
|
| I have this weird timeout error that I have been tracking for a while, and strangely enough it seems related to BLOB fields.I have a table with about twenty varchar/int fields, and a text(16) as the last field, in which user comments are placed. For example, these comments can be anywhere from 0 to 10,000 bytes. Now there are about 3,000 records in this table, and when I do a "select * from mytable order by 1 desc" in EM (enterprise manager) and then attempt to do an update statement a record in "mytable" from the query analyzer, it hangs.If I close the EM window, the query immediately finishes executing, and successive executions finish intantly.If I edit mytable and change the comments vield to a varchar(300) or so, I do not have this locking problem. Can someone give me some insight into how/why this problem is occuring? Is this a known sql bug or is it due to the engine not being intelligent enough to lock only small fields (ie varchar 300s).-trib |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-05 : 19:06:24
|
| I can't say for sure it's a bug, but anytime you use EM to access data while other processes (QA, web apps, VB/C++, ADO, etc.) are accessing it will almost certainly cause problems. EM is not intended to be robust...IMHO it's not even a half-assed...query tool.Let me ask you this: does this ever happen when EM is not involved? If you can't reproduce the problem otherwise, I would tend to think EM is locking the rows excessively. It has to be using some kind of client-side library (like ADO) to display the rows in the query grid window, it could be a cursor of some kind that's holding the locks.Text columns are even trickier, because the data is kept on separate pages, and EM could be escalating the lock to a table level instead of a page level. Once you close the EM window and those locks are released, the other process(es) can dive right in. The fact that successive runs finish instantly doesn't mean much, the data is in the cache after the first one.I don't feel that the "flaw" is in SQL Server per se, it's using EM for data access. If you have another front end for browsing data (like linked tables in MS Access) you're better off using it instead. |
 |
|
|
tribune
Posting Yak Master
105 Posts |
Posted - 2002-03-06 : 14:56:55
|
| This problem does also occur when EM is not involved, however I can only cause it to fail continuously by using EM...the rest of the time it seems like a 1/25 chance, and is being encountered by end-users.Our clients are getting a timeout error perhaps three times a week, with maybe 200 select queries during that time period, and maybe 20 update queries. Its really weird because our sql server has a high load from our other databases but a very low number of queries on this particular table in a separate database...I also was thinking along the lines of lock escalation from text fields, but I dont understand how this could effect our clients in the real world in our situation. There are about two statements that perform updates on the table, and finish instantly. With the small amount of traffic we have its almost statistically impossible for two users to perform update query's that would lock the table. Unless basic select queries will also perform an escalated lock? Then maybe there is a 1/100 chance because I have some select queriest on the table that are run perhaps 50 times per day. Note that these queries are all using ADO the default cursor type (forward only). But still if this were the case I would think I could be able to replicate the problem inhouse but I cant :(Anyway thanks for the reply robvolk |
|
|
|
Lou
Yak Posting Veteran
59 Posts |
Posted - 2002-03-06 : 23:49:46
|
quote:
when I do a "select * from mytable order by 1 desc" in EM (enterprise manager) and then attempt to do an update statement a record in "mytable" from the query analyzer, it hangs.
Tribune,Is your select statement still executing when you submit the update statement? If so, it hangs because of the nature of update locks. If you read the somewhat confusing documentation about update locks, It says something like this. 1. Any data modification statement uses a UPDATE lock2. UPDATE locks can co-exist with shared locks3. However before UPDATE locks can happen, SQL server must scan the table or index to determine which rows to apply the UPDATE lock. During this scan, SQL server will apply INTENT EXCLUSIVE LOCK which cannot co-exist with any shared locks. The data modification statement must wait until the select statement is finished. Long story short, various books actually recommend that you perform DSS and OLTP processing on different copies of your table on different databases. I find this advice to be wishful thinking. |
|
|
|
|
|
|
|
|
Locking Bug/Error