A Simple Problem. But....

SunilKumar111

Hi everybody,

I have a requirement in which a user fetches some records from a table. I want that records to be locked for that user so that when other fetches records these should be records not fetched earlier. Currently I am using a stored proc in which I rerun the select statement to lock the records in temp table. Any better solution

Thanks in advance

Bruce dBA


You don't want users to even be able to READ rows that have been selected and locked Not sure you can do that, as they can SELECT even while the locks are in place. Sounds like you are doing it in a way that makes sense already... that you control which rows are "checked out" and then not allow others to get them untill they are checked in... You could add a status field on your table and then use that to allow an application to select those rows or not... Bruce

DaleJ

Locks can be a good thing and they can also be devastating if mismanaged.

You can tweak the isolation level to help most situations.

How long do you plan to hold these locks

Why do you need to manage them

What is your objective

Please provide a little more detail and depth on your situation and needs.

Rob Farley

How about updating the table, using the OUTPUT clause to return what you've changed, like:

UPDATE thetable
SET islocked = 1
OUTPUT DELETED.*
WHERE islocked = 0
AND ... --other criteria goes here

This way, you'll get the locked rows, and the next user won't be able to. If you want to mark who locked it, then perhaps use a different type for islocked.

Hope this helps.

Rob

SunilKumar111

Hi Dale,

I am making a call center application in two or three agents make outbound calls to customers. If there are 100 calls to be made then each of my agent sees these 100 entries and makes multiple calls to the same customer. I want that when an agent logs in then it should some fixed number of entries(say 25) and when another agent logs in then it should see entries not fetched earlier.

In this way each agent will have different number for outbound call. I am not locking the records per se. In my main table I have a primary key. When I fetch the records I make an entry in the temp table for that records. For other agent I fetch the records that are not in temp table.

Arnie Rowland

I would add a column to the table for the [Agent ID], have a Agent first 'mark' n number of rows with his/her [Agent ID], and then retreive the rows that were marked.

DaleJ

Ah.

It's an allocation challenge.

Like Arnie says, add the agent id to the call record.

Or

Create an intermediate cross-reference/assignment table that matches call records to agent's.

you can then easily manipulate this table with inserts, updates, deletes.

And when you create a new set of assignments you just get those calls that aren't present in the assignment table.

SunilKumar111

Hi Arnie,

Thanks for sparing time. This is exactly what I am doing currently but I don't give agent to mark the rows. Some fixed number of rows are marked(in a temp table) for that agent. And these rows are released when the agent logs out.

Thanks

Rob Farley

Did you try using the OUTPUT clause of an UPDATE statement Regardless of whether you call use "allocatedto = system_user" or "islocked = 1", the effect will be similar.

(Following the order of posts here is awkward - your later post appeared higher than mine in the thread tree)

Rob