Strange deadlock exception...:)
Posted by: Antonel Pazargic
Date: February 20, 2006 04:14AM

This situation appear in java code (in mysql client all is okie)...:(

I create a simple java method (that give a custom increment for selected table) and I receive a strange dead-lock exception... Seems that instead of locking selected row, all table rows are locked.

I run explain on query and unique key is used for retrieving... so why are all rows locked. When I do same operations from mysql client all is okie. Is jdbc driver fault?

Table definition
CREATE TABLE `increments` (
`incrementid` int(3) NOT NULL auto_increment,
`nume_tabela` varchar(30) NOT NULL default '',
`val_increment` int(12) NOT NULL default '0',
PRIMARY KEY (`incrementid`),
UNIQUE KEY `uq1_increments` (`nume_tabela`),
KEY `idx1_increments` (`nume_tabela`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

mysql> explain select * from increments where nume_tabela = 'detalii' for update;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | increments | const | uq1_increments,idx1_increments | uq1_increments | 30 | const | 1 | |

I use this table for customized multitable increments. In java method I do
/*open connection*/
SELECT val_incremens FROM increments WHERE table_name = :table_name FOR UPDATE;
and then
UPDATE increments SET val_increments = :incremented_value WHERE table_name = :table_name
COMMIT;
/*close connection*/

When Connection used is transaction connection (business connection) all is okie, but when I wanna use a diferent connection ('give-the-increment' connection), locktime exceeded occurs. When I try to use Hibernate I can't provide business connection to coresponded multitable customized increment(sequence), it accepts connection only for obtaining increment (normal).

I don't know how can I solve this strange problem.

Options: ReplyQuote


Subject
Written By
Posted
Strange deadlock exception...:)
February 20, 2006 04:14AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.