1. A real-life, real-time problem...
Hello people,
here's my situation:
1. several hundred users are inserting rows in a table, let's say one row
per user per 15-20 minutes.
2. a small group of people (let's say 10 people: the processing team) are
fetching the new rows one at a time, to update a couple of fields. This row
update takes abt 20-30 seconds.
3. the whole thing is done over the web, using IE5, ASP and SQL2000.
the musts:
1. All users must be authenticated at the web level but IIS uses the same
SQL server login for all.
2. A member of the processing team should not fetch a row that is being
processed by another processing team member.
3. A lock to process a row should not last more than 5 minutes.
So far, I'm storing an ADO connection object that holds a dynamic
scroll_locks with readpast cursor in the Session object for the processing
team users (not a recommended thing to do, but it seems the only way to hold
the row locked while the user updates the fields - and there are only very
few such users).
I'm setting Session.Timeout to 5 minutes for the pages that present rows for
processing, 20 minutes elsewhere. The row-update pages also auto-refresh
every 3 minutes, presenting the next row if any. I'm not using transactions,
since updates affect a single row each time and use a single T-SQL
statement.
If everything goes smoothly, this seems to work fine.
The problem is that if a session closes abnormally (e.g. user just closes
the IE window presenting a row for processing), the lock can last for a long
time, until the web server times-out the session, so no others can process
this row. If the user had a second IE window with the same session, opened
after he requested a row for processing, this timeout can reach 20 minutes.
Moreover, it seems very difficult if possible at all to find the SQL Server
process ID of the stalled session in order to kill it manually or using a
background process.
I'd appreciate any ideas on how I can improve or solve this locking problem.
Costas Andriotis
Outsource S.A.
2. Remotely administering an Access database using ODBC link.
3. Help needed in finding MAX and MIN in real time FIFO data
4. FOXPRO 2.5 .EXE PROBLEM
5. Accees97 - SQL Server linked tables deadly slow
6. real time app. on Oracle
7. Q: Application Type (SetTitle)
8. Oracle for a near real-time App
9. App working in Debug but not real time
10. Real problem: need some help!
11. need ADO Help Pessimistic Locking + timing out
12. near real time data from DB2,SQL and Paradox needed on SQL box
by