Hi there,



I'm developping a web site with banner ad rotation functionality and, 

after a period on-line, we noticed a problem: the banner countdown does 

not correspond to page the hits detected by the stats software (Web 

Trends) on the server.



The algorithm is very simple, there is an Application variable that keeps 

track of the next banner to be displayed, so they are visualized 

sequentially.



The banner information is kept in a SQL Server table with fields such 

as "banner_count", "url", "img_file"...



For this recordset the cursor type is "dynamic" and the lock was declared 

as "scroll_locks" (or pessimistic), to prevent concurrent updates.



We noticed that the banner visualization counter corresponds aproximately 

only to half of the page hits.



This probably means that when there are concurrent accesses, the table is 

not updated as it should. I think this is because the recordset is not 

locked while the counter information is read, decremented and then updated.



Two clients might read the contents of the recordset at the same time, 

decrement the corresponding field and both update with the same value. 

Shouldn't a lock of the type "scroll_locks" (or pessimistic) prevent this? 

Or do I have to setup a transaction to wrap the read, decrement and update 

operation?



Thank you for your time!

I am not quite sure how you've implemented this, but you can simply lock 

the application object:



Application.Lock

         ' Do what you have to do here, but do it quick

Application.Unlock



This will prevent two users from modifying the application at the same time.

Are you storing a recordset in the application? This is usually not a very 

good idea. A better approach is to execute your SQL - Update / Insert 

statement, and then release all resources again ASAP.

Connection pooling will handle the efficient use of connections anyway....



HtH



Imar





At 02:59 PM 2/14/2001 +0000, you wrote:

>Hi there,

>

>I'm developping a web site with banner ad rotation functionality and,

>after a period on-line, we noticed a problem: the banner countdown does

>not correspond to page the hits detected by the stats software (Web

>Trends) on the server.

>

>The algorithm is very simple, there is an Application variable that keeps

>track of the next banner to be displayed, so they are visualized

>sequentially.

>

>The banner information is kept in a SQL Server table with fields such

>as "banner_count", "url", "img_file"...

>

>For this recordset the cursor type is "dynamic" and the lock was declared

>as "scroll_locks" (or pessimistic), to prevent concurrent updates.

>

>We noticed that the banner visualization counter corresponds aproximately

>only to half of the page hits.

>

>This probably means that when there are concurrent accesses, the table is

>not updated as it should. I think this is because the recordset is not

>locked while the counter information is read, decremented and then updated.

>

>Two clients might read the contents of the recordset at the same time,

>decrement the corresponding field and both update with the same value.

>Shouldn't a lock of the type "scroll_locks" (or pessimistic) prevent this?

>Or do I have to setup a transaction to wrap the read, decrement and update

>operation?

>

>Thank you for your time!

Dionisio:



Very good article on Developing a Customizable Banner Rotation System:



http://www.4guysfromrolla.com/webtech/010301-1.shtml



HTH,



Bruce