Aller au contenu | Aller au menu | Aller à la recherche


Deadlock MySQL

Par PlaceOweb le mercredi, décembre 25 2013, 19:49 - SQL - Lien permanent

Exception : Deadlock found when trying to get lock; try restarting transaction com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

Exception::getMessage: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

SHOW INNODB STATUS; -- MySQL 5.1 : 'SHOW INNODB STATUS' is deprecated and will be removed in a future release. Please use 'SHOW ENGINE INNODB STATUS' instead
SHOW ENGINE INNODB STATUS;
 
-- Consultez la section : 
------------------------
LATEST DETECTED DEADLOCK
------------------------

La syntaxe de SHOW INNODB STATUS est un synonyme déprécié (supprimé depuis la version MySQL 5.5) de SHOW ENGINE INNODB STATUS, qui fait partie des commandes de consultation du status des moteurs de la base de données SHOW ENGINE.

LATEST DETECTED DEADLOCK

This section provides information about the most recent deadlock. It is not present if no deadlock has occurred. The contents show which transactions are involved, the statement each was attempting to execute, the locks they have and need, and which transaction InnoDB decided to roll back to break the deadlock. The lock modes reported in this section are explained in “InnoDB Lock Modes”.

Relatif au deadlock et au locking : InnoDB Concepts and Architecture

lock type compatibility matrix.

 	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

Syntaxe des commandes SQL : Commandes relatives aux verrous et aux transactions

If you use READ COMMITTED or enable innodb_locks_unsafe_for_binlog, you must use row-based binary logging.

Modèle de transactions et verrouillage InnoDB

Le moteur de tables InnoDB : Modèle de transactions et verrouillage InnoDB : InnoDB et SET ... TRANSACTION ISOLATION LEVEL

Le comportement par défaut de InnoDB est REPEATABLE READ parmis {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}

SHOW VARIABLES LIKE 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
 
| autocommit    | ON    |
 
| innodb_lock_wait_timeout | 50    |
 
+---------------+-----------------+
 
SELECT @@global.tx_isolation,@@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
 
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
SELECT @@global.tx_isolation,@@tx_isolation;
+-----------------------+----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+----------------+
| REPEATABLE-READ       | READ-COMMITTED |
+-----------------------+----------------+
innodb lock wait timeout (applies to InnoDB row locks only)

innodb_lock_wait_timeout

The timeout in seconds an InnoDB transaction waits for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction waits at most this many seconds for write access to the row before issuing the following error:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

InnoDB does detect transaction deadlocks in its own lock table immediately and rolls back one transaction. The lock wait timeout value does not apply to such a wait.

niveau d'isolation de InnoDB :
  • READ UNCOMMITTED : Les commandes SELECT sont non-cohérentes, car ces commandes non-verrouillantes sont effectuées sans rechercher de versions plus récente de la ligne.
  • READ COMMITTED : chaque lecture cohérente, même dans une transaction
  • REPEATABLE READ : niveau d'isolation par défaut d'InnoDB. Les commandes SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE et DELETE qui utilisent un index unique dans une condition de recherche, ne verrouille que la ligne d'index trouvée, et non pas le trou précédent
  • SERIALIZABLE : comme REPEATABLE READ, mais toutes les lectures SELECT sont implicitement converties en SELECT ... LOCK IN SHARE MODE.

Le moteur de tables InnoDB : Modèle de transactions et verrouillage InnoDB : Lecture cohérente non-bloquante

Les lectures cohérentes sont le mode par défaut de traitement des commandes SELECT par InnoDB avec les niveaux d'isolation READ COMMITTED et REPEATABLE READ. Une lecture cohérente ne pose aucun verrou sur les tables auxquelles elle accède, et par conséquent, les autres utilisateurs peuvent librement modifier ces tables en même temps qu'une lecture cohérente est exécutée.

Le moteur de tables InnoDB : Modèle de transactions et verrouillage InnoDB : Verrous de lecture SELECT ... FOR UPDATE et SELECT ... LOCK IN SHARE MODE

Le moteur de tables InnoDB : Modèle de transactions et verrouillage InnoDB : Les verrous posés par différentes requêtes SQL avec InnoDB

Une lecture cohérente n'est pas toujours pratique, dans certaines circonstances. La solution est d'exécuter la commande SELECT en mode verrouillage, avec LOCK IN SHARE MODE.

Dans certains autres cas qui ne conviennent pas, LOCK IN SHARE MODE n'est pas une bonne solution, faite un verrou en mode FOR UPDATE

  • SELECT ... FROM ... : ceci est une lecture cohérente, qui lit un bilan de la base, et ne pose aucun verrou
  • INSERT INTO ... VALUES (...) : pose un verrou exclusif sur la ligne insérée
  • INSERT INTO T SELECT ... FROM S WHERE ... : pose un verrou exclusif sur chaque ligne inséré dans T
  • CREATE TABLE ... SELECT ... effectue une commande SELECT sous la forme d'une lecture cohérente, ou avec des verrous partagés, comme précédemment.
  • REPLACE est similaire à une insertion, si il n'y a pas de collision sur la clé unique. Sinon, une verrou exclusif sur l'index de prochaine clé est posé sur la ligne qui sera modifiée.
  • UPDATE ... SET ... WHERE ... : pose un verrou exclusif sur l'index de prochaine clé, à chaque ligne que la recherche trouvée.
  • DELETE FROM ... WHERE ... : pose un verrou exclusif sur l'index de prochaine clé à chaque ligne que la recherche trouvée.
  • ...

Le moteur de tables InnoDB : Modèle de transactions et verrouillage InnoDB : Gestion des erreurs InnoDB

  • Codes d'erreurs InnoDB
    • 1205 (ER_LOCK_WAIT_TIMEOUT) : Le délai d'expiration du verrou a été dépassé. La transaction a été annulée.
    • 1213 (ER_LOCK_DEADLOCK) : Blocage de transactions. Vous devriez relancer la transaction.
  • Codes d'erreur système

Un deadlock de transaction ou un dépassement de temps dans un attente de verrouillage fait annuler toute la transaction à InnoDB.

lock_mode X locks rec but not gap waiting Record lock
lock mode S locks rec but not gap waiting Record lock
*** (1) TRANSACTION:
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
*** (2) TRANSACTION:
*** (2) HOLDS THE LOCK(S):
lock_mode X locks rec but not gap waiting Record lock
*** WE ROLL BACK TRANSACTION (2)
*** (1) TRANSACTION:
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
*** (2) TRANSACTION:
*** (2) HOLDS THE LOCK(S):
lock mode S locks rec but not gap waiting Record lock
*** WE ROLL BACK TRANSACTION (1)

Autres ressources

JBoss

<datasources>
  <local-tx-datasource>
        <jndi-name>DsScotClient</jndi-name>
    <connection-url>jdbc:mysql://my.server:3306/my_base?zeroDateTimeBehavior=convertToNull</connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
    <metadata>
       <type-mapping>mySQL</type-mapping>
    </metadata>
  </local-tx-datasource>
</datasources>
moi je suis en xa-datasource
le problème c'est le conflit d'ouverture en lock S(hared) et lock X(all)
isolation
  • <no-tx-datasource> - a DataSource that does not take part in JTA transactions using a java.sql.Driver
  • <local-tx-datasource> - a DataSource that does not support two phase commit using a java.sql.Driver
  • <xa-datasource> - a DataSource that does support two phase commit using a javax.sql.XADataSource

<transaction-isolation> - the default transaction isolation of the connection (unspecified means use the default provided by the database):

  • TRANSACTION_READ_UNCOMMITTED
  • TRANSACTION_READ_COMMITTED
  • TRANSACTION_REPEATABLE_READ
  • TRANSACTION_SERIALIZABLE
  • TRANSACTION_NONE

mysql conseil d'être en READ_COMMITTED : Use less locking. If you can afford to permit a SELECT to return data from an old snapshot, do not add the clause FOR UPDATE or LOCK IN SHARE MODE to it. Using the READ COMMITTED isolation level is good here, because each consistent read within the same transaction reads from its own fresh snapshot. You should also set the value of innodb_support_xa to 0, which will reduce the number of disk flushes due to synchronizing on disk data and the binary log.

local-tx-datasource : This element is used to specify the (org.jboss.resource.connectionmanager) LocalTxConnectionManager service configuration. LocalTxConnectionManager implements a ConnectionEventListener that implements XAResource to manage transactions through the transaction manager. To ensure that all work in a local transaction occurs over the same ManagedConnection, it includes a xid to ManagedConnection map. When a Connection is requested or a transaction started with a connection handle in use, it checks to see if a ManagedConnection already exists enrolled in the global transaction and uses it if found. Otherwise, a free ManagedConnection has its LocalTransaction started and is used. The local-tx-datasource child element schema is given in Figure 5.7, “The non-XA DataSource configuration schema”

xa-datasource : This element is used to specify the (org.jboss.resource.connectionmanager) XATxConnectionManager service configuration. XATxConnectionManager implements a ConnectionEventListener that obtains the XAResource to manage transactions through the transaction manager from the adaptor ManagedConnection. To ensure that all work in a local transaction occurs over the same ManagedConnection, it includes a xid to ManagedConnection map. When a Connection is requested or a transaction started with a connection handle in use, it checks to see if a ManagedConnection already exists enrolled in the global transaction and uses it if found. Otherwise, a free ManagedConnection has its LocalTransaction started and is used. The xa-datasource child element schema is given in Figure 5.8, “The XA DataSource configuration schema”.

JBoss Enterprise Data Services : Data Services Reference Guide : Transaction Support

"READ COMMITTED" et "REPEATABLE READ"

Isolation (database systems)

Isolation levels :

  • Serializable
  • Repeatable reads
  • Read committed
  • Read uncommitted

PostgreSQL : Isolation des transactions

En interne, il existe seulement deux niveaux distincts d'isolation, qui correspondent aux niveaux Read Committed et Serializable.

  • Uncommited Read (Lecture de données non validées)
  • Commited Read (Lecture de données validées)
  • Repeatable Read (Lecture répétée)
  • Serializable (Sérialisable)

SQL Server 2012 : SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

Contrôle le verrouillage et le comportement de contrôle de version de ligne des instructions Transact-SQL émises par une connexion à SQL Server.

READ UNCOMMITTED

Spécifie que les instructions peuvent lire des lignes qui ont été modifiées par d'autres transactions, mais pas encore validées.

Il s'agit du niveau d'isolation le moins restrictif.

Dans SQL Server, vous pouvez également limiter les contentions de verrouillage tout en protégeant les transactions de lectures erronées de modifications de données non validées en utilisant le niveau d'isolation READ COMMITTED

READ COMMITTED

Spécifie que les instructions ne peuvent pas lire des données modifiées mais non validées par d'autres transactions. Cela permet d'éviter les lectures incorrectes. Les données peuvent être modifiées par d'autres transactions entre deux instructions au sein de la transaction active, ce qui aboutit à des lectures non renouvelables ou à des données fantômes. Il s'agit de l'option par défaut dans SQL Server.

REPEATABLE READ

Spécifie que les instructions ne peuvent pas lire des données qui ont été modifiées mais pas encore validées par d'autres transactions, et qu'aucune autre transaction ne peut modifier les données lues par la transaction active tant que celle-ci n'est pas terminée.

Des verrous partagés sont placés sur toutes les données lues par chaque instruction de la transaction et maintenus jusqu'à la fin de la transaction. Cela évite que d'autres transactions modifient des lignes qui ont été lues par la transaction active. D'autres transactions peuvent insérer de nouvelles lignes lorsque celles-ci correspondent aux conditions de recherche des instructions émises par la transaction active. Si par la suite la transaction active réexécute l'instruction, elle récupère les nouvelles lignes, ce qui aboutit à des lectures fantômes. Comme les verrous partagés sont maintenus jusqu'à la fin d'une transaction au lieu d'être débloqués à la fin de chaque instruction, l'accès concurrentiel est moindre qu'avec le niveau d'isolation READ COMMITTED par défaut. Utilisez cette option uniquement si c'est nécessaire.

SERIALIZABLE

Spécifie les indications suivantes :

  • Les instructions ne peuvent pas lire des données qui ont été modifiées mais pas encore validées par d'autres transactions.
  • Aucune autre transaction ne peut modifier des données qui ont été lues par la transaction active tant que celle-ci n'est pas terminée.
  • Les autres transactions ne peuvent pas insérer de nouvelles lignes avec des valeurs de clés comprises dans le groupe de clés lues par des instructions de la transaction active, tant que celle-ci n'est pas terminée.

C'est le plus restrictif des niveaux d'isolation, parce qu'il verrouille des groupes de clés entiers et laisse les verrous en place jusqu'à la fin de la transaction.

Differences between READ-COMMITTED and REPEATABLE-READ transaction isolation levels

  • In REPEATABLE READ every lock acquired during a transaction is held for the duration of the transaction.
  • In READ COMMITTED the locks that did not match the scan are released after the STATEMENT completes.

Ajouter un commentaire

Le code HTML est affiché comme du texte et les adresses web sont automatiquement transformées.

Fil des commentaires de ce billet