| Author |
 Topic  |
|
|
Csoft
Starting Member
Nepal
8 Posts |
 Posted - 02/23/2012 : 04:17:37
|
Case
1)There are 50+ workstation across the 4 different branches.
2)Tno should be unique across the table.
3)Tno should be numeric, sequential generated,and must be unique on concurrent request of it.
4)NEWID() cannot be used.
Current applying As follows
Create PROCEDURE [dbo].[GetTransno](@TNO_2 NUMERIC OUTPUT)
as
SELECT @tno_2 = isnull(max(tno),0)+1 FROM uttno
update uttno set tno=@tno_2
select @tno_2
The above script sometimes gets lock and takes time on concurrent access.So anyone pls suggest best alternative practice without using table.
Thanks |
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3426 Posts |
Posted - 02/23/2012 : 05:04:46
|
erm. Why not just use an IDENTITY column on the table and join to it rather than what you are doing?
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
Csoft
Starting Member
Nepal
8 Posts |
Posted - 02/23/2012 : 05:18:39
|
Charlie,
Thanks for quick response
i used that procedure so that tno dont get repeat in multiple tables..., IDENTITY will create unique tno for the table only...I need it to be unique across the multiple tables...
|
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3426 Posts |
Posted - 02/23/2012 : 05:54:25
|
if the [transactionNumber] represents the same entity across multiple tables then you should probably model it as a foreign key mapped to a transaction table.
If you were to do this then you could simply have a transaction table with an autonumber primary key, then have foreign keys for all the other tables to that transaction table. then you will be consistent.
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Csoft
Starting Member
Nepal
8 Posts |
Posted - 02/23/2012 : 06:16:55
|
| i dont want to use any table or trxn table with autonumber as PK..., is there any way of generating TNO with combination of datetime or else ? |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3426 Posts |
Posted - 02/23/2012 : 06:30:05
|
nope -- you need some sort of broker because you've got concurrency. You can use GUID's but you've already discounted that. If each machine had something unique about it then you could probably cobble something together by concatenating unique things about the machine but..... seems really horrible.
I don't understand why you refuse to use a table because you *are* using a table right now, you are just doing it manually. Even if you didn't want the referential integrity of the FK's you could at least let the table handle the concurrency/locking for you with autonumber or a SEQUENTIALGUID or something.
Maybe someone else can help you but I doubt that I can.
Good luck.
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 02/23/2012 : 06:32:28
|
CREATE PROCEDURE dbo.GetTransno
(
@TNO_2 NUMERIC OUTPUT
)
AS
SET NOCOUNT ON
DECLARE @Return TABLE
(
Tno NUMERIC
)
MERGE dbo.UtTno AS tgt
USING (
VALUES (1)
) AS src(Delta) ON src.Tno = tgt.Tno
WHEN MATCHED
THEN UPDATE
SET tgt.Tno += src.Delta
WHEN NOT MATCHED BY TARGET
THEN INSERT (
Tno
)
VALUES (
src.Delta
)
OUTPUT inserted.Tno
INTO @Return;
SELECT @TNO_2 = Tno
FROM @Return
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 02/23/2012 : 06:33:39
|
If you are using SQL Server 2012, you can use a SEQUENCE object instead. Much easier.
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3426 Posts |
Posted - 02/23/2012 : 06:40:03
|
quote:
Originally posted by SwePeso
CREATE PROCEDURE dbo.GetTransno
(
@TNO_2 NUMERIC OUTPUT
)
AS
SET NOCOUNT ON
DECLARE @Return TABLE
(
Tno NUMERIC
)
MERGE dbo.UtTno AS tgt
USING (
VALUES (1)
) AS src(Delta) ON src.Tno = tgt.Tno
WHEN MATCHED
THEN UPDATE
SET tgt.Tno += src.Delta
WHEN NOT MATCHED BY TARGET
THEN INSERT (
Tno
)
VALUES (
src.Delta
)
OUTPUT inserted.Tno
INTO @Return;
SELECT @TNO_2 = Tno
FROM @Return
N 56°04'39.26"
E 12°55'05.63"
Hi Peso,
can you talk me through this? I understand what it does but I'd like to know if there are any advantages over just INSERTING into a table with a simple IDENTITY column with a SCOPE_IDENTITY() call afterwards.
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 02/23/2012 : 07:26:30
|
The statement above is wrong. I can see that now. OP's idea is to update the Tno table which has only one row.CREATE PROCEDURE dbo.GetTransno
(
@TNO_2 NUMERIC OUTPUT
)
AS
SET NOCOUNT ON
DECLARE @Return TABLE
(
Tno NUMERIC
)
IF EXISTS(SELECT * FROM dbo.UtTno)
UPDATE dbo.UtTno
SET Tno += 1
OUTPUT inserted.Tno
INTO @Return
ELSE
INSERT dbo.UtTno
(
Tno
)
OUTPUT inserted.Tno
INTO @Return
VALUES (1)
SELECT @TNO_2 = Tno
FROM @Return
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
Csoft
Starting Member
Nepal
8 Posts |
Posted - 02/23/2012 : 10:19:26
|
Hi Peso...
I dont want to use table...
While using table as you guys suggested..., there is problem.., i want to give example why i refuse to use table
Branch 1 running End of Day operation(EOD)...and one procedure accessing recursively {exec GetTransno @TNO_2 OUTPUT} within begin transaction...and assume this procedure takes 1 min. to complete(depends on trxn)...Here UTTno table is Lock for a min...
Now Branch 2 wants tno {exec GetTransno @TNO_2 OUTPUT}, during branch 1 performing EOD, Branch 2 need to wait until branch 1 completes its EOD operation.
So i need script that can generate unique sequential number like @@IDENTITY but its should be unique across multiple tables.
Any suggestion.... |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3426 Posts |
Posted - 02/23/2012 : 10:27:06
|
OK. That's probably *not* what you want to do.
IF you need to get a transaction number then you should do that first and as quickly as possible. Get the transaction number and commit as soon as possible. then store that transaction number in a variable and use it. if you end up rolling back live the fact that you'll get gaps -- at least you want be locking other processes.
OR use a GUID. That's what they are designed for.
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 02/23/2012 : 10:39:12
|
Why do you care if the values are sequential???
Just grab the new value from the stored procedure first, and then do whatever you want in a transaction.
Don't include the stored procedure above to be included in the transaction.
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
Csoft
Starting Member
Nepal
8 Posts |
Posted - 02/23/2012 : 11:17:29
|
| might b mine bad luck...but still i will find out the wayout... |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3426 Posts |
Posted - 02/23/2012 : 11:28:30
|
Good luck. Without a broker or some deterministic way to make the numbers, or without a globally unique source, I think you will be looking for some time.
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 02/23/2012 : 11:31:00
|
Another possibility would be to use tables at the branches with a BIGINT transaction id, make them IDENTITY with very large IDENTITY seed values that are different for each branch, and add a check constraint on the primary key to prevent overlaps.
Example:
Table for Branch 1
create table MyTrans
(
MyTransID bigint identity(100000000000000,1) primary key clustered,
.. remaining columns...
)
alter table MyTrans
add constraint CK_MyTrans__MyTransID_Range
check (MyTransID between 100000000000000 and 199999999999999)
Table for Branch 2
create table MyTrans
(MyTransID bigint identity(200000000000000,1) primary key clustered,
.. remaining columns...
)
alter table MyTrans
add constraint CK_MyTrans__MyTransID_Range
check (MyTransID between 200000000000000 and 299999999999999)
CODO ERGO SUM |
|
|
|
Csoft
Starting Member
Nepal
8 Posts |
Posted - 02/23/2012 : 23:03:57
|
| Thnxs Jones for response, but no use. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 02/24/2012 : 09:29:12
|
quote:
Originally posted by Csoft
Thnxs Jones for response, but no use.
So you want help, but can't even be bothered to even explain why it is no use?
CODO ERGO SUM |
|
|
|
Csoft
Starting Member
Nepal
8 Posts |
Posted - 02/24/2012 : 20:26:36
|
Michael...
i have already stated why your suggestion is no use in above comments...
mine current implementation is as you suggested..but its giving like 1% problems...so i just want to fixed it also.... |
|
|
| |
Topic |
|
How to Generate sequential Unique Numeric Trans No
