5

Problem in nutshell

  • executed data updates on couple of tables using dbeaver
  • later reads from these tables started failing
  • looks like the rows/pages in these tables are locked by the session where updates are done
  • cant seem to get the locks released

The details

  • connected to an informix db using dbeaver
  • updated a column in one row of a table
  • added a few rows to another table
  • ran selects to verify the updates
  • ran a .net app that depends on these tables, and when it attempted to read the data from this table, it started throwing an error

  • opened another session in dbeaver, ran a select to find the rows i updated, now i get same error

    • for the table with updates... SQL Error [IX000]: Could not do a physical-order read to fetch next row. Could not do a physical-order read to fetch next row. java.sql.SQLException: ISAM error: record is locked.
    • for the table with inserts... SQL Error [IX000]: Could not position within a file via an index. Could not position within a file via an index. java.sql.SQLException: ISAM error: key value locked

  • dug in a bit about the errors, understood that it could be due to tables/pages/rows getting locked

  • pulled up the current locks on the table using the table sysmaster:syslocks
  • confirmed that there are exclusive page locks (may be exclusive row locks, not sure) on the tables that are updated
  • confirmed that there are exclusive intent table locks on the tables that are updated

  • confirmed that the above two are indeed initiated by my dbeaver session using the sysmaster:syssessions table

  • tried restarting dbeaver, tried restarting pc, but none seem to release the exclusive locks

  • my small bit of reading suggests that these should be released automatically, but it doesnt seem like it

How can I get these locks released? any help or direction is appreciated :)

Schu
  • 1,124
  • 3
  • 11
  • 23
  • 1
    The locks should be released when the transaction commits (or rolls back). If the update transaction is not formally committed, then the update locks will be retained indefinitely. Those would cause problems. Do you have a MODE ANSI database? Is your database logged? I'm guessing it is just logged and not MODE ANSI. If so, the update operation has to explicitly start a transaction; otherwise, each statement operates implicitly as a transaction (with BEGIN and COMMIT, effectively). In MODE ANSI, the update operation starts a transaction automatically, but an explicit COMMIT is required. – Jonathan Leffler Sep 27 '18 at 00:32
  • @JonathanLeffler - Thanks for looking into this. i have not explicitly started a transaction in dbeaver, but it sure seemed like an automatic commit, since i was indeed able to see the updated data in another client as well (.net app via odbc).... it does seem like logging is enabled (is_logging = 1) and is not mode ansi (is_ansi = 0) from sysdatabases. if the old transaction is somehow still open, how can I get the old session back, so that i can try to commit or rollback? I no longer have that dbeaver session open. – Schu Sep 27 '18 at 14:16
  • If the dbeaver session terminated, the session is gone (possibly give or take the time taken to spot that the client didn’t cleanly close the connection) and any transaction (implicit or explicit) is complete. If there was an explicit transaction in flight, it will be rolled back; explicit transactions are only committed on COMMIT. – Jonathan Leffler Sep 27 '18 at 14:25
  • @JonathanLeffler - thanks again, its already been about more than 1 and a half day since the session that performed the updates is terminated. do you have any insight into the time the db server could wait to recognize a lost connection? can i do anything as a client to help towards that clean up? – Schu Sep 27 '18 at 15:03
  • Did dbeaver crash, or does it disconnect? If it disconnected, there's no delay. If it crashed, I'd expect it to spot the absence fairly quickly — seconds or minutes, and not hours to days. Are there any intermediary (proxy) programs between dbeaver and the Informix server? Is the connection a network connection, or a pipe, or shared memory? At over a day, I'd expect that the system has recovered. Is a Informix server restart an option? (When all else fails, reboot — not desirable, but often effective.) Or even a reboot of the machine on which it's running. This is seldom necessary, but… – Jonathan Leffler Sep 27 '18 at 15:13
  • dbeaver session was disconnected, didn't crash, so that's interesting. not aware of the network specifics, but the server is on our corporate network sitting inside some data center. i dont know about the restart. i am already trying to get in touch with our dba, so hopefully they know. it seems like i have exhausted my options as a client – Schu Sep 27 '18 at 15:25
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/180889/discussion-between-jonathan-leffler-and-schu). – Jonathan Leffler Sep 27 '18 at 15:26

0 Answers0