Newsgroup: it.comp.software.database
Titolo: [?]Innodb Lock su righe e blocco query(lungo causa log)

Ciao a tutti,
dopo aver letto 1m di pagine ho scoperto di essere un lamer perchè non ho 
ancora capito se eseguendo una update o una insert di una singola riga in 
una tabella Innodb si ottiene in  automatico il lock sulla singola 
riga....... C'e qualcuno che può darmi una delucidazione?
E' possibile dirgli di non lockare?
Grazie
ps sto cercando di venire a capo di una soluzione imbarazzante, via VBA 
eseguo un tot di query update select e insert into. purtroppo ogni tot mi si 
blocca il VBA dandomi 'Errore non specificato''Runtime-error'-
2147467259
 
(80004005) bloccandosi su una delle query (alquanto Random).Schicciando 
'Run' dopo qualche seconda il programma continua.  Io ho pensato che possa 
essere un problema di lock ma potrebbe benissimo non esserlo.
Sto cercando di smanettare sul file .ini di configurazione ma per ora non ho 
risultati.
Qui sotto posto lo status Innodb ed un'altra tabelle di stato mentre il 
programma è in esecuzione (chiedo scusa per la banda occupata) se qualcuno 
con piu esperienza di me nota qualcosa di strano mi potrebbe dare una 
indicazione?
mysql> SHOW INNODB STATUS \G
*************************** 1. row ***************************
Status:
=====================================
050705 16:39:24 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 20 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 326, signal count 326
Mutex spin waits 257, rounds 4540, OS waits 30
RW-shared spins 267, OS waits 133; RW-excl spins 164, OS waits 163
------------
TRANSACTIONS
------------
Trx id counter 0 488166
Purge done for trx's n:o < 0 488144 undo n:o < 0 0
History list length 23
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 3900
MySQL thread id 4138, query id 36585 localhost 127.0.0.1 root
SHOW INNODB STATUS
---TRANSACTION 0 467447, not started, OS thread id 2116
MySQL thread id 1058, query id 2435 LUXP 192.168.2.3 ODBC
---TRANSACTION 0 467446, not started, OS thread id 3660
MySQL thread id 1056, query id 2433 LUXP 192.168.2.3 ODBC
---TRANSACTION 0 467445, not started, OS thread id 228
MySQL thread id 1054, query id 2431 LUXP 192.168.2.3 ODBC
---TRANSACTION 0 467444, not started, OS thread id 3668
MySQL thread id 1052, query id 2429 LUXP 192.168.2.3 ODBC
---TRANSACTION 0 467426, not started, OS thread id 3100
MySQL thread id 1036, query id 2297 localhost 127.0.0.1 root
---TRANSACTION 0 467421, not started, OS thread id 1160
MySQL thread id 1029, query id 2265 localhost 127.0.0.1 root
---TRANSACTION 0 488164, COMMITTED IN MEMORY, OS thread id 3348 committing
mysql tables in use 1, locked 1
, undo log entries 1
MySQL thread id 18068, query id 36584 LUXP 192.168.2.3 ODBC end
UPDATE TRAIN SET Fk_Tracks_ID = '8' Where ID = '2365'
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 1; buffer pool: 0
111 OS file reads, 18946 OS file writes, 17358 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 17.70 writes/s, 16.35 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 207619, used cells 3, node heap has 1 buffer(s)
11.45 hash searches/s, 0.60 non-hash searches/s
---
LOG
---
Log sequence number 0 54105074
Log flushed up to   0 54104903
Last checkpoint at  0 54102846
1 pending log writes, 0 pending chkp writes
16794 log i/o's done, 15.85 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 59291672; in additional pool allocated 255104
Buffer pool size   3200
Free buffers       3012
Database pages     187
Modified db pages  10
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 101, created 86, written 1928
0.00 reads/s, 0.10 creates/s, 1.65 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread id 736, state: sleeping
Number of rows inserted 12627, updated 4023, deleted 2598, read 3140375
11.85 inserts/s, 3.80 updates/s, 0.00 deletes/s, 5262.34 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
ed anche
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Aborted_clients            | 0       |
| Aborted_connects           | 0       |
| Binlog_cache_disk_use      | 0       |
| Binlog_cache_use           | 0       |
| Bytes_received             | 2791421 |
| Bytes_sent                 | 2257567 |
| Com_admin_commands         | 0       |
| Com_alter_db               | 0       |
| Com_alter_table            | 0       |
| Com_analyze                | 0       |
| Com_backup_table           | 0       |
| Com_begin                  | 0       |
| Com_change_db              | 9       |
| Com_change_master          | 0       |
| Com_check                  | 0       |
| Com_checksum               | 0       |
| Com_commit                 | 0       |
| Com_create_db              | 0       |
| Com_create_function        | 0       |
| Com_create_index           | 0       |
| Com_create_table           | 0       |
| Com_dealloc_sql            | 0       |
| Com_delete                 | 0       |
| Com_delete_multi           | 16      |
| Com_do                     | 0       |
| Com_drop_db                | 0       |
| Com_drop_function          | 0       |
| Com_drop_index             | 0       |
| Com_drop_table             | 0       |
| Com_drop_user              | 0       |
| Com_execute_sql            | 0       |
| Com_flush                  | 0       |
| Com_grant                  | 0       |
| Com_ha_close               | 0       |
| Com_ha_open                | 0       |
| Com_ha_read                | 0       |
| Com_help                   | 0       |
| Com_insert                 | 13572   |
| Com_insert_select          | 0       |
| Com_kill                   | 0       |
| Com_load                   | 0       |
| Com_load_master_data       | 0       |
| Com_load_master_table      | 0       |
| Com_lock_tables            | 0       |
| Com_optimize               | 0       |
| Com_preload_keys           | 0       |
| Com_prepare_sql            | 0       |
| Com_purge                  | 0       |
| Com_purge_before_date      | 0       |
| Com_rename_table           | 0       |
| Com_repair                 | 0       |
| Com_replace                | 0       |
| Com_replace_select         | 0       |
| Com_reset                  | 0       |
| Com_restore_table          | 0       |
| Com_revoke                 | 0       |
| Com_revoke_all             | 0       |
| Com_rollback               | 0       |
| Com_savepoint              | 0       |
| Com_select                 | 1581    |
| Com_set_option             | 18      |
| Com_show_binlog_events     | 0       |
| Com_show_binlogs           | 0       |
| Com_show_charsets          | 0       |
| Com_show_collations        | 0       |
| Com_show_column_types      | 0       |
| Com_show_create_db         | 0       |
| Com_show_create_table      | 4       |
| Com_show_databases         | 3       |
| Com_show_errors            | 0       |
| Com_show_fields            | 191     |
| Com_show_grants            | 0       |
| Com_show_innodb_status     | 18      |
| Com_show_keys              | 100     |
| Com_show_logs              | 0       |
| Com_show_master_status     | 0       |
| Com_show_new_master        | 0       |
| Com_show_open_tables       | 0       |
| Com_show_privileges        | 0       |
| Com_show_processlist       | 60      |
| Com_show_slave_hosts       | 0       |
| Com_show_slave_status      | 0       |
| Com_show_status            | 62      |
| Com_show_storage_engines   | 0       |
| Com_show_tables            | 8       |
| Com_show_variables         | 1       |
| Com_show_warnings          | 0       |
| Com_slave_start            | 0       |
| Com_slave_stop             | 0       |
| Com_truncate               | 0       |
| Com_unlock_tables          | 0       |
| Com_update                 | 4242    |
| Com_update_multi           | 0       |
| Connections                | 19423   |
| Created_tmp_disk_tables    | 0       |
| Created_tmp_files          | 20      |
| Created_tmp_tables         | 0       |
| Delayed_errors             | 0       |
| Delayed_insert_threads     | 0       |
| Delayed_writes             | 0       |
| Flush_commands             | 1       |
| Handler_commit             | 0       |
| Handler_delete             | 0       |
| Handler_discover           | 0       |
| Handler_read_first         | 7391    |
| Handler_read_key           | 14795   |
| Handler_read_next          | 0       |
| Handler_read_prev          | 0       |
| Handler_read_rnd           | 0       |
| Handler_read_rnd_next      | 3581799 |
| Handler_rollback           | 19402   |
| Handler_update             | 0       |
| Handler_write              | 13572   |
| Key_blocks_not_flushed     | 0       |
| Key_blocks_unused          | 35927   |
| Key_blocks_used            | 0       |
| Key_read_requests          | 0       |
| Key_reads                  | 0       |
| Key_write_requests         | 0       |
| Key_writes                 | 0       |
| Max_used_connections       | 10      |
| Not_flushed_delayed_rows   | 0       |
| Open_files                 | 0       |
| Open_streams               | 0       |
| Open_tables                | 6       |
| Opened_tables              | 71      |
| Qcache_free_blocks         | 0       |
| Qcache_free_memory         | 0       |
| Qcache_hits                | 0       |
| Qcache_inserts             | 0       |
| Qcache_lowmem_prunes       | 0       |
| Qcache_not_cached          | 0       |
| Qcache_queries_in_cache    | 0       |
| Qcache_total_blocks        | 0       |
| Questions                  | 39300   |
| Rpl_status                 | NULL    |
| Select_full_join           | 2       |
| Select_full_range_join     | 0       |
| Select_range               | 0       |
| Select_range_check         | 0       |
| Select_scan                | 3147    |
| Slave_open_temp_tables     | 0       |
| Slave_running              | OFF     |
| Slave_retried_transactions | 0       |
| Slow_launch_threads        | 0       |
| Slow_queries               | 0       |
| Sort_merge_passes          | 0       |
| Sort_range                 | 0       |
| Sort_rows                  | 7708    |
| Sort_scan                  | 5       |
| Table_locks_immediate      | 21003   |
| Table_locks_waited         | 0       |
| Threads_cached             | 0       |
| Threads_connected          | 8       |
| Threads_created            | 19422   |
| Threads_running            | 1       |
| Uptime                     | 5274    |
+----------------------------+---------+

su una delle query (alquanto Random).
guarda questo link ti può essere di aiuto
http://www.egosoft.com/x2/forum/posting.php?mode=topicprint&t=52803&sid=8a0bb17a4f1410c558850fb78c153f76
ReDiCuori
-- 
-- 
L'uomo non sa perchè si innamora, l'uomo viene travolto e basta
---------------------------------------------------------
www.giordanoemilio.altervista.org
---------------------------------------------------------
www.securitygroup.it
Società di servizio che opera nel campo
della sicurezza aziendale
---------------------------------------------------------
www.pascallike.too.it
http://utenti.lycos.it/pascallike
il sito sulla programmazione pascal
sorgenti, tutorial ed utility
------------------------------------------------------------
www.treves-srl.com
Libreria Internazionale Treves
Via Toledo 249/250 Napoli
------------------------------------------------------------
www.alphalibri.com
Vendita ingrosso di libri
Via S.Anna dei Lombardi 10 , Napoli
-------------------------------------------------------

 ReDiCuori
Grazie Mille dell' interessamento e dell' aiuto. Ho trovato finalmente 
perchè mi dava errore.
Era un problema legato ad un file interno al programma che lanciava le 
query..............(o perso circa 3 settimane per capirlo......)
Ciao!!!!!!
P.s. Grazie Ancora ^_^