Table locked after insert before commit

[Marcel]

I use C# .NET to connect to SQLBase. Connection string: @"host=LOCALHOST;ServerName=Server1;Initial Catalog=ahne;isolation=RL;autocommit=false; ReadCommitted=RL; transport=TCP;Port=2155"

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

[Mike Vandine]

Hi Marcel,

You should be able to read something that you've inserted if the transaction is the same. I suspect it's not...

You've got:

isolation=RL;ReadCommitted=RL

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:

autorc=1
defaultrc=1

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.

Best regards,

[Marcel]

Hi Mike,

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.


Greets, Marcel

[Mike Vandine]

Hi Marcel,

I'll need a small testcase, preferable against the ISLAND database.

Best regards,

[Marcel]

Ok thanks. I'll need some time to make the sample.

Greets

[Marcel]

Hi Mike,

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.

Tx, Marcel

[Marcel]

O, forgot to say in my previous message: the test transactions are against the 'contact' table of the 'islands' database. For simplicity of the example, I hardcoded an instance of a contact having rowid 'EHAAAAAAACAAAAAAGGAKAAAAAAAAAAAINGACAAAA' . This rowid might require a change in the 'program.cs' section.

greets, Marcel

[Marcel]

well, I guess its just the way it works in .NET. I will retrieve the new rowid's after final commit. I already created the software, no need anymore to go into this issue.

tx, Marcel