Lock table for inserts

[simonZ]

I create a transaction:
sqlTran=sqlConn.BeginTransaction(IsolationLevel.Serializable);


Then, I insert some data into report table with sqlCommand object:
oCmd = new SqlCommand("c_reportInsert", sqlConn);
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.Transaction = sqlTran;
....
....
oCmd.ExecuteNonQuery();

Then I do some other inserts on other tables and at the end, I commit transaction:

sqlTran.Commit();

When I execute first sqlCommand object, I cant read or update data in my report table by other programs.
That is OK, because I have transaction opened. Only after I execute sqlTran.Commit(), other programs can read data in that table.

But before I commit transaction, other programs can insert new data into that table.
How can I lock table to prevent inserts too(not only reading), while my transaction is not commited?

I tried all isolation levels but no one prevented inserts into table. Any idea?

Regards,Simon

 

[bruce barker \(sqlwork.com\)]

while it will kill scalability, you set transaction isolation level to serializable for this behavior

-- bruce (sqlwork.com)
I create a transaction:
sqlTran=sqlConn.BeginTransaction(IsolationLevel.Serializable);


Then, I insert some data into report table with sqlCommand object:
oCmd = new SqlCommand("c_reportInsert", sqlConn);
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.Transaction = sqlTran;
...
...
oCmd.ExecuteNonQuery();

Then I do some other inserts on other tables and at the end, I commit transaction:

sqlTran.Commit();

When I execute first sqlCommand object, I cant read or update data in my report table by other programs.
That is OK, because I have transaction opened. Only after I execute sqlTran.Commit(), other programs can read data in that table.

But before I commit transaction, other programs can insert new data into that table.
How can I lock table to prevent inserts too(not only reading), while my transaction is not commited?

I tried all isolation levels but no one prevented inserts into table. Any idea?

Regards,Simon

 

[simonZ]

well, serializable doesn't work. Even if I set to serializible, I can do inserts from other program into that table while transaction is running.

So, when I'm in transaction,I execute the following query:
declare @i int; select @i=1 from ReportTable with(tablockx)

This query locks the table and I can't insert new records until I commit transaction.

This is some kind work around, but it works.

I wonder if there is some other way?

regards,Simon


while it will kill scalability, you set transaction isolation level to serializable for this behavior

-- bruce (sqlwork.com)
I create a transaction:
sqlTran=sqlConn.BeginTransaction(IsolationLevel.Serializable);


Then, I insert some data into report table with sqlCommand object:
oCmd = new SqlCommand("c_reportInsert", sqlConn);
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.Transaction = sqlTran;
...
...
oCmd.ExecuteNonQuery();

Then I do some other inserts on other tables and at the end, I commit transaction:

sqlTran.Commit();

When I execute first sqlCommand object, I cant read or update data in my report table by other programs.
That is OK, because I have transaction opened. Only after I execute sqlTran.Commit(), other programs can read data in that table.

But before I commit transaction, other programs can insert new data into that table.
How can I lock table to prevent inserts too(not only reading), while my transaction is not commited?

I tried all isolation levels but no one prevented inserts into table. Any idea?

Regards,Simon