Explicit Transaction behavior fun

Question

Post reply

Explicit Transaction behavior fun

Deborah Melkin

SSC Veteran

Points: 289
More actions
July 30, 2003 at 1:14 pm

#106348

The way our database is designed is that we have a stored procedure that "polls" the database, selecting records from a table that indicates changes were made to main tables into a temp table and then selects the new information by using seleting the data from the temp table joined to a view. Only one connection runs this procedure and it uses the last value returned to reuse as the parameter so we only get the changes since the last time changes were made.
What is happening is the user (on a seperate connection) runs a process to make changes to the main tables within an Explicit Transaction. (Yes, the process must be in a TRAN and it was pretty efficient according to Profiler.) If it runs between the BEGIN TRAN/COMMIT TRAN of this process, the polling stored procedure does not always return all of the changes. The problem is coming from the SELECT into the temp table. The stranger part of the behavior is that new records added during the TRAN are picked up, but not all of the updated records. We had tried using NOLOCK for the SELECT in the proc at one point to solve a different issue but we ran into all sorts of new problems, so we're a little leary of going back to that. However it's crucial for our app that we get the correct info from this one table, so we're concerned about dirty reads.
So the main question is: What can we do to work around this? Is there something about SQL's locking behavior we're not taking into account? We are moving the polling interval back to see if this helps reduce the changes of contention. This solution is for 7.0 and 2000 and the front end was written in Delphi.
Thanks in advance for your help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

jpipes SSChampion Points: 12230 More actions · Answer 1

1) What isolation level is the transaction set?

2) Are you using SELECT ... INTO or INSERT INTO?

3) Have you done an analysis of the locking scheme that SQL is deploying? To do this, create a deadlock in the executing transaction using the WAITFOR command, then poll SQL's locks using sp_lock. You can identify the executing tran spid using SELECT @@SPID within your executing transaction.

4) Could you explain a little more about why the dirty reads are important to a polling mechanism?

5) Posting any code would be helpful.

Deborah Melkin SSC Veteran Points: 289 More actions · Answer 2

Thanks for replying.

1. We use the default isolation level - Read Committed.

2. I use INSERT INTO. (see below) I've heard about locking problems with SELECT INTO

3. I followed your advice and ran the transaction and stopped it before it committed. I'm not quite sure what I'm looking for other than seeing that most of the locks are X or IX and mostly KEY or RID.

4. The reason the polling mechanism cares about dirty reads is that it uses the result set for action by other parts of the application. For example, we noticed the problem because the data is used to update a main grid in the app and we noticed certain rows that should have been removed weren't. Also, as I said, when we originally tried using the NOLOCK hint for this table before, we opened a host of problems as soon as it was in the dev db - I don't know why we started deadlocking like crazy, but we did.

5. Here's the proc that isn't returning the right values.

CREATE PROCEDURE pr_Changes @last_update datetime --time of last update

AS

BEGIN

SET NOCOUNT ON

CREATE TABLE #changes

(

event_type char not null,

record_identifier varchar not null,

last_update datetime not null,

ext_identifier varchar null,

Update int NOT NULL

)

INSERT INTO #changes

SELECT c.*

FROM updates c

JOIN(/*** ensure that only one record is pulled ***/

SELECTrecord_identifier,

MAX(Last_update) last_update

FROMupdates

WHERElast_update > convert(varchar(30), @last_update, 121)

GROUP BY record_identifier

) lu ON (c.record_identifier = lu.record_identifier AND c.last_update = lu.last_update)

SELECT*

FROM#changes c

LEFT JOIN view ON record_identifier = id

WHERE (event_type IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H') OR id <> '')

ORDER BY c.last_update

DROP TABLE #changes

SET NOCOUNT OFF

END

GO

One of my thoughts is that the SELECT statement starts before it knows of the changes to the updates because of the type of lock that SQL is using. I don't know if the analysis from #3 would be able to tell me that. But there's something about the results that doesn't work with that theory. What else should I be looking for? Is there a solution?

jpipes SSChampion Points: 12230 More actions · Answer 3

There are a couple suggestions that would improve the speed of the procedure you posted; however I don't know how the dirty reads are happening until I see the code for the transaction, not the read, since the transaction code is what is creating the updated records that are (rightfully) not being read by the reading procedure. The problem is that the ADDED records should not be shown if the transaction has been properly structures and has not been committed by the time the read has taken place. Go ahead and post the tran code. Here is a suggestion on how to improve the performance of your read procedure:

1) Don't mix DDL and DML in the same procedure.

2) Don't use SELECT *

3) Don't need the convert on date to a string

4) Alternate way of getting only max updated records



CREATE PROCEDURE pr_Changes @last_update datetime --time of last update 

AS

BEGIN

--

SET NOCOUNT ON 

DECLARE @MaxUpdate DATETIME

--

SELECT @MaxUpdate = MAX(last_update) FROM updates WHERE last_update > @last_update

--

SELECT c.Field1, c.Field2, etc... /* Don't use SELECT * ! */

FROM updates c

LEFT JOIN view v ON c.record_identifier = v.id

WHERE c.last_update = @MaxUpdate

AND (v.EventType BETWEEN 'A' AND 'H' OR v.id > '')

--

SET NOCOUNT OFF 

--

END

GO

HTH, and post the tran code.

Deborah Melkin SSC Veteran Points: 289 More actions · Answer 4

Thanks again for replying.

1. I originally wrote it as a single select. Unfortunately, the view has nolocks so SQL Server would put together the view first and then get information from the audit table, which was changed at the same time the view was put together, and join the data, which was then out of sync. As I explained in my earlier post, this created problems in our app. Using the temp table solved the problem, as much as it pained me to do.

2. The actual procedure doesn't use SELECT *; I just figured it was easier to read SELECT * than to list out all columns. I guess I forgot to add a comment saying that I had cleaned up code for that and other proprietary reasons.

3. I'll try without the convert. Will it be that much of a boost for the problem I'm having though?

4. Unfortunately, your suggestion won't work for us because this will only pull the last row changed and not all of the rows changed since the last time the proc was run. See 1 for the other reason I can't use it.

Here's an outline of the code fired. I realize that the first comments will be about optimization and other design issues that I've been trying to work around for awhile. Not much can be done other than what I'm already doing here due to a multitude of reasons. But the COMMIT TRAN clearly comes after all actions were done. The SELECT from the other procedure started in the middle of tran and ended after the COMMIT TRAN. The code is generated in the front end but it did properly put the transaction together properly.

BEGIN TRAN

INSERT INTO mt(...) VALUE (...)

EXEC proc

DELETE FROM A WHERE ... --triggers fired for changes table