You can do something like this:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

DECLARE @NewVal BIGINT = (SELECT MAX(member_id) FROM dbo.member WITH (TABLOCKX))+1;

INSERT INTO dbo.Member (member_id, column list)

VALUES (@NewVal, @params);

COMMIT;

Do it in one step, not as separate Insert then Update steps.

This sample sets the isolation level very aggressively. One of the main problems with this kind of computation is that two concurrent inserts can potentially end up with the same value. Serializable and TablockX will prevent that. Will also slow the query down, but if you make the insert fast enough, that shouldn't matter.

With the right indexing on the table, the Max() query should be trivially fast. If not, then you'll want to move that part of the process into a single-row table. Record the next value in that table, use TablockX and query it, use that in the insert into the main table. Single-row query will be very fast, and you don't end up with hyper-aggressive locking on the main table if anything slows the insert down.

But really, you're probably better off just using a computed column for member_id. Then you don't have to do any of this. Why have two columns in one table that both go up by 1 for every insert? I'm sure there's a business reason for it, probably something along the lines of "we don't want the account numbers to be that small", but it can solved in more efficient, less error-prone ways. Like re-seeding the existing column to start at a higher value, or using a computed column that adds some reasonably large number to the existing column.