Excessive locking

I inherited a hell hole of a system.
This is how a key is generated in my current system:
--GET A NEW ORDERID
SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
(TABLOCKX))
--UPDATE LAST ORDERID TABLE
UPDATE LAST_ORDERID SET OrderID = @.OrderID
SET @.ErrCount = @.ErrCount + @.@.Error
--END UPDATE LAST_ORDERID
Last_orderid has one column / one row, and is pretty useless IMO.
Past the idea of creating an identity column on Orderid in Orders would
it be good practice to put this in a begin transaction, commit transaction
to reduce the lock time?
TIALooks like your identity in the Orders table is what's needed, but rejecting
that idea (racking my brain to think or a reason why)...
You could make the orderid in Last_OrderID an identity column and let it
create the rows. You will need another column to insert (use NULLbit) If
you use @.@.Scope_Identity you don't need the lock.
The additional storage space is minimal. You could even delete that table
every night if that is an issue (note: Do not use Truncate. It resets the
identity whereas delete does not).
Your DDL would look like:
Drop Table Last_orderid
Go
Create Table Last_orderid
orderid int identity (XXX, 1),
NULLbit bit
Go
--Where XXX is the next orderid at the time this script is run
Your query would look like:
Declare @.newOrderID int
Insert into Last_orderid (NULLbit) Select NULL
Set @.newOrderID = @.@.Scope_Identity
--Update Order Table
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:ulVZ7%23K4FHA.1184@.TK2MSFTNGP12.phx.gbl...
>I inherited a hell hole of a system.
> This is how a key is generated in my current system:
> --GET A NEW ORDERID
> SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
> (TABLOCKX))
> --UPDATE LAST ORDERID TABLE
> UPDATE LAST_ORDERID SET OrderID = @.OrderID
> SET @.ErrCount = @.ErrCount + @.@.Error
> --END UPDATE LAST_ORDERID
>
> Last_orderid has one column / one row, and is pretty useless IMO.
> Past the idea of creating an identity column on Orderid in Orders would
> it be good practice to put this in a begin transaction, commit transaction
> to reduce the lock time?
> TIA
>
>
>
>
>|||Thanks Joe!
"Joe" <joe@.aol.com> wrote in message
news:u0Lx4fL4FHA.3444@.tk2msftngp13.phx.gbl...
> Looks like your identity in the Orders table is what's needed, but
> rejecting that idea (racking my brain to think or a reason why)...
I have found 28 SP's that insert into this table, and I'm real afraid that
in our years of code some people have written dynamic crap to do likewise.
I then have to follow this to about 15+ other tables in our system.

> You could make the orderid in Last_OrderID an identity column and let it
> create the rows. You will need another column to insert (use NULLbit) If
> you use @.@.Scope_Identity you don't need the lock.
Queston I have is when will that brainfart lock be released if no commit
tran is in the SP? At the end of the SP? If so that is what I need to
STOP. I am getting killed in an invoicing run that does similar locking,
but that SP is huge.
What I want to do is convert over to identity, but I have to walk softly,
and look for a real big stick. I am going to deal with one primay key
that is used in the invoicing run above. It's got a small impact for users
who could be affected.

> The additional storage space is minimal. You could even delete that table
> every night if that is an issue (note: Do not use Truncate. It resets the
> identity whereas delete does not).
> Your DDL would look like:
> Drop Table Last_orderid
> Go
> Create Table Last_orderid
> orderid int identity (XXX, 1),
> NULLbit bit
> Go
> --Where XXX is the next orderid at the time this script is run
>
> Your query would look like:
> Declare @.newOrderID int
> Insert into Last_orderid (NULLbit) Select NULL
> Set @.newOrderID = @.@.Scope_Identity
> --Update Order Table
>
> "__Stephen" <srussell@.transactiongraphics.com> wrote in message
> news:ulVZ7%23K4FHA.1184@.TK2MSFTNGP12.phx.gbl...
>|||Put it in a transaction to assure correct behavior, it won't save any
time, though.
J.
On Thu, 3 Nov 2005 13:49:47 -0600, "__Stephen"
<srussell@.transactiongraphics.com> wrote:

>I inherited a hell hole of a system.
>This is how a key is generated in my current system:
>--GET A NEW ORDERID
>SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
>(TABLOCKX))
>--UPDATE LAST ORDERID TABLE
>UPDATE LAST_ORDERID SET OrderID = @.OrderID
>SET @.ErrCount = @.ErrCount + @.@.Error
>--END UPDATE LAST_ORDERID
>
>Last_orderid has one column / one row, and is pretty useless IMO.
>Past the idea of creating an identity column on Orderid in Orders would
>it be good practice to put this in a begin transaction, commit transaction
>to reduce the lock time?
>TIA
>
>
>
>
>|||"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:n52lm19pmhronpc0iks5vr5pe17l8ar1h6@.
4ax.com...
> Put it in a transaction to assure correct behavior, it won't save any
> time, though.
I only gave 1% of the SP for show. In all actuality an insert into my
Orders table follows this:
Lock NextKey#Table and return it's current value +1
Update NextKey#Table
Pull secondary business data for use later.
Insert into Orders Table using NextKey# from above.
Insert into OtherBizTable with NextKey and that other BizData
Insert into OrderDetails
Check ErrorStatus
if so Roll back and send email to people who care
otherwise commit
Now lock will disengage? < I think that I have locked ALL tables in use
through this SP because of this opening line>
SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
(TABLOCKX))
So if I commit that transaction at the top, I won't lock all the way
through?
TIA
__Stephen|||Are you asking if you should separately commit the couple of lines at
the top of the SP, instead of putting the whole thing in a
transaction?
You are aware that an SP does not automatically comprise a
transaction, you have to explicitly ask for it, so maybe there is no
transaction at all in your current code?
J.
On Fri, 4 Nov 2005 08:04:31 -0600, "__Stephen"
<srussell@.transactiongraphics.com> wrote:
>"jxstern" <jxstern@.nowhere.xyz> wrote in message
> news:n52lm19pmhronpc0iks5vr5pe17l8ar1h6@.
4ax.com...
>I only gave 1% of the SP for show. In all actuality an insert into my
>Orders table follows this:
>Lock NextKey#Table and return it's current value +1
>Update NextKey#Table
>Pull secondary business data for use later.
>Insert into Orders Table using NextKey# from above.
>Insert into OtherBizTable with NextKey and that other BizData
>Insert into OrderDetails
>Check ErrorStatus
>if so Roll back and send email to people who care
>otherwise commit
>Now lock will disengage? < I think that I have locked ALL tables in use
>through this SP because of this opening line>
>SET @.OrderId= (SELECT MAX(Orderid) + 1 AS LastID FROM LAST_ORDERID with
>(TABLOCKX))
>So if I commit that transaction at the top, I won't lock all the way
>through?
>TIA
>__Stephen
>
>