After inserting in the database, I immediately want to read the rowid of the inserted row, before committing. (committing is much later) But the table is locked until I execute the commit. I attached a zip file showing screen prints of SQL command center:
Connections to the database having isolation level "RL".
Locks for the table that I inserted the row into the value "UXI". (I tried to find out what this screen means but could not find anything in the
documenation) But I gues this represents there is some kind of lock.
My question is: what settings in the connection string or elsewhere shoud I apply so that I can read data from the table immediately after insert, before commit.
Many thanks, Marcel
You should be able to read something that you've inserted if the transaction is the same. I suspect it's not...
I think the ReadCommitted=RL is redundant. I think this is specifically for RC isolation level and you're already saying you want to connect using RL.
I would modify the sql.ini file to add two lines:
This will convert *every* connection that comes in as an RL isolation level to RC1. Try to see if that setting works for you. If it doesn't, put in a defaultrc=2 and try that. If that doesn't work, try defaultrc=3.
If none of these settings works, then I think the transaction is not the same and you might need to investigate that.
BTW, I think the lock display means that there is an Exclusive Insert lock being held.
Thanks for your swift answer. The databaselock is now gone. But retrieving the rowid of the newly inserted / updated row (before commit) is not giving me what I want. If I insert / update a row and then want to retrieve it's new rowid (before commit) it does not find it (insert) or it returns the old one (update). In SQLTalk there is no problem. I can retrieve the new rowid before commit.
Retrieving rowid before the commit makes things much easer because . If I wait to retrieve rowid after commit, I have to start all over again and iterate over all updates that were part of the committed transaction.
in sql.ini, i tried defaultrc=1, defaultrc=2 and defaultrc=3.
The sample is a project in visual studio express 2015 for windows desktop. I removed newtonsoft package to reduce filesize. Please install newtonsoft again via nuget package manager with command
' Install-Package Newtonsoft.Json' .
So after insert / update, I want to immediately retrieve the new rowid, before even a commit has been executed. Before commit, because further on in the transaction an error might occur that requires a rollback.
If I retrieve the rowid after update / insert, the old rowid (in case of update) or no rowid (in case of insert) is found. If I execute a commit before retrieving the rowid, the new rowid is returned, proving there is nothing wrong with the code.
In SQL talk, SQLBase acts the way I want: the new rowid is returned after insert / update. No commit is required.
Who is online
Users browsing this forum: [Ccbot] and 0 guests