Locking Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Locking Issue

Author

Topic

hollandstephen
Starting Member

10 Posts

Posted - 2007-05-16 : 17:32:49

Hello.

ISSUE 1
Correct me if I am wrong, but all versions of SQL Server since 7.0 auto-commit everything unless the code is blocked in BEGIN and COMMIT TRANSACTION statements. Auto-commit is still on by default in SQL Server 2005, is it not?

-----
ISSUE 2

I am having a locking problem in SQL Server 2005. I have an older app that was written in C++ and that has been working on SQL Server 6.5, 7.0 and 2000.

We are in the process of a complete re-write, but the new app will not be ready for up to 18 months, so I need to see if I can come up with a way to support the current app on SQL Server 2005.

The issue:
When I update a record via the app, the code first attempts an INSERT. If the INSERT fails (i.e. UNIQUE INDEX disallows the INSERT because the UNIQUE ID already exists), then the app attempts an UPDATE.

In past versions of SQL Server, the failed statement would immediately release its connection and allow the subsequent UPDATE statement to run. In SQL Server 2005, it appears that the failed transaction holds on and LOCKs the row so that the UPDATE cannot proceed.

Any ideas why SQL Server 2005 acts different from SQL Server 2000?

Any ideas how I can stop this locking issue?

Thanks, Steve

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-16 : 17:52:28

ISSUE1 - it depends on your tool. Both SSMS and Query Analyzer implicitly commit by default. If you are using a third party tool or have written your own application, then it depends.

ISSUE2 - we'll need to see the code and error.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

hollandstephen
Starting Member

10 Posts

Posted - 2007-05-16 : 17:59:15

How do I set auto commit to be on by default?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-16 : 18:02:32

There isn't a SQL Server setting for this. It's done on the application side.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

hollandstephen
Starting Member

10 Posts

Posted - 2007-05-16 : 20:31:05

Thanks for the help. I truly appreciate it.

I have a couple of more questions.

After executing an ODBC trace, I found something interesting...

This app uses SQL Server Authentication.
Let's say the SQL Login is "USER1" and USER1 only belongs to PUBLIC.

All of the tables in the database are owned by USER1.

While logged into the database as USER1:

The user attempts an INSERT into TABLE_A without fully qualifying
the object name.

The INSERT fails due to the fact that it would violate a unique
index on TABLE_A.

SQL SERVER 2000 - Not fully qualified
The error that is returned in SQL Server 2000 is:
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'TABLE_A' with unique index 'TABLE_A_U1'.
The statement has been terminated.

The connection used for the INSERT statement is then dropped and a subsequent UPDATE statement is allowed to execute.

SQL SERVER 2005 - Not fully qualified
When the same insert, using the same credentials is attempted in SQL 2005, the error returned is:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.table_a' with unique index 'TABLE_A_U1'.
The statement has been terminated.

The connection used for the INSERT statement is NOT dropped and a subsequent UPDATE statement is NOT allowed to execute because the previous connection does not release the lock.

Note that the returned error shows the object qualified with DBO as the owner of the object instead of USER1.

SQLSERVER 2005 - Fully Qualified
If I run the same insert statement with the object qualified as USER1.TABLE_A the error returned is:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'user1.table_a' with unique index 'TABLE_A_U1'.
The statement has been terminated.

Note that this time, the error message correctly qualified the name with USER1 as the table owner.

QUESTIONS
Is this not a bug in SQL 2005?

Is it possible that the ODBC driver fails to drop the connection it was using for the INSERT, because it is trying to drop a connection to DBO.TABLE_A while the actual connection is to USER1.TABLE_A?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-05-17 : 12:43:27

How are you performing the insert and update? Via a stored procedure? Could you post the code?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

hollandstephen
Starting Member

10 Posts

Posted - 2007-05-17 : 12:57:46

Simple insert statement via an ODBC call. I have also tested via straight insert attempt in Qyery Analyzer to see what error message would be rendered. The error was the same in both cases, dependent of course upon SQL Server version.

Example:

TABLE_A has 4 columns. There is a composite unique index on first 3 columns.

There is already a row in the table that contains the values:

COL_A | COL_B | COL_C | COL_D
1 2 3 5

I attempt the following INSERT

INSERT INTO TABLE_A VALUES '1', '2', '3', '4'

The insert should fail, of course, because of the unique index violation.

The program handles the error by dropping the connection, reconnecting and attempting an update statement:

UPDATE TABLE_A SET
COL_A = '1',
COL_B = '2',
COL_C = '3',
COL_D = '4'
WHERE
COL_A = '1'
AND COL_B = '2'
AND COL_C = '3'

Thanks for the help.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-17 : 12:58:59

Can you change the app so that it tries an update - if it updates no rows then does an insert?
At least then you aren't relying on an error for processing.

There isn't a connection to the table just to the server.
Does it work ok with te fully qualified names? I notice that the state is different in v2005 but the same in both cases.

In v2000 it was the owner, in v2005 that is the schema and there is a separate owner.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hollandstephen
Starting Member

10 Posts

Posted - 2007-05-17 : 13:01:57

Changing the code is not an option at this point. THe program is in rewrite for obvious reasons, but will not be available for 12 - 18 months.

I am just hoping to find a way to support SQL 2005 with the leghacy app.

USER1 is the owner and the schema in this case.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-17 : 13:18:58

You could put an instead of trigger on the table - check the value and do an update instead of an insert if it exists - in that way you will never get this error.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hollandstephen
Starting Member

10 Posts

Posted - 2007-05-17 : 14:44:42

Thanks for the trigger tip. Sounds like a workaround that could work. I'll give it a shot.

hollandstephen
Starting Member

10 Posts

Posted - 2007-05-17 : 21:19:37

Thanks so much for suggesting the INSTEAD OF trigger. It is a viable workaround for this issue.

After repairing the issue in one section, I found that this problem was rampant throughout the program when executing against SQL Server 2005. I created INSTEAD OF triggers in all cases and I am well on the way to supporting this legacy app on SQL 2005.

Thanks to everyone who responded!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources