| Author |
Topic |
|
Kyle.Stevens
Starting Member
26 Posts |
 Posted - 2007-10-23 : 15:45:34
|
Our inventory is currently updated without transactions, which is very bad. I am trying to implement transactions in everything that modifies the Inventory table, but doing so is locking out the entire table. The Visual Basic (slightly modified for simplification) code is shown below. It is updating based on the product id and location. There is a unique index on those two columns. It was updating based on the primary key before, but even then the entire table was being locked. I've also tried using WITH(ROWLOCK) to no avail. In my tests, I am only updating one to two rows per transaction, so I wouldn't think it would escalate to a table lock.Any help would be greatly appreciated. I've been working on this all day, and have made no progress.Visual Basic Code: Dim UpdateString As New Nepco.Transaction Try For Index As Integer = 0 To Shipment_Inventory_Datatable.Rows.Count - 1 UpdateString.Add( _ "UPDATE Inventory SET " _ & "Quantity = CONVERT(int,Quantity) - " & Shipment_Inventory_Datatable.Rows(Index).Item(2).ToString & ", " _ & "Available = CONVERT(int,Available) - " & Shipment_Inventory_Datatable.Rows(Index).Item(2).ToString & ", " _ & "Damaged = CONVERT(int,Damaged) - " & Damaged.ToString & ", " _ & "Modified_By = '" & User & "', " _ & "LastActionCode = " & "4" & ", " _ & "LastActionItem = '" & Me.Shipment_ID & "' " _ & "WHERE Product_ID = '" & Shipment_Inventory_Datatable.Rows(Index).Item(0).ToString & "' " _ & "AND Location = '" & Shipment_Inventory_Datatable.Rows(Index).Item(3).ToString & "' ") Next 'Run the full query UpdateString.Commit() Table schema:CREATE TABLE [Inventory] ( [Inventory_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Client_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Product_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Location] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Quantity] [int] NOT NULL , [Available] [int] NOT NULL , [Allocated] [int] NOT NULL , [Damaged] [int] NOT NULL , [Date_Modified] [datetime] NULL CONSTRAINT [DF_Inventory_Date_Modified] DEFAULT (getdate()), [Modified_By] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [LastActionCode] [tinyint] NULL , [LastActionItem] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , CONSTRAINT [PK_Inventory] PRIMARY KEY CLUSTERED ( [Inventory_ID] ) ON [PRIMARY] , CONSTRAINT [IX_Inventory] UNIQUE NONCLUSTERED ( [Product_ID], [Location] ) ON [PRIMARY] , CONSTRAINT [FK_Inventory_Client] FOREIGN KEY ( [Client_ID] ) REFERENCES [Client] ( [Client_ID] ), CONSTRAINT [FK_Inventory_Product] FOREIGN KEY ( [Product_ID] ) REFERENCES [Product] ( [Product_ID] ), CONSTRAINT [CK__Inventory__Alloc__7AC720C5] CHECK ([Allocated] >= 0), CHECK ([Available] >= 0), CHECK ([Damaged] >= 0), CHECK ([Quantity] >= 0)) ON [PRIMARY]GO |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-10-24 : 07:21:27
|
| All transaction stuff, and dB access for that matter, should be shifted into StoredProcedures.Problem with your approx is that if there is a client problem which need interaction at the client computer after a transaction has started but not finished, then you are going to encounter blocking problem forever. You'll also benefit from a better security model and from bettter/faster data access (from caching).You'll have to move it into StoredProcedures of the type begin transupdate tableaupdate tablebinsert into tableccommit transaction |
 |
|
|
Kyle.Stevens
Starting Member
26 Posts |
Posted - 2007-10-24 : 08:47:57
|
| Using stored procedures probably would be a good idea, and maybe I will try to move this to one. But if the entire table is being locked, it will still lead to problems, I imagine. It is one of the main tables in our database, and with 20-30 people accessing it simultaneously, there will be a lot of blocking. |
|
|
|
Kyle.Stevens
Starting Member
26 Posts |
Posted - 2007-10-24 : 09:22:48
|
Below is the output from Enterprise Manager on the locks that are being held. Why does it look like there are two locks, one on the key level, and the other on the table level?Process ID Lock Type Mode Status Owner Index Resource58 KEY X GRANT Xact PK_Inventory (bf01c50f83c1) 58 TAB IX GRANT Xact |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-10-24 : 09:47:32
|
| How big is your table? Sometimes it's easier to lock everything than to lock 1 row.Do you have a test system where you can trace the code + the locking levels? i.e. as you step through the code, you can see where the locking begins, escalates, and then finishes?Are you doing any other activity in the transactions? |
|
|
|
Kyle.Stevens
Starting Member
26 Posts |
Posted - 2007-10-24 : 10:00:34
|
| There are 53484 rows in the table. Everything I am doing is on a test database. No other activity is occurring in the table (or database, for that matter) other than the code I posted above.The locking behavior is very strange. Using Enterprise Manager, if I select everything from the database and then try to update a row it won't let me. Depending on the where clause in the select statement, it sometimes allows me to update the result set, and sometimes it does not allow it. I cannot figure out the methodology behind it. Can you tell from my post at 9:22 what is being locked? |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-10-24 : 10:34:20
|
| Are you building up multiple update statements inside the "begin trans, commit trans" construct?the key is to keep the transaction as small as possible. are the multiple updates related to each other? should you not move the "begin, commit...etc" inside the "For" loop?Rather than struggle to solve a "spot problem" can you step back and decribe in general terms what you are trying to do, and if possible supply table ddl, "sample data and sample matching expected results? There might be a far simpler way of solving your problem if we can see the big picture rather than just a tiny corner of it. |
|
|
|
Kyle.Stevens
Starting Member
26 Posts |
Posted - 2007-10-24 : 10:46:25
|
Yes, the update statements all occur inside a single "begin trans, commit trans" construct. It is very important that all the updates are part of a single transaction, because the inventory is being modified based on a single order being shipped. If a problem occurs, everything needs to fail together.The DDL for the Inventory table is posted in my original post.Below is the original data in the table for the test client.Executed: 10/24/2007 10:42:40 AM******************************************************************************************************SELECT *FROM InventoryWHERE Client_ID='TEST'******************************************************************************************************Inventory_ID Client_ID Product_ID Location Quantity Available Allocated Damaged Date_Modified Modified_By LastActionCode LastActionItem -------------------------------------------------------------------------------------------------------TE10242007-104147-527-7668 TEST TE222007-10263-3541193 TEST 100 100 0 0 10/24/2007 12:00:00 AM KYLES 7 44013 TE10242007-104147-590-2161 TEST TE222007-102651-609128 TEST 100 100 0 0 10/24/2007 12:00:00 AM KYLES 7 44013 ***************************************************************************************************** Below is the expected result after the order in question has been shipped which will remove all the inventory, in this case.Executed: 10/24/2007 10:45:06 AM******************************************************************************************************SELECT *FROM InventoryWHERE Client_ID='TEST'******************************************************************************************************Inventory_ID Client_ID Product_ID Location Quantity Available Allocated Damaged Date_Modified Modified_By LastActionCode LastActionItem -------------------------------------------------------------------------------------------------------TE10242007-104147-527-7668 TEST TE222007-10263-3541193 TEST 0 0 0 0 10/24/2007 12:00:00 AM KYLES 7 44013 TE10242007-104147-590-2161 TEST TE222007-102651-609128 TEST 0 0 0 0 10/24/2007 12:00:00 AM KYLES 7 44013 ****************************************************************************************************** |
|
|
|
Kyle.Stevens
Starting Member
26 Posts |
Posted - 2007-10-24 : 10:49:53
|
| What is happening, is an order has been shipped in our system. So what the code in question is doing is removing the inventory that was shipped out in the order. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-10-24 : 11:13:09
|
| I'd look for others to dive in an comment, but it might be better if you changed strategy and conducted "singleton" updates and tried to handle "rollbacks" using application logic than using "database/system" facilities. By this I mean that all updates would be issued and committed independant of being part of larger set. If an update fails (technically or by virtue of causing a business rule conflict (i.e. stock < 0)) then use your appliction to reverse the updates previously "committed" in that batch.This approach would allow all updates to proceed with no locking . The downside would be having to build extra application code to "keep track of and reverse" a possible incomplete batch - which should only happen in extreme cases. I've done similiar before on another platform. Not vanilla code, but not rocket science either. Sometimes the cost of dealing with a "failure" is less than the cost of preventing it 100% of the time. |
|
|
|
Kyle.Stevens
Starting Member
26 Posts |
Posted - 2007-10-24 : 11:41:49
|
| I don't know, Andrew. Doing rollbacks in the application logic seems to add quite a layer of complexity. Plus, there are other issues to contend with. What if the power goes out? It won't help there. Also, what if the rollback fails, which is entirely plausible?Does anyone know how MySQL fairs in regards to locking during transactions? I think after lunch I may see if this situation works under MySQL. We've been wanting to switch to that anyway for a while.Thanks again for any help. |
|
|
|
Kyle.Stevens
Starting Member
26 Posts |
Posted - 2007-10-24 : 15:07:14
|
| I tried the same thing in MySQL, and it is locking by row. I am going to migrate the entire system to MySQL. This was planned to happen eventually anyway. Good bye, Microsoft. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-24 : 22:46:05
|
| Tried with rowlock hint in your query? |
|
|
|
Kyle.Stevens
Starting Member
26 Posts |
Posted - 2007-10-25 : 08:19:39
|
| Yes, I tried it with a rowlock hint. It didn't take the hint. |
|
|
|
|
Locking Behavior of UPDATE Transaction