Re: Strange problem in insert record into the database
From: Wayne Snyder (wsnyder_at_computeredservices.com)
Date: 03/15/04
- Next message: Axel Lanser: "Reduceing the filesize of datafiles"
- Previous message: Wayne Snyder: "Re: Login Event! (or something like that)"
- In reply to: Alan: "Re: Strange problem in insert record into the database"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 15 Mar 2004 06:54:19 -0500
If there is regular blocking , your page will probably time out.
If there is a deadlock, one of the participants will have his batch aborted
and the transaction rolled back... In this case your server side (Stored
proc) error handling will not run at all.... Without running a test I don't
know if scope_identity would return null or ( more likely) the previous row
id you inserted..
-- Wayne Snyder, MCDBA, SQL Server MVP Computer Education Services Corporation (CESC), Charlotte, NC www.computeredservices.com (Please respond only to the newsgroups.) I support the Professional Association of SQL Server (PASS) and it's community of SQL Server professionals. www.sqlpass.org "Alan" <alan_1985@hotmail.com> wrote in message news:uWD1M9$BEHA.3788@TK2MSFTNGP10.phx.gbl... > Thanks SK > > As what you said, there may be error occured in the insertion of record. > > This problem occur often in my database server. I guess it is because some > kind of "deadlock by some other process", I always check "Locks / Process > ID" in the Enterprise Manager and processes are always blocking by some > other process, does these kind of blocking will make error occure if > insertion is preform in the same time? what can I do to make the blocking > does not occur again? > > Alan > > > "Steve Kass" <skass@drew.edu> ??? > news:%23M0TU%23%23BEHA.596@TK2MSFTNGP12.phx.gbl ???... > > Alan, > > > > @@identity will be the last identity value generated, not the last > > identity value successfully inserted into the table. If an identity > > value was generated but for whatever reason the insert it was generated > > for was unsuccessful, because of an error, a constraint violation, a > > rollback in a procedure or trigger, that identity value will not be used > > again, and you will see behavior like you describe. > > > > Do you check @@error between inserting and returning the @@identity > value? > > > > SK > > > > Alan wrote: > > > > >Dear experts, > > > > > >I am suffering from a strange problem. > > > > > >OS: MS Win NT 4.0 > > >SQL: MS SQL 7.0 > > > > > >I am running a web site with over 800,000 page view per day, which > involve > > >thousand of insert and select transactions from the database. > > > > > >The problem is: I use stored procedures to insert records into the > database, > > >in most case I return the record ID from the stored procedure > (@@identity) > > >to the application. > > > > > >In my mind, whenever I can get back the ID, that means the insert is > > >success. But this is not always true. In my server, if I select the > record > > >from the database by that ID, sometimes, it return nothing. And if I > insert > > >another record into the table, that ID will be skipped and the next one > will > > >be return. > > > > > >It seems appear when the server is busy, but not 100% sure > > > > > >Any one had this experience which can share the solution with me. > > > > > >Thank you x 1000000 times > > > > > > > > > > > > > > > >
- Next message: Axel Lanser: "Reduceing the filesize of datafiles"
- Previous message: Wayne Snyder: "Re: Login Event! (or something like that)"
- In reply to: Alan: "Re: Strange problem in insert record into the database"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
