What is there to lock? You're building a logging facility, right? This means you're doing INSERTs exclusively, I think. "User 1 visits page 1", "User 1 visits page 2", "User 2 visits page 1" etc etc.

In such a scenario, you have nothing to lock. Just send your INSERT statements to the database. The database engine will deal with concurrency issues regarding writing to the database at the same time; this is not something you have to control yourself.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Imar,Madhu

well madhu you gave me the answer in the recordset querry that i had asked ... u see as there is a CURSORTYPE there is also a LOCKTYPE which u can set to the desired value .... depends on the priorty of the transaction that you are going to perform on the database ...

well imar i didnt know that access has its own locks which are applied automatically bcoz as i said i have always used explicit lock on my ACCESS database .. i only know that ORACLE uses implicit locking as well as explicit locking mechanisms ...

finally i have never used MYSQL or SQL server and hence dont know anything about them ...

Sudhan.

What explicit locks in Access are you talking about? Can you give a code example? Or are you talking about the LockType enumeration?

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Another Night In - Tinderstick by Faithless (Track 8 from the album: Back To Mine) What's This?

I'm not talking about file locks in Access. I wanted exclusive write access to a text file while a line was being written to it.

As there does not appear to be any facility in ASP/VBScript to lock a text file, I am using an Application variable to indicate that the file is in use while a line is being written.

The purpose of the log file is for tracking only, in an attempt to find out why a cookie key value is being lost. I decided not to use a database, as some have suggested, as the size of the database would grow rapidly with what would be, in a few hours, redundant data and there is no compelling reason for using a database.

Thanks to all for your input.

Pat

-------------------
System 3 2000 Limited
www.system3-2000.co.uk

Imar,

i am talking about LOCKTYPE enumeration ...
is it different from locking

Sudhan.

No, but I thought you said you were locking Access yourself. Using the LockType is the correct way to indicate to the database how you want your locks applied.....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Stay by Tricky (Track 1 from the album: Vulnerable) What's This?

I know it is wrong to post PHP codes in this forum. But I want to explain a situation and the code is not that difficult also.

if(mysql_query("LOCK TABLES tbl_basic_submission WRITE",$cid_webilicious))
    {
        }
    else{
        echo(mysql_error());
    }

    $qry_insert_basic_submission = "insert into tbl_basic_submission (".$str_fields.") values(".$str_values.")";
    if(mysql_query($qry_insert_basic_submission,$cid_m ine))
    {
    }
    else{
        echo(mysql_error());
    }

    $i_order_id = func_getfield($str_connection,"select max(order_id) from tbl_basic_submission");
    //echo($i_order_id);
    if(mysql_query("UNLOCK TABLES",$cid_mine))
    {
    }
    else{
        echo(mysql_error());
    }

This will first lock the table. Then insert one record. Then get the primary key of the newly inserted record. (This value is needed for inserting a number of records into another table). This can be nicely done in PHP.

However the same thing could not be done in ASP, I think. Because, there is no way to lock the table for write operation. So the primary key value returned may not be correct as in between another record may have got inserted.

I am inviting your opinions into this issue. This case does require locking.

Hi Madhukp,

I would suggest you to post that in a separate/different topic, as this gets diverted from the original topic posted by Pat. Also Pat might be still expecting something related to his requirement, but only to read all these posts, as he has said Cheers!

_________________________
-Vijay G
Strive for Perfection

Yeah, true, this is an issue with databases like Access.

There are a couple of ways around this, but probably not as easy as the way you do it in MySQL. None involve locking, though. IMO, locking will hinder scalability and performance of your application, while there are other ways available. (If it was as easy as in your PHP / MySQL example, I'd use it though)

1. Create the ID before you do the insert. Create a query that returns the next ID for a table. Not a very nice solution, though.

2. Use GUIDs for the ID. They are globally unique so a conflict will not occur. You can create a GUID before you execute the INSERT statement, and then pass in that GUID as the ID.

3. Use AddNew(). I think if you create a recordset and then use the AddNew method (as opposed to direct SQL statements), Access will correctly handle the new record. So, after you call Update, MyRecordset("ID") will contain the new ID.

4. If your records have a unique signature, you could requery the entire record for all its fields to get the ID. Then again, a composite key might be more appropriate.

5. Retrieve the ID using @@IDENTITY. Not 100% guaranteed to return the right ID, but for most applications I believe it to be close enough.

In SQL Server you could use @@SCOPE_IDENTITY to limit the scope of the ID that is returned. In combination with a Stored Procedure, that would be the way I would do it in any professional application, but it requires the use of SQL Server.

Cheers,

Imar




---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Millennium by Ed Rush & Optical (Track 4 from the album: Wormhole #1) What's This?

Let us continue discussion on this in the issue

<a href="http://p2p.wrox.com/topic.asp?TOPIC_ID=15255" target="_blank">DB Table locking</a>

as Vijay has suggested.