Ошибка odbc sqlstate 40001 - Не ошибается лишь тот, кто ничего не делает!

Remove From My Forums

Question
I am getting below Error, when i am trying insert data from third party applications to SQL Tables by ODBC Connection

ODBC ERROR: State = 40001 Error = Transaction ( Process ID 235) was deadlocked on lock

I have integrated Third party application to My SQL Server through ODBC Connection. Once We entered Data on Third party application and click submit the entered data will export in to SQL Table. That is working good.

If at a time multiple usrs are working on Third party application the entered data is not exporting and getting below ERROR

ODBC ERROR: State = 40001 Error = Transaction ( Process ID 235) was deadlocked on lock
- Edited by
  
  Monday, April 8, 2013 8:47 PM
- Changed type
  SSISJoostMVP
  Tuesday, April 9, 2013 4:36 AM
  more appropriate type
- Moved by
  SSISJoostMVP
  Wednesday, April 10, 2013 4:03 AM
  Not ssis related

В 1С при выгрузке данных через внешний источник данных набором записей получил ошибку

Ошибка ODBC. SQLSTATE: 40001
Номер ошибки: 1205
Описание: [Microsoft][ODBC SQL Server Driver]Транзакция (идентификатор процесса 60) вызвала взаимоблокировку ресурсов блокировка с другим процессом и стала жертвой взаимоблокировки. Запустите транзакцию повторно.

Несколько дней потратил на поиск решения.

Помогло следующее:

отсортировать выгружаемые данные в порядке ключевых полей, сделать выгрузку не пачками, а отдельными выгрузками в разрезе ключевых полей;
во внешнем источнике данных перенести поля в начало таблицы (аналогично тому как это сделано в 1С с измерениями регистров), упорядочить поля аналогично ключевым полям;
на SQL-сервере в таблице получателе создать уникальный кластерный индекс по ключевым полям в соответствии с их порядком;
убрал транзакции;

Как понимаю проблема в том, что если нет кластерного индекса – идет блокировка большей части таблицы или всей таблицы. Если есть кластерный индекс – идет блокировка записей индекса. И конфликта блокировок не возникает. Большую роль еще играет упорядоченность данных, т.к. не происходит хаотичной блокировки таблицы. Ну и очень важно выгружать данные в каждой отдельной транзакции так чтобы в запросе был только набор уникальных измерений.

Метки: 1Сmssqlблокировкикластерный индекс

Читайте также:

Источник

The reason code 68 already tells you this is due to a lock timeout (deadlock is reason code 2) It could be due to other users running queries at the same time that use the same data you are accessing, or your own multiple updates.

Begin by running db2pd -db locktest -locks show detail from a db2 command line to see where the locks are. You’ll then need to run something like:

select tabschema, tabname, tableid, tbspaceid 
from syscat.tables where tbspaceid = # and tableid = #

filling in the # symbols with the ID number you get from the db2pd command output.

Once you see where the locks are, here are some tips:

◦Deadlock frequency can sometimes be reduced by ensuring that all applications access their common data in the same order – meaning, for example, that they access (and therefore lock) rows in Table A, followed by Table B, followed by Table C, and so on.

taken from: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.trb.doc/doc/t0055074.html

recommended reading: http://www.ibm.com/developerworks/data/library/techarticle/dm-0511bond/index.html

Addendum: if your servlet or another guilty application is using select statements found to be involved in the deadlock, you can try appending with ur to the select statements if accuracy of the newly updated (or inserted) data isn’t important.

Источник

We received a deadlock condition on a SQL server running a proprietary software application. The software vendor said this occurred because of a computer network issue. This application is running on a VMware virtual server. Any suggestions for finding a solution to this issue are welcome. Is it possible for this to occur due to a network issue? The application runs on the server where SQL is installed (the database is not on a separate server).

Error message [Microsoft] [ODBC SQL Server Driver] [SQL Server] Transaction (Process ID 117) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.  Rerun the transaction.  
 
We have not been able to identify the specific transaction.

Open in new window

Microsoft SQL Server

Источник

Hi,

I am trying to upsert a SQLserver table,job design is like

SQL Server Enterprise—> CPY —> SQL Server Enterprise

Process is failing with Error :

ODBC_Connector_51,0: ODBC function «SQLParamData» reported: SQLSTATE = 40001: Native Error Code = 1,205: Msg = [Microsoft][SQL Server Native Client 10.0][SQL Server]Transaction (Process ID ##) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (CC_OdbcDBStatement::executeUpdate, file CC_OdbcDBStatement.cpp, line ###)

I tried to replace SQLServer Enterprise with ODBC connector stage , still no hope.

My target table (table I am trying to update) is Indexed and trying to update on Indexed column only.

If I try to write insert/update statement in SQLserver it works fine(in Management Studio)

I seared in DSXchase and foud the below link and tried to change Arrey to readUncommited , still not working

viewtopic.php?t=140177

Is there any other property I need to change?

Thanks in Advance.

Источник

Question

Читайте также: