Deadlock On Single Table
We have one user who enters a transaction and then does a single row
update (updates all columns but only one is changing - this is due to
the way our sql is generated in the application), at this point
another user enter a transaction and tries to update the same row (he
understandably has to sit and wait while he is blocked by the original
user). The original user then updates the same row again – at this
point the second user is chosen as a deadlock victim and killed. If I
try and recreate this with any other tables(or pubs) I get my expected
behaviour of the original user just doing 2 successful updates and the
second user then completing his update once the original user has
either committed his changes or rolled back. The query plan indicates
that a drop and insert of the row is happening (this is not the case
with any other tables where we get our expected behaviour). This only
happens when the index is clustered - if we use a non-clustered index
it does not occur.
Is this expected behaviour? it seems dangerous to me as the first
user has not commited or rolled back his updates. It was only
highlighted by a fault in our application that caused the second
update to be executed.
I have some thoughts about it being something to do with a row lock
being relased due to a delete / insest of the row in the second update
(we see this in the execution plan).....
Any help much appreciated as I am struggling to get my head round how
the second user was ever able to get hold of the resource.
View Complete Forum Thread with Replies
Related Forum Messages:
Deadlock Due To Transactions Within A Single SPID(syslockinfo Table)
I got a deadlock scenario and so I took a snap shot of the syslockinfo table. I found out an interesting scenario where the deadlock is because of two transactions within the same spid. rsc_text rsc_bin rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid req_ecid req_ownertype req_transactionID req_transactionUOW 1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 113 0 1 96462284 00000000-0000-0000-0000-000000000000 1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 67108864 113 0 1 96594607 00000000-0000-0000-0000-000000000000 For the first one the lock was granted(transactionID:96462284) but for the next one(transactionId:96594607) it was put in wait state and SQL server detected it as a deadlock. I just wanted some clarifications. 1) what is the significance of req_transactionID column in syslockinfo table? 2) what is the relationship between req_transactionId column and spid column? 3) In case of nested transactions what will be the values of these 2 columns and what will be the relationship between them in that scenario? 4) In this case the deadlock is occurring while executing a SP(the nested level of calls go till 3rd level). 5) Will there be contention for locks between transactions within a single spid. it is something like this: sp_cache sp_cache1 while sp_cache2 end while the deadlock occurred when executing sp_cache 2. At that time there were only two transactionId values in the syslockinfo table for this spid,They were: 1) 96462284 2)96594607 If some body could please help me it would be really helpful for me. Thanks in advance!!
View Replies !
Combine Data In Single Row From Single Table
How can i combine my data in single row ? All data are in a single table sorted as employeeno, date Code: Employee No Date SALARY 1 10/30/2006 500 1 11/30/2006 1000 2 10/25/2006 800 3 10/26/2006 900 4 10/28/2006 1000 4 11/01/2006 8000 Should Appear Code: EmployeeNo Date1 OLDSALARY Date2 NEWSALARY 1 10/30/2006 500 11/30/2006 1000 2 10/25/2006 800 3 10/26/2006 900 4 10/28/2006 1000 11/01/2006 800 PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT. THANKS IN ADVANCE
View Replies !
How To Get Table Info From RID Deadlock ?
Hi There I am trying to troubleshoot deadlock issues. The dead lock is with a RID. Now from various links i have established that you have to use DBCC PAGE() with relevant info to get more information, i get back a object and index id, m_objid. However i dont know how to get the tablename form here, msdn simply says that it is an allocation id not and actual table id, so how can i from here get to table information? All i am trying to figure out is which table is affected fromt he RID dealock, but that does not seem to be a simple process. Thanx
View Replies !
Deadlock On Update Using Temp Table
I sometimes get the following error from an update statement in astored procedure:Transaction (Process ID 62) was deadlocked on thread | communicationbuffer resources with another process and has been chosen as thedeadlock victim. Rerun the transaction.The isolation level is READ UNCOMMITTED and there are no explicittransactions in the stored procedure. The update statement is asfollows:UPDATE PLSET PL.PL_SI_LAST_YEAR_AMOUNT = #tmpWorkPLPrior.PRIOR_AMOUNTFROM #tmpWorkPLPriorWHERE PL.COMPANY = @compAND PL.PLAN_YEAR = @yearAND PL.FORECAST_QUARTER = @qtrAND PL.VERSION_ID = @verAND PL.BUSINESS_UNIT_CODE = #tmpWorkPLPrior.BUSINESS_UNITAND PL.PROJECT_ID = #tmpWorkPLPrior.PROJECT_IDAND PL.BUDGET_CODE = #tmpWorkPLPrior.BUDGET_CODEAND PL.BUSINESS_UNIT_CODE <> 'G7'PL rows: 24,342,553PL rows - Filtered: 230,088#tmpWorkPLPrior rows: 3,641Updated rows: 43,692The temp table (#tmpWorkPLPrior) is created by a SELECT INTO statement.It has the values that need to be set in the PL table. The PL tablehas a clustered index on 8 columns. The filters (@comp, @year, ...)select 230,088 rows. When the update succeeds it updates 43,692 rowsin about 15 seconds. Why does this sometimes deadlock and other timessucceed? There is nothing else running, so the process is deadlockingon itself.Thanks,Frank
View Replies !
Deadlock When Working With Temporary Table?
Hi, We wrote some stored procedures, and some temporary table used because there some some complicated logic. In normal testing, the application works weel, but very easy to get deadlock error in stress testing. Error message like this: Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Sample code: CREATE TABLE #PageIndexForUsers ( IndexId int IDENTITY (0, 1) NOT NULL, UserId int ) INSERT INTO #PageIndexForUsers (UserId) SELECT USER_ID FROM Users Other tested functions will insert/update table Users too. I searched on internet, several people said it's MS bug, anybody help?
View Replies !
Deadlock Scenarios (can Deadlock Happen While...)
Hi, I am adding deadlock handling code by putting while loop block where the loop executes aslong as we get error 1205 (which is now warning). I am just wondering if deadlock can happen for below actions. ALTER DATABASE, when CREATEing/DROPing FILE/FILEGROUP ALTER TABLE, when trying to SWITCH the PARTITION of a TABLE Just thinking it is not worth handling if this won't happen for above cases comments... Thanks -D
View Replies !
Deadlock Problem? 3 Way Conditional Split Of Data From One Table To Another Never Completes
I have a source table which I'm splitting 3 ways based on a column value, but the target is the same OLE DB destination table. One conditional path is to a Multi-Cast two way split to same OLE DB gestination table. The default split is to a flat file for logging unknown record types. For a test I have data for only the 3 column values I want, but I'm having trouble with the process completing. If I pre-filter the data going into the source table by one or two values I can get the process to complete even if one split is to the multicast. If I include all three data types in the source table, I get different results depending on the order in which the conditions are specified - sometimes only two split paths are executed; other times all three are executed, but in some cases only one path of the multicast split is executed. In any case, when the three source data types are used in the test, the process never competes - the pathes are in a yellow condition and never complete. Am I creating some kind of deadlock situation by having the source data directed to the same target table via 4 splits? Any help you can provide is appreciated. Thanks.
View Replies !
Trying To Return A Single Record For Each Client From Child Table Based Upon A Field Of Date Type In Child Table
I have table "Clients" who have associated records in table "Mailings" I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting. I'm trying to return records containing the next upcoming mailing for each client. The closest I can get is below: I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date' SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
View Replies !
BCP A Single Table
I've managed to BCP in a single table form a backup DAT file into a database, but it took WAY to long (1 hour+ on a meaty server) and I can't understand why. The table only had a few rows of data and only had a few small dependancy tables. The table has a primary key, hence an index so the BCP becomes a logged operation but it still should not take this long. Here is the BCP command line that I ran :- bcp <dbname>.dbo.<tablename> in <DAT filename> /U sa /P /S <server> /m 1 /n Could anyone please shed some light on this. Is there anyway of 'fast' bcp'ing this table into the database overwriting the existing one. Failing that, is there any way of scripting the transfer of a table from one server to another. Many thanks. Dave
View Replies !
Intersection On A Single Table
Hi all ! I have a table with no keys (temp table) which looks like this : col1|col2|col3 001|A|.087 001|B|.032 001|C|.345 002|A|.324 002|B|.724 003|A|.088 003|C|.899 001|A|.087 001|A|.234 001|B|.032 As you see, there is some duplicate entries in it. I would like to get a list of all the rows that have the same col1 and col2 BUT different col3 value. The result should return col1=001 col2=A but NOT col1=001 col2=B. I tried a lot of queries with EXISTS, HAVING, etc... but nothing seems to work. Anyone have an idea how I can do it ?
View Replies !
Single Table Restore
Hi! This morning, I needed to restore 1 table to a database because it had accidentally been deleted (don't ask). Each time I tried to restore the single table, I would select TOOLS| DATABASE BACKUP/RESTORE| RESTORE tab from Enterprise Manager, then I select SINGLE TABLE, but am only given the existing table names as choices to restore. This happens when I select to restore 'FROM DEVICE' as well. Am I doing something wrong or is this how MS SQL6.5 is supposed to work? Am I only able to restore a corrupt table and not a missing table? I ended up restoring the entire database... Thanks for your help! Toni Eibner
View Replies !
Single Table Load
I thought I had posted this question already, but didn't see it in the list. I apologize if this is a repost. I am running SQL Server 6.5 SP 4. I am attempting to load a single table from backup, but continually get the error about schemas not matching. Interestingly, it comes back with a status 4, and not the status 3 indicating a mismatch on Ansi_Padding. The statement I am using is load table demhist from internal_tape with file=5,nounload I have tried creating the table from scratch and using select * into... I have tried both above with both settings of Ansi Padding. The table I am trying to load contaings char columns which allow nulls. I have experimented with loading 2 other tables. One of the other tables loads and one doesn't. The one which does not load also has char columns which allow nulls, while the one that does load does not have char columns which allow nulls. Is it a known problem or limitation on the table load that it cannot reload tables that contain char columns which allow nulls?
View Replies !
Single Table Restoration
It appears that in SQL 2000 restoring a single table is a bit more tedious and less user friendly as in earlier versions (6.5). Can anyone explain in a nutshell the easiest/quickest way to restore a single table from a backup? Thanks, Christine
View Replies !
Backup A Single Table
I have a table with about 5000000 Ml records that I need to copy to other database. What will be the easy way to do it? Can I backup a single table? Any suggestions!
View Replies !
Restore Single Table
How can I restore just one table from the full backup? It was very easy with 6.5 but I am not sure whether it's possible with 7.0 and if it is possible, how to do it. Thanks in advance.
View Replies !
Restore A Single Table
Does anyone know of a way to restore a single table using SQL 7.0? I know that I can build a dummy database and do a database restore, then copy the table from database to database. But....was wondering if there is a way to restore a single table from a backup. Is losing the ability to restore a single table one of the 'features' of 7.0?
View Replies !
Two Different Datasets In A Single Table.
Hi, I need to display the datas in a table format. I have placed a table and now i need to use two different datasets for a single table. Those two different datasets are generated based on two different stored procedures. Those stored procedures retrieve data from two different sql tables. Can any one explain me how to use two different datasets for a single table. As far i have tried i was able to fetch only the first record of the other dataset. But i need to fetch all the records of both the dataset. Thanks in advance for any help. Thanks, Sangeethaa
View Replies !
Restore A Single Table
Hi, I have a problem. For some reason I needed to restore a single (large) table that shares a filegroup with other tables. I have a full backup of the database. How can I restore my particular table? -- Srinivas
View Replies !
How To Check If A Single Value Exists In A Table
What’s the easiest way to check if a single value exists in a table column? I’m building a simple login page, and I want to get the username and check if it exists in my Users table. Here’s my SQL: SELECT UserID FROM UsersWHERE UserID = "admin" Could someone give me code to check for “admin” in my UserID column? Here’s some code I tried, using my SqlDataSource, but it returns an error “Could not load type 'System.Data.OleDb'” protected void Button1_Click(object sender, EventArgs e) { // Retreive the results from the SqlDataSource as a DataReader OleDbDataReader reader = (OleDbDataReader) SqlDataSource1.Select(DataSourceSelectArguments.Empty); // Read in the value if (reader.Read()) { } // Close the reader reader.Close(); } I don’t have to use the SqlDataSource, but originally thought it might be easier. I know how to use SqlDataSource to fill a whole GridView but this is different.
View Replies !
Select 1 Row From Each Group In A Single Table
Hello everyone, I've got a bit of an SQL mystery that I'm not sure how to solve. For some reason I just cant get my head around it. Here's the scenario: Table A: _____________ BidID - Int identity AuctionID - int BiderName - varchar(50) bidAmount - money ______________________ Now obviously each Bid will have a Unique ID using BidID but the other rows will contain multiple bids per user, on many different items possibly. BidID AuctionID BiderName BidAmount 1 4005 joeblow 100.00 2 4005 janedoe 101.00 3 4005 joeblow 107.00 4 4006 joeblow 100.00 5 4006 janedoe 105.00 6 4006 joeblow 106.00 I need to find out which Auctions JoeBlow is bidding on, but I dont need a table with Rows for every single one of his bids, just a distinct auctionID for his top bid so in this case the only thing returned would be 3 4005 joeblow 107.00 6 4006 joeblow 106.00 Any clues? I've been through sub querys, and stored procedures, and I cant get anything to work quite right. Thanks in advance for your help.
View Replies !
Will A Single Row Be Locked Or The Entire Table?
I am giving the following statement outside the begin tran block in a stored procedure: select * from table1 with (holdlock) where column1 = @colValue I have the following two questions regarding above situation: Being outside the begin tran block, will the select statement cause a lock to be held or it has to be within the begin tran block for the lock to be held? Will the select statement hold a lock only on the row or rows matching the where clause, or it may lock a page or even the entire table in this situation? Thanks
View Replies !
Grant Access To A Single Table
So here's the situation: I'm creating a front-end application to an SQL Server 2000 database and I need to create a new login to the database for the app. Is it possible (or even; is it a good idea...) to create a logon that only has access to a single table? Actually, I know it's possible - but I don't want to have to go through the hundreds of tables and deny access to them all! So I guess the question is; Is there a quick and easy way of granting a login permissions to a single table only?
View Replies !
SIngle Table Backup/Restore
Hi guys, How to make single table backup / or Restore individual table from the Database backup file.SQL server 6.5 got an option to do this...Anyone know in SQL 7.0 or 2000 I appreciate ur immediate reply! Thanks Rajeev
View Replies !
Single Table BACKUP/RESTORE
Gentlemen: SQL 6.5 used to support single table backup and single table restore from a full backup. I find no such feature in SQL 7.0. Is it definitely no more supported? Best regards and ciao, Andrea
View Replies !
Slow SELECT On Single Table
SELECT * on a 4000 row table is taking more than 12 seconds. Other larger tables are not nearly as slow. I've DBCC dbreindex'd, and dbcc showcontig shows density at 100%. How can I figure out why this is happening? What are some remedies? Thanks for your help.
View Replies !
Can I BCP A Single Colume Into An Exsisting Table?
I need to update one column in a very large table. Can I bcp, bulk copy, bulk insert... one column from another table into an existing table? For example: table RES column STATUS needs to be updated with the same column from another table (PROP). I have looked at bcp but it looks like it wants to insert/update all columns in the row. I just need the one column updated and I need it done fast. Any help would be appreciated. Charlie.
View Replies !
4 Lookups Against Single Large Table
I need to do a 4 column lookup against a large table (1 Million rows) that contains 4 different record types. The first lookup will match on colums A, B, C, and D. If no match is found, I try again with colums A, B, C, and '99' in column D. If no match, try again with column A, B, D, and '99' in Column C. Finally, if no match in any of the above, use column A, '99' in B, '99' in C, '99' in D. I will retreive 2 columns from the lookup table. My thought is that breaking this sequence out into 4 different tables/ lookups would be most efficient. The other option would be to write a script that handled this logic in a single transform with an in-memory table. My concern is that the size of the table would be too large to load into memory. Any ideas/suggestions would be appreciated.
View Replies !
Mutiple Table Single Task
I have about 100 different tables that I would like to bring on nightly basis via integration services. SSIS will process the data and send on to its warehouse destination. Is it possible to use a single task to bring in all these tables from within SSIS? and can write failed records to flat files at the sametime? instead of defining a data flow for each table and dealing with hundreds of dataflows just have one task that loop through the list of tables. Wonder how warehouses fed by 100s of tables deal with this kind of scenario?
View Replies !
Splitting Single Row Of Table In To Two Rows
Hi All, I was wondering is there any way by which i can split a single row in my table into two rows in my table in design of my report. In my table there are 16 rows i cant design table with 16 rows as it will increase size of my report body & it will give problem while exporting. Instead what i want to do is , design a table with two header rows first header with first 8 columns & second with remaining 8 columns. The table should look like this //column 1 2 ID NAME AGE SEX ----------------------------------------------------------------------------------- 2 abc 10 M ----------------------------------------------------------------- 3 def 20 M Is it possible Thanks -- Darshan
View Replies !
Date Difference From A Single Table
Hi, I have a table VisitLog pkey customer_id pkey user_id visit_date visit_note Given both id's, I need a report that will list the visit interval. Assuming the rows are entered in ordered. the result should just display a single column with rows filled with date difference. Date Format in example below is dd/MM/yyyy 1, 1, '01/01/07', null 1, 1, '03/01/07', null 1, 1, '08/01/07', null The result should list difference in days. 2 5 Is it possible to do this in a SELECT Statement? Thanks, Max
View Replies !
Convert Single Record To Table
Hi all! I have imported a table into SQL Server from a legacy program. Each record has a repeating sequence of similar fields. (Ex. Accnt1, Assesed1, Paid1, Accnt2, Assesed2, Paid2, etc.) I would like to take a single record and put data from these fields into a table that has the columns Accnt, Assesed, and Paid. I am doing this for easier use in a program I am developing in VB 2005. Can this be done in SQL or do I need to have help from some VB code? If it's possible, what might the SQL look like? Thanks.
View Replies !
Exporting A Single Table From A Database
Hello, Let me frist start saying that I am no SQL DB guru or have any great knowledge. I am sure this questions is the most basic question posted here. I would like to know how to export a single table from an SQL 2005 DB. I need to export this table from one SQL server to another. Just one table and its contents. Also, I woud like this exported table to be saved as a *.dat file? Thanks in advanced.
View Replies !
How To Copy Single Table From Database
Hello all, I created one Database in my server,n i wanted the same database in my local computer.So i copied it and restored in my database. Now i created another one new table in my server. I want to copy that table into my computer. How can i copy a single table in database and how can i restore(paste) that into my existing databse of my local compuer. Can u please help me to resove this problem. Thanks. rita
View Replies !
Get Child Value From A Single Table Structure
Hi I have a single table which holds a hierarchical a structure. I would like to get all the child ID's from one Stored Proc call. For example I give it an ID in the structure and I would like to give me all the child records that exist to the lowest level for each category found My table structure is [SvcID] [varchar](30) NOT NULL, [Ser_SvcID] [varchar](30) NULL, [SvcDesc] [varchar](100) NOT NULL I know where the root of the hierarchy is because Ser_SvcID will be null. Im not sure how to do this in one stored procedure call Hope the above makes sense Many thanks in advance
View Replies !
Which Is Best? Select From Joins Or Single Table
Hi, I am using a SP which one has lot of Joins(More than 10 tables). For every request from user SP executed, and the DB performance got slow. For this i planned to write a schedule which runs once in an hour to put all the results of SP in to One single table. I thought if the user selects from single table means the performance will increase. I don't know whether this one is a right solution. I am very new to SQL server. Help me for this Problem. Thanks in advance.
View Replies !
SQL Single Query For Multiple Table
Hi friends, I have three table named as Eventsmgmt,blogmgmt,forummgmt.. Each table contain the common column named as CreatedDateTime.. I want to get the most recent CreationDateTime from these three table in single query.. Plzz help me its urgent Thanks
View Replies !
Help On Mutli Access To A Single Same Table
hi everyone, is that possible to access a table if the table is doing an insert command? im trying to insert data into a data while another function calling select statement on that same table. will the table be lock when its doing an insertion? or its free? sorry about the question structure. cheers, delete469
View Replies !
Single Table Query &<blush&>
I usually write (very simple) queries and export to Excel, then work my magic there. But there's got to be a better way. sampletable CUSTNO PARTNO ABC001 A1 ABC002 A1 ABC002 A2 ABC003 A2 I need to find all CUSTNO that have both PARTNO A1 and A2. select * from sampletable where PARTNO in('A1','A2') returns 2 rows. I really only want a list of unique CUSTNO ... "Only 1 customer has both partno A1 & A2". Thank you, --mark
View Replies !
Which Is Best? Select From Joins Or Single Table
Hi, I am using a SP which one has lot of Joins(More than 10 tables). For every request from user SP executed, and the DB performance got slow. For this i planned to write a schedule which runs once in an hour to put all the results of SP in to One single table. I thought if the user selects from single table means the performance will increase. I don't know whether this one is a right solution. I am very new to SQL server. Help me for this Problem. Thanks in advance.
View Replies !
|
TRACKER
