Re: Stored procedure

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Karl (fuka@xxxxxxxx) writes:
searching around in the www and trying some codes at last I tried this
statement:
DECLARE @ID_New uniqueidentifier

SET @ID_New = NEWID()

print @ID_New
BEGIN TRANSACTION
INSERT INTO dbo.tbl_VorVerf
(ID_Verf, ID_VerfArt, ID_Kd, DsNr, DsNr_Verbund,
ErfDat,
Verf_Jahr, Verf_Nr)
SELECT @ID_New, 1, '3F53784A-
DACA-4CC1-9192-34636A375FED', '84302', '84301', CONVERT(varchar(10),
GETDATE(), 104), YEAR(GETDATE()), MAX(Verf_Nr)+1
FROM dbo.tbl_VorVerf WITH (XLOCK, ROWLOCK)
WHERE Verf_Jahr = YEAR(GETDATE()) AND DsNr = '84302'

COMMIT TRANSACTION

(I think, for my problem ist doesn´t matter at the moment, if I use
static or dynamic sql). At the moment I think, this could be a
solution: As long as one user execute this procedure no other one can
start it. But you wrote in this thread, that deadlocks could be
created doing it this way - however I cannot see, in which way this
could happen in that moment. Construction is like that: Creating a new
row the user first selects an existing customer or creates a new one
(ID_Kd), after that application starts this procedure to create the
new row; and give some machine-created values back to the application.
There is no other action of the user in this moment.

XLOCK is not always what it seems. UPDLOCK is better.

But rather than being philosophical, test. Use SQL Server MVP
Adam Machanic's SQLQueryStress to simulate a bug crowd of
concurrent users. Get it from
http://www.datamanipulation.net/SQLQueryStress/.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Quantcast