Shared Locks
Posted in 1998
Topics: SQL Development & Query Writing, Stored Procedures & SPL, Connectivity: ESQL/C, 4GL & Embedded SQL, Transactions, Locking & Isolation
Does anyone know how and why shared locks are used when selecting data from a Dynamic Server 7.2 database? We have a Powerbuilder application that selects customer data (isolation level = dirty read) and displays it in tab page format for the user to navigate. The problem is that over the course of the day, shared locks build up as the users look at the information. Eventually, so many locks build that errors start to occur and the user must log out. We only have this problem with our client/server applications and I suspect that it may have something to do with the select statements being inside a transaction. All of our programs written in Informix-4gl are fine. Also, I curious as to how locks handled with temporary tables. If you create a temp table in a stored procedure, does it (or should it) create exclusive locks? I know that this is probably a loaded question, but I can't seem to find the answer anywhere. I have read all of the definitions in the books on the different types of locks and isolation levels, but it doesn't seem to help. Thanks in advance.