[
https://issues.apache.org/jira/browse/OFBIZ-3557?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13040315#comment-13040315
]
Bruno Brandmeier commented on OFBIZ-3557:
-----------------------------------------
Hi all,
why not using a lock table like this (example in c#):
c_Lock mylock = new c_Lock("rechnungs_nr");
mylock.use_lock();
int nur_rechnungs_nr = mylock.increment_counter();
mylock.open_lock();
class c_Lock
{
string name = "";
string lock_closed_by = "";
int lock_counter = 0;
bool can_use_lock_now = false;
string unique_id = "";
MySqlConnection conn_shop = null;
c_BB_Shop bb_shop = new c_BB_Shop();
string sql_string = "";
MySqlDataAdapter da = null;
System.Data.DataTable dt_remote = null;
DataRow row_remote = null;
MySqlCommand cmd = null;
BB_Gen.c_BB_Gen bb_gen = new BB_Gen.c_BB_Gen();
public c_Lock(string feldname)
{
name = feldname;
if (name == "")
{
MessageBox.Show("Feldname wurde nicht übergeben");
}
else
{
conn_shop = bb_gen.conn_open(bb_shop.g_connectionstring_shop);
read_lock();
}
Random zufallszahlen = new Random();
unique_id =
zufallszahlen.NextDouble().ToString().Substring(2).PadRight(18,
Convert.ToChar("0"));
}
public bool use_lock()
{
int start_zeit = 0;
int schloss_geschlossen_seit = 0;
int max_lock_lease = 10;
int aktuelle_zeit = 0;
bool result = false;
if (row_remote["lock_type"].ToString() != "undefined")
{
start_zeit = bb_gen.unix_timestamp();
while ((result = close_lock()) == false)
{
aktuelle_zeit = bb_gen.unix_timestamp();
schloss_geschlossen_seit =
int.Parse(row_remote["lock_closed_since"].ToString());
if (((aktuelle_zeit - start_zeit) > max_lock_lease) &&
((aktuelle_zeit - schloss_geschlossen_seit) > max_lock_lease))
{
crack_lock();
break;
}
else
{
Thread.Sleep(1000);
read_lock();
}
}
}
else
{
result = false;
}
return result;
}
public void open_lock()
{
if (can_use_lock_now == true)
{
sql_string = "UPDATE locks SET
lock_status='unlocked',lock_closed_by='',lock_closed_since='0' WHERE Lock_ID='"
+ row_remote["Lock_ID"].ToString() + "';";
cmd = new MySqlCommand(sql_string, conn_shop);
int ergebnis = cmd.ExecuteNonQuery();
cmd.Dispose();
if (ergebnis == 1)
{
lock_closed_by = "";
can_use_lock_now = false;
}
}
conn_shop.Close();
conn_shop.Dispose();
}
private bool crack_lock()
{
bool result_var = false;
sql_string = "UPDATE locks SET
lock_status='locked',lock_closed_by='" + unique_id + "' WHERE Lock_ID='" +
row_remote["Lock_ID"].ToString() + "';";
cmd = new MySqlCommand(sql_string, conn_shop);
int ergebnis = cmd.ExecuteNonQuery();
cmd.Dispose();
if (ergebnis == 1)
{
lock_closed_by = unique_id;
can_use_lock_now = true;
result_var = true;
}
else
{
result_var = false;
}
return result_var;
}
public int increment_counter()
{
int return_var = 0;
if (can_use_lock_now == true && row_remote["lock_type"].ToString()
== "counter")
{
sql_string = "SELECT lock_counter FROM locks WHERE Lock_ID='" +
row_remote["Lock_ID"].ToString() + "';";
cmd = new MySqlCommand(sql_string, conn_shop);
int result = int.Parse(cmd.ExecuteScalar().ToString());
cmd.Dispose();
if (result == 0)
{
return_var = 0;
}
else
{
int lock_counter_incremented = result;
lock_counter_incremented++;
sql_string = "UPDATE locks SET lock_counter='" +
lock_counter_incremented.ToString() + "' WHERE Lock_ID='" +
row_remote["Lock_ID"].ToString() + "';";
cmd = new MySqlCommand(sql_string, conn_shop);
int ergebnis = cmd.ExecuteNonQuery();
cmd.Dispose();
if (ergebnis == 0)
{
return_var = 0;
}
else
{
lock_counter = lock_counter_incremented;
return_var = lock_counter_incremented;
}
}
}
else
{
return_var = 0;
}
return return_var;
}
private bool close_lock()
{
bool result_var = false;
string object_identifier = unique_id;
sql_string = "UPDATE locks SET
lock_status='locked',lock_closed_by='" + unique_id + "', lock_closed_since='" +
bb_gen.unix_timestamp().ToString() + "' WHERE Lock_ID='" +
row_remote["Lock_ID"].ToString() + "' AND lock_status='unlocked';";
cmd = new MySqlCommand(sql_string, conn_shop);
int ergebnis = cmd.ExecuteNonQuery();
cmd.Dispose();
if (ergebnis == 0)
{
result_var = false;
}
else
{
sql_string = "SELECT lock_closed_by FROM locks WHERE Lock_ID='"
+ row_remote["Lock_ID"].ToString() + "';";
cmd = new MySqlCommand(sql_string, conn_shop);
string object_identifier_aus_db =
cmd.ExecuteScalar().ToString();
cmd.Dispose();
if (object_identifier_aus_db != object_identifier)
{
read_lock();
result_var = false;
}
else
{
lock_closed_by = object_identifier;
can_use_lock_now = true;
result_var = true;
}
}
return result_var;
}
private bool read_lock()
{
bool return_val = false;
sql_string = "SELECT * FROM locks where name='" + name + "';";
da = new MySqlDataAdapter(sql_string, conn_shop);
dt_remote = new System.Data.DataTable();
da.Fill(dt_remote);
if (dt_remote.Rows.Count == 1)
{
row_remote = dt_remote.Rows[0];
return_val = true;
}
return return_val;
}
}
Bruno
> Enforced sequence does not work with concurrent access
> ------------------------------------------------------
>
> Key: OFBIZ-3557
> URL: https://issues.apache.org/jira/browse/OFBIZ-3557
> Project: OFBiz
> Issue Type: Bug
> Components: framework
> Affects Versions: Release Branch 09.04, SVN trunk
> Reporter: Wickersheimer Jeremy
> Attachments: OFBIZ-3557-1.patch, OFBIZ-3557-2.patch
>
>
> There is a fundamental issue with enforced sequences (for orders, invoices,
> etc ..) and concurrency.
> For example if two users are creating an order at the same time one of them
> will see the creation fail with a PK error. The problem is that the
> "getNextXXXId" rely on the party accounting preference entity, but there is
> absolutely no guarantee that the last number in the sequence gets updated
> before another service can read it.
> This is at best very annoying when used only internally but may be
> unpractical for e-commerce sites.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
