Re: More than pessimistic record locking needed...

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance


"master" <master@xxxxxxxxxx> wrote in message
news:u$QBt4fmGHA.4076@xxxxxxxxxxxxxxxxxxxxxxx
Adding a timeout checking to my solution seems quite easy - a datetime
field
should be added to tLocks table, each record will be added with the
current
time. Each transaction would then start with deleting records that are
timed-out, i.e. the records whose datetime field is older than a session
timeout...

Potential progress then!

That would be nice, provided the whle solution worked ;-). But the users
reported it did not :-/.

This is why I am thinking about handling the locking not by the DB but by
the web application instead, using a global application cache and a lock
statement. I assume that each http session has a separate thread, am I
right?

Each session has its own thread safe object but I'm not sure holding
thousands of records in application or session is a good idea. Thats a lot
of work for a collection when a database is probably better optimised for
this type of processing.

Additionally, this solution should be a lot faster than blocking the
database with highly isolated transactions...

Yes - perhaps. More than 1 query can return a select so you can almost
guarentee at some point on a heavy database that you will get concurrency
issues, and you would essentially be recreating db functionality in asp.net.
With the correct locking inside a transaction SQL Server places an exclusive
lock on the page (or the row) so unique marking should be possible - leaving
you free to select exclusively and not worry about update problems due to
work in progress time passing, and reducing your memory overhead in
asp.net..

If I was you, I'd pop into one of the SQL server newsgroups and ask their
advice.

--
Regards

John Timney (MVP)



.

Relevant Pages

  • RE: Foreign Key Violations During Insert in Transaction
    ... parent row into the database, SQL Server generates a new identity value. ... that value does really exist even before you commit the transaction. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: statement not allowed within multi-statement transaction (Long)
    ... So if I do have an open transaction I can't figure ... > 100 Database does not exist in sysdatabases can not dettach ... > --Verify database Exiists for detach ... >> Wayne Snyder, MCDBA, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: remote sessions (database link, v$session)...
    ... if I understand what you want it is for session A running on ... database B to support the distributed transaction issued from A. ... The remote SID could be passed back. ...
    (comp.databases.oracle.misc)
  • Re: Form Arrays
    ... I'm a new person when it comes to PHP and I have a quick question. ... enter the information to the database until they are done. ... You issue a tag id when the header form is created, and carry it as a post variable through all the session. ... If they switch off and walk away, the transaction isn't marked as complete, and the data can be erased sometime later. ...
    (comp.lang.php)
  • Re: Insert Into Without Log
    ... level of logging of logged in the database ... transaction log, however, you can minimize this via setting the database ... >> Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.fulltext)