TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED: INSERT INTO cache_menu (cid, data) VALUES ('test:two', '')
However if you do need to make sure that a stored procedure is only called once then you can use a sql mutex lock using spgetapplock.
#NET NO EXCEPTION THROWN FOR DEADLOCK SQL UPDATE#
MySQL thread id 391, OS thread handle 0xa77ffb40, query id 2076 localhost root update sql server deadlocks are normal & to be expected in this type of scenario - MS's recommendation is that these should be handled on the application side rather than the db side. LOCK WAIT 3 lock struct(s), heap size 320, 2 row lock(s) TRANSACTION 1FDBC05, ACTIVE 111 sec inserting Purge done for trx's n:o < 1FDBC04 undo n:o < 0 You'll notice that this query is blocked waiting From LOG: TRANSACTIONS INSERT INTO cache_menu (cid, data) VALUES('test:two', '') RECORD LOCKS space id 0 page no 19215 n bits 88 index `PRIMARY` of table `XXX`.`cache_menu` trx id 1FDBC04 lock_mode XĮg On T2. TABLE LOCK table `XXX`.`cache_menu` trx id 1FDBC04 lock mode IX MySQL thread id 9, OS thread handle 0xb6f49b40, query id 2071 localhost root Record lock, heap no 1 PHYSICAL RECORD: n_fields 1 compact format info bits 0Ġ: len 7072656d756d asc supremum RECORD LOCKS space id 0 page no 19215 n bits 88 index `PRIMARY` of table `XXX`.`cache_menu` trx id 1FDBC05 lock_mode X TABLE LOCK table `XXX`.`cache_menu` trx id 1FDBC05 lock mode IX MySQL thread id 391, OS thread handle 0xa77ffb40, query id 2074 localhost root TRANSACTION 1FDBC05, ACTIVE 3 secĢ lock struct(s), heap size 320, 1 row lock(s) Next, execute on T2: SELECT 1 AS expression FROM cache_menu cache_menu WHERE ( (cid = 'test:two') ) FOR UPDATE Įxecute show ENGINE innodb status on the LOG, note supremum lock. On T1 execute: SELECT 1 AS expression FROM cache_menu cache_menu WHERE ( (cid = 'test:one' ) ) FOR UPDATE On T1 and T2 execute: set autocommit=0 begin On LOG1 execute: CREATE TABLE innodb_lock_monitor(a int) ENGINE=INNODB Make sure no other processes use the database (server). To reproduce, start three mysql sessions and point them to your database (LOG1, T1, T2). RECORD LOCKS space id 0 page no 19209 n bits 112 index `PRIMARY` of table `XXX`.`cache_menu` trx id 1FDB807 lock_mode X locks gap before recĭepending on what's in your table, and what you are trying to insert, the gap lock can also hold the supremum pseudorecord. FOR UPDATE with a cid that's not in cache_menu: TABLE LOCK table `XXX`.`cache_menu` trx id 1FDB807 lock mode IX RECORD LOCKS space id 0 page no 19215 n bits 88 index `PRIMARY` of table `XXX`.`cache_menu` trx id 1FDB808 lock_mode X locks rec but not gap FOR UPDATE with an existing cid results in: TABLE LOCK table `XXX`.`cache_menu` trx id 1FDB808 lock mode IX Create via CREATE TABLE innodb_lock_monitor(a int) ENGINE=INNODB )) (Output below from SHOW ENGINE INNODB STATUS requires the the table 'innodb_lock_monitor' in the db. The key to this problem appears to be INNODB creating a gap lock on the index when a key is NOT in the index on a SELECT. In apachesolr_rebuild_index_table() (line 500 of /var/Drush command terminated abnormally due to an unrecoverable error. WHERE (type = :db_condition_placeholder_0) )) Array Once, even a fatal error occurred with about the same message (sadly, I didn't save it and now can't reproduce), which for the first time mentioned the cause: It seems, an INSERT INTO
#NET NO EXCEPTION THROWN FOR DEADLOCK SQL CODE#
The error isn't written in a normal drupal_set_message()-type message box but seemingly simply output with echo, appearing either before any content or suddenly somewhere in the header of the HTML code – but always displayed above the actual site. _log.I receive the same error frequently on node pages, when I try to open several nodes at once (with Javascript, so really simultaneously, not one after another). Protected static void Execute(string connectionString, string commandString) TimeSpan delay = TimeSpan.FromSeconds(5) Could I add here any code to handle it?Ĭan I do this handling inside a catch block?īuilding on response, I present a general purpose retry wrapper method: private static T Retry(Func func)
If (Connection.State = ConnectionState.Closed) If (DatabaseType != enumDatabaseType.SQLCOMPACT)Ĭommand.CommandTimeout = Connection.ConnectionTimeout If(DatabaseType != enumDatabaseType.ORACLE)Ĭommand.CommandText ="BEGIN " + query + " END " Here is the code for a method we use: public int ExecuteQuery(string query) We want the deadlock victim to retry perhaps after some time delay, but I don't know if it is possible. Part of our team is working on the sql code and transactions to minimize these events, but I'm thinking about making this Database class robust against deadlock situations. We are experiencing serious problems with Deadlock situations in SQL Server 2000. For instance, when I need to execute a procedure, I call Database.ExecuteProcedure(procedureName, parametersAndItsValues). I have a class 'Database' that works as a wrapper for ADO.net.
0 kommentar(er)
