I have come into a system that uses a secondary table to generate (for
want of a better word) Identities.
eg
create table myidents
( name sysname not null, ident int not null)
create procedure getnextident @table sysname, @ident int output
as
begin
if not exists (select top 1 1 from myidents where name = @table)
insert into myidents values (@table, 0)
update myidents
set @ident = ident = ident + 1
where name = @table
end
now, (ignoring for now the use of reserved words) the problem is that
this is called frequently, from other procedures. Trouble is that the
calling procedures call it from within a transaction. We now have a
wickedly hot spot on this table, with frequent deadlocks.
Is there any relatively quick fix for this? Some locking hints or
whatever.
Or do we need to go and recode, moving this kind of thing outside the
transaction (which are all rather too long for my liking), and even
cosidering using identity columns as a replacement?
My question: I'm sure I read somewhere that ideally you shouldn't use the auto-increment facility in a table's id field but should generate you're own when you insert. Does this make any sense to anyone or is it just some rubbish I read online somewhere.
Hullo!Doubt it matters too much, but I'll just start off by saying that I'm using C# ASP.NET 2.0 in Visual Studio 2005.I currently have a UID field entitled "ID", and I'd like to make it autonumbering so that users can input stuff into it through a simple web interface and not have to worry about the user's ID.Anyone know how I could go about doing this? I can't seem to find a way through VS2005's GUI, and I can't seem to find anything on the internet about doing it through an SQL statement.Thanks!
Hi All,I am having trouble with a simplet INSERT statement. I want to insert a record. The field "dbid" is the primary key and should be autonumbering. What do I need to add to my code? Dim myconnection As SqlConnection myconnection = New SqlConnection() myconnection.ConnectionString = _ ConfigurationManager.ConnectionStrings("infoNoticeDBConnectionString").ConnectionString
Dim strSQL As String = "INSERT INTO users " & _ "(dbid, infoid) VALUES (@dbid, @infoid)"
Dim dbComm As New SqlCommand(strSQL, myconnection) dbComm.Parameters.Add("infoid", SqlDbType.NVarChar, 50, "@infoid")
Try myconnection.Open() dbComm.ExecuteNonQuery() Catch ex As Exception Response.Write(ex.Message) Response.End() Finally If myconnection.State = ConnectionState.Open Then myconnection.Close() End If End Try
Response.Write("A new record has been added") Response.End()
I am moving from Foxpro to SQL. I am little confused about one aspect dealing with multiple autonumbering columns.
In foxpro it will allow you to set your fields to "autonumber" so you can achieve having 3 or 4 fields for example with unique numbers, depending on the application function.
When I try to do this in SQL I can setup my first identity and PK and it autonumbers just fine, when I try to setup a second autonumber column with a unique I cant seem to get control of the identity to set the starting seed, Unless I select that column in the identity to yes, but it seems to disable the the first identity column I setup.
Can anyone tell what I missed or doing wrong ?
One last note, I am still using the Studio Manager Still learning the programming portion..
I created table with "id" field as "uniqueidentifier" (primary key).I connect via ODBC from MS-Acces.-->>> Don't know how to something like "Autonumber" in MS-Access.Let me know If any trigger or something like this is required.Please post some code of trigger if need.Until now I have only developed some native MS-Accessdatabases. I hope there is some solution about "Autonumbering records"in SQLServer.SqlServer 2000, Access Xp.Marek B±bski
I've been assigned to do the data access layer for an existing SQL database created by someone else. Amazingly enough, the primary table, the one containing all of the records that are central to the db and the whole project, has over 3000 entries and no unique identifer (autonumbered ID) column. The data in all of the other columns is repetitious so none of them can be used as a primary key. I have added a column called TaskAssignmentID, designated as an int data type. The goal is to somehow autofill this column with sequential numbers and then designate that column as the primary key. (don't you just love fixing other people's mistakes?) Any suggestions/examples on how to accomplish this the easiest, most efficient way?
For reference purposes, the table is called tbl_MCHS and the newly created column is called TaskAssignmentID. The data type of the column can be changed, if that would make it easier.
Tools available include Visual Studio 2005 (very familiar) and SQL Server 2005's SQL Server Management Studio (less familiar).
Things I am restricted from doing include blowing away the table and starting over or strangling the previous developer (I asked about both). :-)
Any and all suggestions, steps, or examples will be appreciated.
Our system is reasonably complex with a lot of non-trivial stored procedures. As the load on our DB increased we're now getting more and more deadlocks (10 per day or so from about a million stored proc executions).
We try to avoid transactions where we can, and we do attempt to optimse stored procs to steer clear of deadlock conditions, but with the sheer number of stored procedures we can't possibly avoid all deadlock conditions.
One solution I'm considering is to re-run stored procs that failed because of a deadlock. In the .net code we'll run the stored proc, check for a deadlock error and if one happened, wait 100ms and try again.
we have a production inviremont that is running for about 10 months. Since a couple of weeks we are having problems with "Deadlocks".
This cant be due to an increase in data size on the tables that are having the issues because these are cleaned in the same transaction that populates them.
These tables are used to store temporary data that the production system needs to calculate the correct price for any given order. This transaction takes between 0.5 to 1 second to commit.
We are running on a dual processor machine with 1 Gb of RAM with SQL Server 7 - sp 3, Windows NT 4 sp 6, Microsoft Transaction Server.
In all our queries and stored procedures we use the optimizer hints (nolock) for select statements and (rowlock) for updates or deletes.
Is there any way to totally avoid deadlocks. In some critical applications we have removed transactions entirely, counting on other means to maintain database consistency. We still get deadlocks in this area. These are mainly inserts, and the only thing I can think is that updates to the indexes are causing multiple page locks which result in deadlocks. Is this true?
Will deadlocks be eliminated in 7.0 with row level locking for this situation? Or will index page splits still cause a possibility of deadlock contention?
I have a problem with a SP in 6.5. When i try to run a Stored Proc which is a simple select statement dumped into a temp table in a particular database, I lock other users who are tring to log into other databases some in tempdb database. When i try to kill the process the rollback takes almost 45 mins or so..till then no one can log on to the server.
The SP works fine when no one is logged into the Great Plains server. One more thing i observed is that, the SP when run results on a deadlock only when the owner is a user. If the owner is DBO it works fine.
I've got a deadlock problem. The log below has been generated. The problem is that during one day, I have more than 300 deadlocks like it. Before, the were not so many deadlocks. During past year, the number of users has grow (from 100 before to 500 or 700 now)
*** Deadlock Detected *** - Requested by: SPID 360 ECID 0 Mode "S" - Held by: SPID 113 ECID 0 Mode "S" Index: aaaaa_PK Table: TABLE_1 Database: MYDB == Lock: KEY: 22:325576198:1 (ff009ae5078d) - Requested by: SPID 113 ECID 0 Mode "S" - Held by: SPID 374 ECID 0 Mode "X" Index: aaaaa_PK Table: TABLE_1 Database: MYDB == Lock: KEY: 22:325576198:1 (ff009ae5078d) - Requested by: SPID 374 ECID 0 Mode "IX" - Held by: SPID 360 ECID 0 Mode "S" Table: TABLE_2 Database: MYDB == Lock: PAG: 22:1:2428 == Deadlock Lock participant information: Input Buf: S E L E C T the_rest_of_the_query SPID: 360 ECID: 0 Statement Type: UNKNOWN TOKEN Line #: 1 Input Buf: s p _ e x e c u t e 8 Input Buf: s p _ c u r s o r 8À B 8 8f ç @ Table I Input Buf: S E L E C T the_rest_of_the_query SPID: 360 ECID: 0 Statement Type: SELECT Line #: 1 == Session participant information: == Deadlock Detected at: ==> Process 360 chosen as deadlock victim
I have done : - rebuild indexes on all tables (fillfactor 90) - analysed memory activity
Could a lack of memory be at the origin of the problem ? Which counters in perfmon are significant for memory lack ?
Could the index fill factor could be at the origin of the problem ? At time, it is at 90 percent.
Config : Winnt4 Server, MS-SQL 7 SP4 , 2 GB of RAM , 2 x Xeon 700
I have an application built on top of a questionable DB design which requires overcomplicated selects. The application is experiencing deadlocks regularly, in some cases with only one concurrent user.
I set the trace flag 1204 but am not seeing anything in the Error.log and I initiated a trace in profiler which does not seem to show any deadlock. Despite having recreated the problem which show my browser hanging indefinitely. When I run the following queries:
SELECT spid, waittime, lastwaittype, waitresource FROM master..sysprocesses WHERE waittime > 10000 AND spid > 50
SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)] FROM master..sysprocesses p WHERE open_tran > 0 AND spid > 50 AND datediff (s, last_batch, getdate ()) > 30 ANd EXISTS (SELECT * FROM master..syslockinfo l WHERE req_spid = p.spid AND rsc_type <> 2)
I get:
55860978LCK_M_XPAG: 13:1:2573
54AWAITING COMMANDsleeping sa 11499 55UPDATE sleeping sa 21499
We have a SQL 2005 transaction database server that suddenly started to issue deadlock errors last week on most of the databases on that server and a lot of timeout errors. Before that, that database server performed very well and timeouts were minimal to zero. I am not sure what changed for it to have these performance problems.
The only major change we did was to convert several varchar columns to nvarchar in several tables (as part of internationalization initiatives). We did not modify the procs from varchar to nvarchar though but would be doing that phase by phase.
There is also one proc in which we used the snapshot isolation level of sql server 2005. These are only 2 major changes done within the past 2 weeks. Would these be the cause for these deadlocks and timeouts on our web-based application?
Hi EverybodyI am new to sqlserver 2000.I know basics of locks.but i dont know how toresolve deadlock issues.I am cofusing by reading articles with 90%information and remaining 10% missing.Can any one help me which is the goodsite to learn and resolve deadlocks.Note: I create deadlock. when i try to trace deadlock using dbcc traceon(1205,3604,-1).In error log showing nothing about the deadlock.showing created traceon.........Any help would be appreciated.--Message posted via http://www.sqlmonster.com
We have a problem with a table giving us deadlock issues and we can'tfigure out why.It's a table we write to fairly often perhaps 50 times a minute. Andalso do a select of 200 rows at a time from 4 servers every 5 minutes or so.We are only keeping 48 hours worth of rows in the table which averagesat 30000 a day on a busy day.This table has 1 PK and 2 FKs plus one TEXT column which does notparticipate in the WHERE clause.We are using binded variables.We have applied the latest patch to SQL2003 server running onWindows2003. The patch is supposed to resolve deadlock issues.Anyone have any advice on how to alleviate this problem.Thanks
Morning All, Am getting the following error from a number of users and am sort of wondering where to start in terms of diagnosing the problem. If anyone could give me any pointers on where to start in diagnosing the issue I would be grateful. "System.Data.SqlClient.SqlException: Transaction (Process ID 282) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
I know blocks and Deadlocks are different but how related are they? Seems like when I get reports of deadlocks I always have blocks and the blocks grow as time passes.
My database has been in production for 10 months with no deadlock problems. Three weeks ago I had to restore the database and I am now experiencing several deadlocks. SQL server is suppose to handle this and kill a process to resolve the deadlock but it is not. The deadlock occures on a stored procedure which is a simple select on a table with 187 records. DBCC ran with no problems. Any ideas what might be causing this? Any why is SQL not handling? Your help is greatly appreciated!
I need some help in reducing deadlocks in 6.5 I have tested with `Insert Row Locking` turned off and it reduced the number of deadlocks. What i need to know is if removing the foreign key relationships on tables reduces/eliminates Deadlocks. If any of you have any info on this please let me know.
i have an issue regarding the following error message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID ##) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
this is caused on a couple of ASP pages, which are using:
This is happening fairly frequently, which can't be a good thing, so would it be worth changing the lock or cursor types to a different one, in particular, adLockPessismistic?
Any pointers or comments would be very welcome.
This is happening on a windows server os, sql server 2000 using classic asp (.asp) pages, connecting to sql server using ado within the asp pages.
I am conducting stress testing for my website and keep getting deadlocks with the following message when one process is adding about 100 records per second and another process is trying to access the data:
Transaction (Process ID 499) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
What do I need to do in my stored procedures to avoid this? I only have ONE stored prcoedure that locks a row while incrementing an ID value. I am not doing any other locks, so is this a SQL Server system lock?
I have a search function, which searches across many tables.
It's a pretty heavy SPROC, I'm wondering in general, what are the best way to reduce deadlocks ? Its used a fair bit, and altho I haven't noticed problems with it myself, there are definately a decent amount of deadlocks showing up in the logfiles.
I've always assumed this is something really difficult, and avoided it like the plague.
I'm going thru my application log, and just seeing what errors are popping up. I have a relatively intense search feature, thats causing alot of deadlocks.
Exception type: SqlException Exception message: Transaction (Process ID 105) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
In general, what's the best way to resolve this ?
Should I see if I can apply "WITH (NOLOCK)" to my data ?
Problems in a 2008 R2 environment, database grows quickly and deadlocks occure somtimes.When I checked the database the tables lacked completely of indexes.How can I find out which indexes that should be created and how to manage the deadlocks?
I face alot of DeadLocks in my SQL Server. the server is SQL 2000 SP3 with Windows 2000 sp4. I have all kinds of locks and i really don't understand none of them. I tried to restart the server and it didn't help - the locks id and locks objects are still there - about 15 objects. what to do?
I'm trying to diagnose deadlocks in SQL Profiler. The deadlocks weregenerated by Loadrunner scripts (stress testing) simulating applicationSQL via an ODBC DSN connection.2 things are puzzling me in the SQL Profiler traces that I have logged1) There are a large number of Lock:Timeout events but the 'locktimeout' setting is the default 'wait forever' so I dont know what istiming out.2)When say 2 distinct SPIDs are in a Deadlock Chain, they are using thesame ClientProcessId at the time of deadlock. What is theClientProcessId and is it relevant to the deadlock?Thank you in advance for any replies.
I am getting lot of deadlocks in my application. As it is very complexti avoid deadlocks at this stage of application we have done few stepsto lessen the impact.We have added retries after deadlock is capturted.We have added select * from TABLE with (nolock) wherever possible.But interestingly second step is not working. I have few simple selectstatements where i am using nolock criteria still I am gettingdeadlock victim error. Any idead why it happening. I thought as soonas I put nolock in the query it will ignore all the locks.My sp isCREATE procedure sp_Check_denomination@supply_till_idint,@product_codechar(4),@iso_currency_codechar(3),@denominationmoneyasdeclare @product_id numeric(5)select @product_id = product_id from product with (nolock) whereproduct_code = @product_codeif exists (select *from transaction_inventory TI with (nolock),product_ccy_denom PCD with (nolock)where TI.supply_till_id = @supply_till_idand TI.product_id = @product_idand TI.iso_currency_code= @iso_currency_codeand TI.denomination = @denominationand TI.product_id = PCD.product_idand TI.iso_currency_code = PCD.iso_currency_codeand TI.denomination = PCD.denominationand PCD.product_id=@product_idand PCD.denomination = @denominationand PCD.iso_currency_code=@iso_currency_codeand PCD.tradeable = 1)beginreturn(1)endelsebeginreturn(0)endGO
Hi All,I have read about deadlocks here on Google and I was surprised to readthat an update and a select on the same table could get into adeadlock because of the table's index. The update and the selectaccess the index in opposite orders, thereby causing the deadlock.This sounds to me as a bug in SQL Server!My question is: Could you avoid this by reading the table with a'select * from X(updlock)' before updating it? I mean: Would thisresult in the update transaction setting a lock on the index rowsbefore accessing the data rows?Merry Christmas!/Fredrik Möller