1

When doing select on a table row in INFORMIX, how do you know if it is currently being locked by another user without using "for update" construct?

mihirp724
  • 129
  • 5

1 Answers1

2

Interesting question, and I am curious to know your motive and your end goal behind this question!
I do not believe there is a way (via select statement, without update) to find out if there a shared lock held on a row. Only when your session tries to modify the row you'll find out if some other session is holding a shared lock.
If you just want to know if there is an exclusive lock held on a row, then you can set your session isolation level to repeatable read.
set isolation to repeatable read; select * from tab1 where col1=1;
The above information also assumes that the table itself uses row level locking (the default is page level locks).
NOTE: Repeatable Read is the most restrictive isolation level. (https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_0030.htm)

Pradeep Natarajan
  • 370
  • 2
  • 10
  • I have a rows of information to process in an application, which essentially is a result of multiple inner joins. Ultimately, after the processing is done, only one of the tables get updated( at this point, the application terminates because of a lock). – mihirp724 Feb 27 '17 at 18:14
  • Are you wanting to find out which session is holding the lock on that row? It could be any other session (depending on the isolation level) either trying to read or update concurrently. If you have DBA permissions, you can take many actions to reduce contention between concurrent sessions. – Pradeep Natarajan Feb 27 '17 at 18:28
  • No, just want to know if the current row I'm processing is locked or not (regardless of who's locking it). – mihirp724 Feb 27 '17 at 19:04
  • @mihirp724 What you really need in your application is some kind of error handling in case of a locked row. There is not a perfect way to do what you are looking to do (assuming I understand the exact use case!), i.e.., select a row, check if it has any locks, if not locked update it. The reason is that in a concurrent system, you can never be certain if a given row is going to be locked or not. Even if there are no locks at the time of checking, it could get locked immediately after that check! – Pradeep Natarajan Feb 27 '17 at 21:27