question about locking

Hi,
I have a table where we save loan applications.
I've created a web page for processing these applications.
Now there are up to 10 people using the site, and they sometimes end up with
the same application.
In the table there is a column called STATUS. If the value of STATUS is -1,
the application is ready for processing.
The Id for the application is then selected, the table is updated and the
status changes to 1. Then the row is selected based on the Id.
They started out with only two people using the application, so everything
worked fine and everybody was happy, but not anymore.
So now Im looking into locking!
Will "SELECT Id FROM BLABLA WITH(ROWLOCK)" fix the problem ? Or is there a
better way to do this ?
BR,
SteinHi Stein
Locking is a huge topic, so you really should read about it in the Books
Online before you start changing your code.
You need to know that locking is not a problem in SQL Server. SQL Server
acquires and releases locks constantly. The problem is BLOCKING, when a lock
is held for too long and other processes block on the lock.
From your limited description, it's impossible to tell exactly what the
problem is, so we really can't know what will fix it.
The SELECT ... WITH (ROWLOCK) will only hold the lock for the duration of
the SELECT. As soon as a result set is returned, the lock will be released.
So my guess is that it is not nearly enough to solve your problem.
You can use sp_who to see who is blocked, or use the current activity screen
in Enterprise Manager.
To solve this problem, you are most likely going to need to determine:
Which process is blocked and what exact statement is it trying to execute?
What process is it blocked by, and what resources are being locked by that
blocker process.
Also:
How big is the table?
What indexes are on it?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Stein Lundbeck" <fatlaces@.newsgroups.nospam> wrote in message
news:eR26neCeGHA.3484@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a table where we save loan applications.
> I've created a web page for processing these applications.
> Now there are up to 10 people using the site, and they sometimes end up
> with the same application.
> In the table there is a column called STATUS. If the value of STATUS
> is -1, the application is ready for processing.
> The Id for the application is then selected, the table is updated and the
> status changes to 1. Then the row is selected based on the Id.
> They started out with only two people using the application, so everything
> worked fine and everybody was happy, but not anymore.
> So now Im looking into locking!
> Will "SELECT Id FROM BLABLA WITH(ROWLOCK)" fix the problem ? Or is there a
> better way to do this ?
> BR,
> Stein
>|||Hi Kalen,
I'll try to explain.
This is the table:
CREATE TABLE [dbo].[App_Application](
[LOPENR] [int] IDENTITY(1,1) NOT NULL,
[SOKNADID] [char](14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IDENT] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[STATUS] [int] NULL,
CONSTRAINT [PK_SOKNAD_tmp] PRIMARY KEY CLUSTERED (
[LOPENR] ASC
) ON [PRIMARY]
) ON [PRIMARY]
The table has over 100 columns, so I just included the first four.
So, in this table we save all new applications.
I use the value of the STATUS column to see if the applications is under, or
done processing.
And this is the SP:
LTER PROCEDURE [dbo].[App_GetNextApplication]
@.Ident VARCHAR(20),
@.UPDATESTATUS BIT = 0
AS
BEGIN
DECLARE @.appIdentity VARCHAR(20),
@.Tag VARCHAR(20)
IF @.Ident IN (
SELECT IDENT
FROM App_Application app
WHERE app.IDENT = @.Ident
AND app.STATUS IN (
SELECT
VERDI
FROM
ApS_ApplicationStatus
WHERE
TAG = '<behandling>'
)
)
BEGIN
--Bruker har sknader som er til behandling EDIT: User allready has an
applcation under processing
SET @.tag = '<behandling>'
END
ELSE BEGIN
--Bruker har ingen sknader som er til behandling, hent ny EDIT: User
has no application under processing, get a new one
SET @.tag = '<ikke_registrert>'
END
SET @.appIdentity = (
SELECT TOP 1 app.SOKNADID
FROM App_Application app
WHERE app.STATUS IN (
SELECT VERDI
FROM ApS_ApplicationStatus
WHERE TAG = @.tag
)
ORDER BY app.POST_DATO ASC
)
IF @.UPDATESTATUS = 1 BEGIN
DECLARE @.STATUS AS VARCHAR(20)
EXEC ApS_GetApplicationStatus '<behandling>', @.STATUS OUTPUT
UPDATE App_Application
SET STATUS = @.STATUS,
STATUS_FORANDRET_IDENT = @.Ident,
STATUS_FORANDRET_DATO = GETDATE(),
IDENT = @.Ident,
REG_DATO = GETDATE()
WHERE SOKNADID = @.appIdentity
END
SELECT @.appIdentity AS 'SOKNADID',
app.IDENT,
app.POST_DATO
FROM App_Application app
WHERE app.SOKNADID = @.appIdentity
END
So then, where I set the @.appIdentity, I need to make sure that two users
never end up with the same identity.
Or maybe I have to lock until the @.UPDATESTATUS section is done ?
I dont know, I need help! :)
BR,
Stein Lundbeck
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:<uRoQXwCeGHA.3632@.TK2MSFTNGP02.phx.gbl>...
> Hi Stein
> Locking is a huge topic, so you really should read about it in the Books
> Online before you start changing your code.
> You need to know that locking is not a problem in SQL Server. SQL Server
> acquires and releases locks constantly. The problem is BLOCKING, when a
> lock is held for too long and other processes block on the lock.
> From your limited description, it's impossible to tell exactly what the
> problem is, so we really can't know what will fix it.
> The SELECT ... WITH (ROWLOCK) will only hold the lock for the duration of
> the SELECT. As soon as a result set is returned, the lock will be
> released.
> So my guess is that it is not nearly enough to solve your problem.
> You can use sp_who to see who is blocked, or use the current activity
> screen in Enterprise Manager.
> To solve this problem, you are most likely going to need to determine:
> Which process is blocked and what exact statement is it trying to execute?
> What process is it blocked by, and what resources are being locked by that
> blocker process.
> Also:
> How big is the table?
> What indexes are on it?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Stein Lundbeck" <fatlaces@.newsgroups.nospam> wrote in message
> news:eR26neCeGHA.3484@.TK2MSFTNGP04.phx.gbl...
>|||Hello Stein,
It seems that in the whole SP you read a row from table and you do n't want
other sessioin to update and/or read the row before you update/process the
row in the SP. You may want to use consider set "repeatable read isolation
level" for this sitaution and put all these query in one transaction. The
row shall be locked in the whole transaction. Also, you may use hint
"xlock" for select query so that it could lock other reads.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.